| ⚠ 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: |
| https://powerbi.microsoft.com/en-us/blog/power-bi-november-2021-feature-summary/#post-17912-_Toc87473946 |
| 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:
- https://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/
- https://www.skolenipowerbi.cz/l/google-drive-connector-kompletni-pruvodce/
- https://github.com/fluf1024/PQGoogleSpreadsheet
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
Add the OAuth credentials as shown below
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.
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:
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:
Have fun!
-
I’ve made a pull request to the original repo, so in the future you may just clone and use that. ↩︎
-
https://github.com/Microsoft/DataConnectors/blob/master/docs/m-extensions.md#overview ↩︎