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.
GGetty - Thanks for sharing those details - that also works nicely for my files.
I'm curious why the "sharing" method would be more "robust" ?
I'm able to set up manual and scheduled refresh in app.powerbi.com. You just have to specify the web credentials (Anonymous). I dont think its necessary to continually refresh through PBI Desktop.
Hi everyone,
Thanks for keeping this thread going.
Just a comment and some questions on both methods shared by mike_honey and GGetty. I tried both solutions and it worked perfectly, I did notice that you have to be the owner of a file for sharing the links to make it work, i.e even if you have full edit rights on a Google sheet and you publish or share the file you will run into issues.
Has any one else experienced this or can the ownership reason be verified? How do you get around this if you aren't the owner of a file to make this work?
Cheers
Ernst
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.
