cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gaillardb Frequent Visitor
Frequent Visitor

Re: How to connect google sheet to Power BI

Mike,

 

would you please help me locate the Get Data / From Web ...

 

Get data gives me the following options:

Import of Connect data

Files or Database

 

In database, I can't seem to find a way to paste a simple URL source. Thanks!!

GGetty Regular Visitor
Regular Visitor

Re: How to connect google sheet to Power BI

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.

 

mike_honey
Advisor

Re: How to connect google sheet to Power BI

 @gaillardb - It sounds like you are starting from app.powerbi.com ?  I didnt make it clear, but actually my solution uses Power BI Desktop.  That has much more "Get Data" functionality.

 

BTW these forums arent great - if you want someone to be notified of your reply you need to mention them eg @xyz

mike_honey
Advisor

Re: How to connect google sheet to Power BI

@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.

 

GGetty Regular Visitor
Regular Visitor

Re: How to connect google sheet to Power BI

@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.

ernestoj Visitor
Visitor

Re: How to connect google sheet to Power BI

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

otravers Member
Member

Re: How to connect google sheet to Power BI

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!

RasikaOgale Frequent Visitor
Frequent Visitor

Re: How to connect google sheet to Power BI

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 ?

Chief4ik New Member
New Member

Re: How to connect google sheet to Power BI

Anonymous
Not applicable

Re: How to connect google sheet to Power BI

this parameter ?format=xls does not work anymore.

somehow, I managed to make it work using ?gid=0&single=true at the end of the published link.

but I don´t want to publish, as my data is and must remain private.

how can I use google API to make Power BI read my sheets?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 43 members 811 guests
Please welcome our newest community members: