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 tried the https://docs.google.com/spreadsheets/d/idstring/export?format=xlsx&id=idstring method *without* publishing the Google Sheet and my PC tries to download and open the exported Excel file rather than load it in Power Query. Same behavior in Power BI Desktop and Excel 2016.
I can't suggest to my clients to rely on obscurity to keep their data secure.
I thought of using Zapier, but while there's a GSheets trigger for "new worksheet", supported Excel Online actions are limited to rows, you can't create a whole worksheet that way.
I'd love to have a reliable and secure GSheets -> Power BI/Excel connection. I can already do it from Smartsheet and Airtable, it's weird not to have real support for Google Sheets!
this parameter ?format=xls does not work anymore.
somehow, I managed to make it work using ?gid=0&single=true at the end of the published link.
but I don´t want to publish, as my data is and must remain private.
how can I use google API to make Power BI read my sheets?
Hi Anonymous,
It seems Google have changed their File / Publish to the web pop-up, but actually it now seems easier. If you change the 2nd setting from "Web page" to "Microsoft Excel (.xlsx)", it now builds a link that works without modification.
Hello
I tried to apply your solution however I got this error
I could not see my data
column 1 column2
info icon navigation to the webpage was cancelled
info icon
what you can try
refresh the page
any idea please?
Thanks