cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

How to connect google sheet to Power BI

Good day,

 

I would like to ask if how can I import google sheet to power BI?

 

For your assistance.

 

Thank you and God bless,

Christian

1 ACCEPTED SOLUTION
Power Participant
Power Participant

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.

View solution in original post

69 REPLIES 69

Hi @trebgatte,  thanks a lot - missed that.

I followed all the steps and have been prompted with an "Edit credentials" dialogue.

Which used Basic with my google credentials, also anonymus, but nothing worked.

How is this supposed to work?

 

Thanks, Imke

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@trebgatte I played around with it some more and could make your code work only if I allow everyone access who has a link. Then anonymus connections works, as the API key does it's job (just like your sample file in the comments with the cocktail receipes)

 

Do you know how to tweak the code so that it works with specific users and a user authentication dialogue pops up ?

 

ShareSettingsGoogleSheets.jpg

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi there,

I found a custom connector for OAuth2 - authentication for Google Sheets. See how this worked for me here: http://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Basically, what you show to get the client secret and all is what I did to get mine working and it works in the service since it's not a custom connector. Perhaps that's what people are missing. 

ImkeF, I can't find this dialog box with Basic Authentication in my Power BI. The web connector is to simples, like this: 

 

web-connector.PNG

Yes, paste the URL that you've copied from Google sheet there.

The dialogue for the authentication will appear after that step. 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

The next step is not the authenticator, is this screen, with a blank document: 

 

next-step-power-bi.PNG

 

 

Thanks!

@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

Continued Contributor
Continued Contributor

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!

Anonymous
Not applicable

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?

Hi @Anonymous,

 

It seems Google have changed their File / Publish to the web pop-up, but actually it now seems easier.  If you change the 2nd setting from "Web page" to "Microsoft Excel (.xlsx)", it now builds a link that works without modification.

 

Google Sheets - Publish to web as Excel.PNG

 

 

Hello

 

I tried to apply your solution however I got this error

I could not see my data

 

column 1  column2

 

info icon  navigation to the webpage was cancelled

info icon 

              what you can try

              refresh the page

any idea please?

Thanks

 

 

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

Power Participant
Power Participant

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.

View solution in original post

How to get data from web in the Power BI pro (not desltop) ?????????

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!!

 @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

Hello Mike,

 

Could you help me locate the basic Get data / From web in PowerBI. I am registered as a free user.

 

Thanks

Thanks for this! It works on my Google Spreadsheets.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors