How to connect google sheet to Power BI

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/

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)

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

Hi trebgatte,  thanks a lot - missed that.

I followed all the steps and have been prompted with an "Edit credentials" dialogue.

Which used Basic with my google credentials, also anonymus, but nothing worked.

How is this supposed to work?

Thanks, Imke

I got asked by another client to look into a secure/API connection direct to Power BI again. 

My conclusion is that the only Google API Authorization option now is the dreaded oAuth2, which is beyond the capabilities of Power BI. 

I did have a look at Treb's blog post, but that seems to get tripped up by the same change in the Google API.

FWIW my proposed workaround is to use Microsoft Flow to read from Google Sheets and write the rows to an Excel file stored in OneDrive, running on a schedule. I got a crude prototype running quickly so it seems viable.

Hope this saves others some time.

Obviously the best solution would be for Microsoft to add Google Sheets connectivity with integrated authorization - if you agree please add your votes and comments here - maybe it will be delivered before it's 3rd birthday ...

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6656584-connectivity-to-google-sheets

Deleted the thread because the "solution" actually didn't work.

ImkeF, I can't find this dialog box with Basic Authentication in my Power BI. The web connector is to simples, like this: 

Yes, paste the URL that you've copied from Google sheet there.

The dialogue for the authentication will appear after that step. 

The next step is not the authenticator, is this screen, with a blank document: 

Thanks!

delete

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!

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.