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.
Edit: As it turns out, the credentials work for all Google accounts. So you can download my .mez and simply paste it into your Custom Connectors-path without touching Visual studio ( create path:
[My Documents]\Microsoft Power BI Desktop\Custom Connectors ). I believe this will work for the 1st 100 users and then you have to create you own. But if you want to use it in production, I’d strongly recommend to create your own anyway (otherwise continue with section “Use Google Sheets Data Connector in Power BI Desktop”) :
Setup Google API
Go to the Google Developer API and if you don’t have a project yet, just create one:
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: