Google Spreadsheets Custom Connector

⚠ WARNING: The content of this post has been made deprecated due to the availability of the official Google Sheets connector and you should use that feature to import the data. ⚠
Check the official announcement on the link below:
This post is just for future reference

In the beggining

I’ve been tasked to find a way of exporting data from Google Spreadsheets for a client. I’ve started researching for an already working solution and got to the following links:

A lot of the work and heavy lifting has been done by them and the rest of this post will be about how to do it today, as some of their content is outdated and was not working when scheduling refreshes via the Power BI Enterprise Gateway.

OAuth at Google

Create a project at Google Developer API

New Project

Add the OAuth credentials as shown below

Add Credentials

Add Credentials 2

Add Credentials 3

Add Credentials 4

Save or download these credentials we will need them later.

Get the project

Clone the project from my fork1 @GitHub and replace appKey and appSecret on PQGoogleSpreadsheet.pq file with the values that you’ve got on the previous step.

Replace String

Build the project in Visual Studio and copy the PQGoogleSpreadsheet.mez from bin folder file to [My Documents]\Microsoft Power BI Desktop\Custom Connectors2.

If everything went as supposed to, you should now be able to see it in the list of conectors:

Google Spreadsheets Connector @ Power BI

Enterprise Gateway

If you wish to use the connector with the enterprise gateway you need to copy it over to the gateway server.

You can configure it’s location on the Connectors pane:

Custom Connector Folder

Have fun!

  1. I’ve made a pull request to the original repo, so in the future you may just clone and use that. ↩︎

  2. ↩︎