How to connect google sheet with power bi?

Question

I am trying to connect google sheet with power bi using R Studio, there is a video on youtube by stephnie locke, but that is just a short video and dont help completely. any one there which can help properly and completely.

Answer 1

If you're not limited to R Studio, there is another way to access Google Sheet data from Power BI:

Sample data:

1. Get shareable link of the Google Sheet (Permission at least Anyone with the link can view):

2. Modify the shareable link and add the following parts:

   From:

   https://docs.google.com/spreadsheets/d/google-sheet-guid/edit?usp=sharing

   To:

   https://docs.google.com/spreadsheets/d/google-sheet-guid/export?format=xlsx&id=google-sheet-guid

e.g.

https://docs.google.com/spreadsheets/d/1ohAz8SD6viEGIrcor-JC-vCt2yRbgY1c-7Uflv-_hrA/edit?usp=sharing -> https://docs.google.com/spreadsheets/d/1ohAz8SD6viEGIrcor-JC-vCt2yRbgY1c-7Uflv-_hrA/export?format=xlsx&id=1ohAz8SD6viEGIrcor-JC-vCt2yRbgY1c-7Uflv-_hrA

3. Create a blank query in Power BI and get the source as below:

   let Source = Excel.Workbook(Web.Contents("the-link-in-step-2"), null, true) in Source

4. If you drill into the Table cell you should be able to see the data and work from there.