cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
christianjon17
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
mike_honey
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

ImkeF
Super User II
Super User II

@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

ImkeF
Super User II
Super User II

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!

mike_honey
Power Participant
Power Participant

@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

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

mike_honey
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors