Forum Discussion
How to connect google sheet to Power BI
2023 update - the method below is no longer required, the native connector to Google Sheets is generally available. This supports Google authentication, so the data no longer needs to be public.
https://learn.microsoft.com/en-us/power-query/connectors/google-sheets
**** BELOW IS NOW OUT-OF-DATE ****
The easiest way is to Get Data / From Web, then enter the URL to your google sheet, with "&output=xls" on the end, e.g.
http://spreadsheets.google.com/pub?key=r1hlZB_n1rpXTij11Kw7lTQ&output=xls
PBI then analyses the resulting Excel file, showing the tabs as tables , which you can edit and manipulate.
I think it now takes a little more doing than just the "&output=xls" on the end of URL. Here's a robust solution that I've tested and used quite a bit to get full data out of Google Sheet. Apologies for all the steps:
1. Use Power BI desktop (this won't work just on Power BI service you have to start on desktop).
2. Share Google Sheet and get link from sharing.
3. Paste Google Sheet shared link and it will end in something like "adfe/edit?usp=sharing"
4. Remove the /edit?usp=sharing off the url
5. then add export?format=xlsx&id= where the edit/? had previously started
6. then copy and paste the long id from the first part of your url
7. the long, final URL you should use for Power BI get from web will be something like:
"https://docs.google.com/spreadsheets/d/1nWV8adkjfadkfHWDIAa3ad/export?format=xlsx&id=1nWV8adkjfadkfHWDIAa3ad"
NOTE: id after equals sign matches id from Google for share sheet. (BTW this isn't a real link just demonstration).
That's it - now you can design in Power BI desktop and publish to Power BI service on web (if needed). Only downside is there's no automatic refresh. Folks can edit / enter on Google Sheet but change won't appear in Power BI Desktop until you click refresh and won't appear in Power BI Service until you republish and overwrite. To attempt quasi-automation from a Google Sheets data source, you might want to consider saving your PBIX desktop file in your OneDrive folder since the Power BI service could update that hourly, that could potentially at least eliminate the final step in refreshing Power BI service. As soon as folks make changes to Google Sheet, you simply click refresh in Power BI Desktop which will fetch and refresh visuals based on updated data, but then just save that updated PBIX file in a OneDrive folder that is published to Power BI service and it should update automatically within an hour.
GGetty Does using the "export?format=xlsx&id=" publish the sheet to wthe web for access?
Just want to make sure, before I use it, that this solution doesn't cause our sheet to be published or available to anyone other than who it has been shared with.
If this maintians the security of the sheet this is a real slick solution.
Look forward to hearing back from you!
NWBI I think you did have to publish if you wanted to use the "export?format...." solution. You should definitely test first with a spreadsheet that doesn't have sensitive data. Caveat emptor. This thread is nearly two year old. Other folks in the thread talked about setting up a Google Developer ID for more secure access - you might want to try one of those solutions.
This worked just fine on an unpublished sheet.
Interesting.
Good solution, thanks!
NWBI glad to hear it. I imagine it all worked smoothly as you as the author were logged in to Power BI and Google with correct credentials. It would be interesting to hear what happens if other users attempt to view the Power BI visualizations on PowerBI.com or by opening the Power BI .PBIX file and clicking refresh.
Glad you got what you needed. Cheers.