EDIT 17th August 2019: Added instructions for the newly added consent screen below..
Recently I came across the need to connect to Google Sheets with a secure authentication process quite often, so I will share with you how and to what extend I got the custom connector working that I found here. It uses OAuth2 authentication, so you can share your workbook with selected colleagues and they will be prompted to enter their credentials in Power BI if they try to access these files.
High level overview
First you have to create an API in your Google account. This will enable other programs to connect to your data (provided they pass the correct credentials). After that you have to adjust the .mez-file to include the client id and client secret that identify your API. This data will be generated when setting up the API. After you’ve adjusted the .mez you have to store it in a dedicated folder so that Power BI can access it. Lastly, if you want to use this connector in the Power BI service, you have to add the .mez-file to a gateway.
Setup Google API
Go to the Google Developer API and if you don’t have a project yet, just create one:
NEW: A OAuth consent screen will pop up and you have to give your app a name and fill in “PowerBI.com” into the “Authorized Domains”:
Next go to “Credentials” -> Create credentials and choose “OAuth client ID”:
Choose “Web application”, adjust the “Name” if you like and paste the redirect-url into “Authorized redirect URLs”: https://preview.powerbi.com/views/oauthredirect.html
This will return the client ID and secret for your connector:
Adjust the files for the connector
Next download the files from this GitHub folder:
“PQGoogleSpreadsheet.pq” is the file you need to edit:
The documentation says “To make it run add client_id.txt and client_secret.txt containing your Google API client credentials”, but I didn’t find out where to store these file to make it work, so I replaced the green strings with the hardcoded Google OAuth values:
It would be nice being able to treat these strings as variables, so that one could use one connector for content from different users: So if you know how to get this working, please give a shout.
Then build the project and move the .mez-file from the bin-folder of your project to your custom connector folder ( its “C:\Users\<YourUserName>\Documents\Microsoft Power BI Desktop\Custom Connectors” in my case).
Use Google Sheets Data Connector in Power BI Desktop
Close PBID and re-open so you will see the new connectors in your data sources:
Authentication in PBID
Enter the URL for your file into the next dialogue:
and this warning will show:
Click “Edit Credentials” and this dialogue will pop up:
Check “Sign in” and a Google authentication window will appear:
Enter credentials and the data of the sheet will appear in a clean format.
If data connectors are new to you, check out this article to get you started.
I ran into trouble with my Google account and always got “Couldn’t sign you in”-error.
By now, I haven’t found a solution for this. At the end I created a new account and granted access for my files to this account for being able to connect to them.
I’ve also heard from other problems, like “DMTS_PublishDatasourceToClusterErrorCode”. This could be due to firewall issues with the gateway (https://github.com/microsoft/DataConnectors/issues/239) .
Enjoy & stay queryious 🙂