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.
Most Valuable ProfessionalI'll have to revisit the blog post as there were some changes on how to get the developer id on the Google side. Once you have the developer id, it connects securely and off you go.
I used it last week so I know it is working. The new API reports back the number of columns now but I have no idea where they are deriving that number. I had a nine column sheet and it told me I had 15 columns. If you set the fold to 15, it really jacks up the data.
Updates will be forthcoming.
Treb Gatte | MVP | TumbleRoad.com | PowerBICertification.com
how do I get a developer id from google?