Custom Connector to import Google Sheets with OAuth2 authentication in PowerBI

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:

 

Adjust the connector in Visual Studio

Next clone the project and open in Visual Studio:

“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\imkef\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.

Edit: This is currently only supported for the Desktop version. But implementation for the service is planned.

Enjoy & stay queryious 🙂

Comments (10) Write a comment

  1. Pingback: #Excel Super Links #156 | Excel For You

  2. I was able to get to work in the PowerBI Desktop, thanks! Has anyone been able to get this to work in the PowerBI online service in terms of automated refresh? In the service I get a message that tells me this is not a supported connector.

    Reply

    • No, it’s currently just supported for the Desktop-version.
      It will definitely not work in the service now, but should come for the service later as well.

      Reply

      • We are hoping sooner as this is a big requirement for us and a lot of other companies. We keep lobbying Microsoft but it does not seem to be a priority.

        Reply

        • I don’t know a timeline for this, but my impression is that the connectors have a high priority for Microsoft. So wouldn’t be too pessimistic about it.

          Reply

  3. HI,

    I’m trying to use this great custom connector, but without successes.

    After adding the spreadsheet url i get the error below:

    [Details: “We cannot convert the value null to type Text.”]

    Any help will be highly appreciated,

    Thanks in Advanced,
    Avi

    Reply

    • Did you build it yourself or did you use my .mez that you can download from github?
      If you created the .mez yourself, please compare it with my version from github.version from github (unzip the files and open the .m-files with an editor of your choice, or directly in Visual Studio).
      Cheers, Imke

      Reply

  4. I’m totally beginner, So I followed each step accurately.

    I’ve used your .mez file and just change the client_id and the client_secret.
    It seems that the connector is working, but after i entered the spreadshhet url,
    it did not log in and the written error below appeared.

    Unable to connect
    We encountered an error while trying to connect.
    Details: “We cannot convert the value null to type Text.”

    Reply

  5. Hi Imke,

    I managed to solve it
    It turned out that another connector caused the problem.

    Thank you for your help, and for your great connector.

    Best Regrades,
    Avi

    Reply

Leave a Reply