How to connect google sheet with power bi?
An easier way: Go to Get data, search Web. Select Web Select Web
on the URL.
-> Modify the URL.
From:
https://docs.google.com/spreadsheets/d/emphasized textgoogle-sheet-guid/edit?usp=sharing
To:
https://docs.google.com/spreadsheets/d/google-sheet-guid/export?format=xlsx&id=google-sheet-guid
Paste.
Paste link
And there is your sheet.
Select your sheet
To build upon @Joseph A's perfect answer, if you would like to make it refreshable after publishing your report to the Power BI service from Desktop:
- Navigate to the workspace where the report is published (i.e. My Workspace)
- Click Datasets
- Under Actions, click the ellipses and choose Settings
- Expand Data source credentials and click Edit Credentials
- Change Authentication method to Basic
- Enter User Name and Password of Google Docs account where the file resides and click Sign In
Your report can now be refreshed and updated (manually or scheduled) to reflect changes in your Google Sheets doc!
If you're not limited to R Studio, there is another way to access Google Sheet data from Power BI:
Sample data:
- Get shareable link of the Google Sheet (Permission at least
Anyone with the link can view
):
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
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
- If you drill into the Table cell you should be able to see the data and work from there.