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.
trebgatte I played around with it some more and could make your code work only if I allow everyone access who has a link. Then anonymus connections works, as the API key does it's job (just like your sample file in the comments with the cocktail receipes)
Do you know how to tweak the code so that it works with specific users and a user authentication dialogue pops up ?
Hi there,
I found a custom connector for OAuth2 - authentication for Google Sheets. See how this worked for me here: http://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/
Basically, what you show to get the client secret and all is what I did to get mine working and it works in the service since it's not a custom connector. Perhaps that's what people are missing.
