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.
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