Forum Discussion
How to connect google sheet to Power BI
2023 update - the method below is no longer required, the native connector to Google Sheets is generally available. This supports Google authentication, so the data no longer needs to be public.
https://learn.microsoft.com/en-us/power-query/connectors/google-sheets
**** BELOW IS NOW OUT-OF-DATE ****
The easiest way is to Get Data / From Web, then enter the URL to your google sheet, with "&output=xls" on the end, e.g.
http://spreadsheets.google.com/pub?key=r1hlZB_n1rpXTij11Kw7lTQ&output=xls
PBI then analyses the resulting Excel file, showing the tabs as tables , which you can edit and manipulate.
I think it now takes a little more doing than just the "&output=xls" on the end of URL. Here's a robust solution that I've tested and used quite a bit to get full data out of Google Sheet. Apologies for all the steps:
1. Use Power BI desktop (this won't work just on Power BI service you have to start on desktop).
2. Share Google Sheet and get link from sharing.
3. Paste Google Sheet shared link and it will end in something like "adfe/edit?usp=sharing"
4. Remove the /edit?usp=sharing off the url
5. then add export?format=xlsx&id= where the edit/? had previously started
6. then copy and paste the long id from the first part of your url
7. the long, final URL you should use for Power BI get from web will be something like:
"https://docs.google.com/spreadsheets/d/1nWV8adkjfadkfHWDIAa3ad/export?format=xlsx&id=1nWV8adkjfadkfHWDIAa3ad"
NOTE: id after equals sign matches id from Google for share sheet. (BTW this isn't a real link just demonstration).
That's it - now you can design in Power BI desktop and publish to Power BI service on web (if needed). Only downside is there's no automatic refresh. Folks can edit / enter on Google Sheet but change won't appear in Power BI Desktop until you click refresh and won't appear in Power BI Service until you republish and overwrite. To attempt quasi-automation from a Google Sheets data source, you might want to consider saving your PBIX desktop file in your OneDrive folder since the Power BI service could update that hourly, that could potentially at least eliminate the final step in refreshing Power BI service. As soon as folks make changes to Google Sheet, you simply click refresh in Power BI Desktop which will fetch and refresh visuals based on updated data, but then just save that updated PBIX file in a OneDrive folder that is published to Power BI service and it should update automatically within an hour.
I just want to make a slight correction to GGetty's solution, which is AWESOME!
At step 4, DO NOT remove the slash "/" before the edit?usp=sharing
Keep it, and then do step 5 so it should look like:
""https://docs.google.com/spreadsheets/d/1nWV8adkjfadkfHWDIAa3ad/export?format=xlsx&id=1nWV8adkjfadkfHWDIAa3ad"
This is amazing! It actually works, unlike the others
Hi,
Tried using this, however after building connection it doesnt load the file properly. With other methods too, constant error is this the table view has 4 columns (not part of usual data base). Even after i move ahead from preview, the actual data doesnt load
I've used this custom connector recently and it worked just fine:
https://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/
Should this be working as well with Power BI Report Server?
Best Regards
GGetty - Thanks for sharing those details - that also works nicely for my files.
I'm curious why the "sharing" method would be more "robust" ?
I'm able to set up manual and scheduled refresh in app.powerbi.com. You just have to specify the web credentials (Anonymous). I dont think its necessary to continually refresh through PBI Desktop.
Hi everyone,
Thanks for keeping this thread going.
Just a comment and some questions on both methods shared by mike_honey and GGetty. I tried both solutions and it worked perfectly, I did notice that you have to be the owner of a file for sharing the links to make it work, i.e even if you have full edit rights on a Google sheet and you publish or share the file you will run into issues.
Has any one else experienced this or can the ownership reason be verified? How do you get around this if you aren't the owner of a file to make this work?
Cheers
Ernst
I tried the https://docs.google.com/spreadsheets/d/idstring/export?format=xlsx&id=idstring method *without* publishing the Google Sheet and my PC tries to download and open the exported Excel file rather than load it in Power Query. Same behavior in Power BI Desktop and Excel 2016.
I can't suggest to my clients to rely on obscurity to keep their data secure.
I thought of using Zapier, but while there's a GSheets trigger for "new worksheet", supported Excel Online actions are limited to rows, you can't create a whole worksheet that way.
I'd love to have a reliable and secure GSheets -> Power BI/Excel connection. I can already do it from Smartsheet and Airtable, it's weird not to have real support for Google Sheets!
mike_honey Thanks for the follow-up. This is great news (anonymous continuous updates of data from Google Sheets directly from app.powerbi.com without having to go through Power BI desktop and republish / overwrite). I never even considered that app.powerbi.com could access a data source configured / specified in Power BI Desktop. I keep thinking of app.powerbi.com as merely the online presence of a Power BI Desktop file and that is just not true. app.powerbi.com can act on its own once established and published via Power BI Desktop. It's just (still?) counterintuitive to me to realize there is ongoing functionality in app.powerbi.com (e.g., connect to web sources) even though the original functionality can only be built via Power BI desktop.
Hi,
I have copied doc link, but couldnt understand in Power Bi where do I need to past it. I mean which data connection I have to use to past this link ?
Hi RasikaOgale
I wasn't clear in my original post on this, but I meant to start the data import from Power BI Desktop, not app.powerbi.com.
Also note my post from 10 minutes earlier on getting the right URL.
Not the publish again =_= I don't want people to see what I put on my Google Spreadsheet =_=
are there any other way ?
GGetty, it works fine for me! Thanks! :smileyvery-happy:
Hi GGetty, thanks a ton for this post. It was immensely helpful and worked like a wonder. Cheers!
GGetty Does using the "export?format=xlsx&id=" publish the sheet to wthe web for access?
Just want to make sure, before I use it, that this solution doesn't cause our sheet to be published or available to anyone other than who it has been shared with.
If this maintians the security of the sheet this is a real slick solution.
Look forward to hearing back from you!
NWBI I think you did have to publish if you wanted to use the "export?format...." solution. You should definitely test first with a spreadsheet that doesn't have sensitive data. Caveat emptor. This thread is nearly two year old. Other folks in the thread talked about setting up a Google Developer ID for more secure access - you might want to try one of those solutions.
This worked just fine on an unpublished sheet.
Interesting.
Good solution, thanks!
Thank You!
It's working.
