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.
Hi dosansil and Anonymous:
I made a stupid mistake here, so the solution doesn't work unfortunately: I didn't recognize that the sheet I was referrig to was still published to web. So despite me restricting the access in the "share"-area, there was still the published web-option which accepted my credentials. But the problem here is, that I actually didn't need credentials, as the anonymus connection would have worked as well.
I'm very sorry to have wasted your time here!! (Will edit/delete my orignal post so that others will not get fooled as well)
Hello, ImkeF. Don't worry. You are always helpful and gently. I'm grateful for your try and good assistance. If you discovery a solution, let me know. Thanks!
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/
I have the basic process here in my post. https://tumbleroad.com/2016/07/07/free-marquee-google-sheets-template/ Do not use my sample developer id as this will not allow you to connect to your data.
However, this is the latest Google docs on the process: https://developers.google.com/identity/sign-in/web/devconsole-project Unfortunately there are no screenshots to walk you through this process. If you use the terms from the process above on getting the ID process and the updated doc link, you should be able to navigate through the new Google UI. I'm putting this on the backlog for update.
Treb Gatte | MVP | TumbleRoad.com | PowerBICertification.com
I'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?
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 ?
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.
