EDIT 17th Feb 2022: Finally, we have the native connector in Power BI: Power Query Google Sheets connector – Power Query | Microsoft Docs – so no need for the workaround described in this blogpost.
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.
Caveats
I ran into trouble with my Google account and always got “Couldn’t sign you in”-error.
Error with certain accounts
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 🙂
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.
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.
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.
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.
Microsoft has implemented custom connector ability in the On-Premise Gateway (personal mode). This means it will work to refresh google sheet data in the service soon. I say soon as I am not able to get this to work yet. Has anyone had any luck?
Hi Robert,
no I haven’t had the chance to test it yet.
Cheers, Imke
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
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
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.”
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
Pleased to hear – thanks for feedback!
Will this work for PowerQuery?
Not yet. Custom connectors are currently supported only for Power BI Desktop unfortunately.
1) Does the URL have to be in any particular format for the step “Enter the URL for your file into the next dialogue”?
2) When I try to Refresh All in PBID, I’m sometimes but not always getting errors that random columns cannot be found, even though individual query Refresh Previews succeed without errors (i.e. there are no problems with the underlying columns). I’ve seen this type of error mentioned on other search results for Power BI + Google Sheets, which makes me wonder whether ongoing/stable/refreshable connections to Google Sheets are still not possible as of July 2018. Have you not encountered this kind of error?
Thanks!
PR
Hi PR,
I’m not using this in practice, so no further experience here.
URL has to be simple text.
/Imke
Hi! I am following along, and got a little stuck at the Visual Studio part, as its a program im not all that familiar with.
I opened the project in VS without problem, even used the GitHub extension. I applied the changes as expected, but I dont have an option to build, nor do I have any .mez files.
Is there anything im missing?
Hi Stefan,
please see the picture below where you should see the options to build your solution. If this doesn’t show up, you might have to adjust your setup (I don’t have any specific material for that).
By default, you’ll find the .mez-files (after the solution has been built) in a subfolder like so: …\\Documents\Visual Studio 2017\Projects\\ \bin\
https://www.thebiccountant.com/WP/wp-content/uploads/2018/10/VS1.png
Hello,
I am following the above steps but I am unable to add Authorized redirect URIs as it shows me this error: “Invalid Redirect: domain must be added to the authorized domains list before submitting.”. Please guide me as soon as possible.
Thanks in advance.
Very sorry, but the solution doesn’t seem to work any more.
Just included a disclaimer in the post so that other users don’t waste their time on this.
Will update once I’ve got it working again.
This worked for me! Not sure if I did anything different, but happy to help if I can. Only thing I noticed was that I had to press enter after pasting in the redirect URL. Also, I put the id and secret into two files I created in the project folder called “client_id.txt” and “client_secret.txt”, then set their Build Action to “Compile” in Visual Studio.
Thanks for the tipp!
Does the refresh in PBI service work as well?
Hi I was trying out today, changing the client_id and client_secret inside the “PQGoogleSpreadsheet.pq” file, until the part where i key in my google sheet url. but when i attempted to sign in (i.e. clicking on the sign in button), there was an error of “[Expression.Error] We couldn’t find the file….”. As such I could not sign in. Any idea why and how to resolve it? Thanks 🙂
Hi Marcus,
you have to replace everything after the “=” with your client_id and client_secret (incl. “Text.FromBinary….)
/Imke
I only use it with a manual monthly-ish refresh, so I’m not sure about the service – sorry!
Also, I was unable to reply to your comment – Firefox just kept trying to download wp-comments-post.
No prob – thanks anyway!
Hi All
Does this also work with Power BI Report Server as well? In the document you put powerbi.com ; is it for t the service address or could I use the company domain?
Many thanks in advance
Yes, you can publish the report on report server, but cannot refresh (current limitations with OAuth: https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh)
/Imke
Hi Admin,
I was able to create this connector thanks to your awesome post although slight complications in the process. I was trying to configure scheduled refresh for this connector using Personal Gateway as listed by this blog https://www.poweredsolutions.co/2018/04/23/refreshing-a-power-bi-custom-connector-in-the-cloud/
This article says to include a test connection handler function to “update your Custom Connector to be refreshable on the cloud.” I was having trouble setting this function up in my code file as I’m not that good of a programmer. Can you show how to integrate this with your provided code?
Its working incase of an Excel file, but not supported with a text/csv.
I’ve a folder in my Google Drive with few Excels and Csv docs, Any idea how to access the entire folder? that is, all of the files from the drive folder.
Yes, it is not designed to work with Google Drive.
Just native Google sheets are supported with this connector.
Haven’t had the time to check out Google Drive connection yet.
Thanks for the reply. How do i make a connector for google drive? Any instructions, references or guidelines would be helpful.
Here comes a link to instructions and many samples: https://github.com/Microsoft/DataConnectors/blob/master/docs/m-extensions.md
/Imke
Thanks alot, appreciate it.
Here’s how using client_id.txt and client_secret.txt insead of hardcoding works:
For each file, right-click the file in the Solution Explorer and select Properties. In the Properties window, change Build Action to Compile. This ensures that the files are included in the .mez file. That’s all there is to it.
Thanks for sharing this!
Hello!
I couldn’t set refresh in Power BI Service for your Google Spreadsheet connector(
I have an error after logging in my Google account: https://yadi.sk/i/3D2nFPYHfiFj2g. It was only in Power BI Service when i tried to edit credentials for this datasource.
In Power BI Desktop connector works well.
And i have the same problem with my connector for Yandex.Direct (i made it based on your sample).
Do you know the possible reason?
Thank You!
Hi Alexandr,
I haven’t checked it out for a while.
Let me see what I can find out…
I downloaded your .mez and dropped it it. I put in my spreadsheet URL and get an Oauth screen. When I try to sign in:
“Sign in with Google temporarily disabled for this app
This app has not been verified yet by Google in order to use Google Sign In.”
I’m guessing I just need to go ahead and build my own?
Hi Joseph,
I’m experiencing difficulties with the login as well.
Will need to dig deeper later during the week on this.
Sorry for the inconvenience,
Imke
Finally I found the location for “client_id.txt” and “client_secret.txt”. They are located in the .mez.
For those who don’t want to install Visual Studio and recompile the mez file, you can follow the instruction by the original developer.
https://www.skolenipowerbi.cz/l/google-drive-connector-kompletni-pruvodce/
Thank you very much for your efforts! I managed to build the connector. Unfortunately I still have a problem. Could you help me with the message below?
“Details: Web.Contents cannot retrieve the content from ‘https://drive.google.com/drive/folders/export?format=xlsx’ (404): Not Found”
Thank you very much in advance for your reaction
Hello Simon,
it looks as if you haven’t put in a url/filename to your Excel file.
/Imke
I am trying to extract data from an oAuth2 API.
In one of the steps a drop-down window appears asking for login and password.
Is it possible to log in to a Pop-Up window with Pòwer Query?
I’ve never heard of that.
Otherwise, please check the options for it here: https://docs.microsoft.com/en-us/power-query/handlingauthentication
/Imke