Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Memorable Member
Memorable Member

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.

View solution in original post

72 REPLIES 72

I'll have to revisit the blog post as there were some changes on how to get the developer id on the Google side. Once you have the developer id, it connects securely and off you go.

 

I used it last week so I know it is working. The new API reports back the number of columns now but I have no idea where they are deriving that number. I had a nine column sheet and it told me I had 15 columns. If you set the fold to 15, it really jacks up the data.

 

Updates will be forthcoming.

  

 

Treb Gatte | MVP | TumbleRoad.com | PowerBICertification.com

Anonymous
Not applicable

how do I get a developer id from google?

I have the basic process here in my post. https://tumbleroad.com/2016/07/07/free-marquee-google-sheets-template/  Do not use my sample developer id as this will not allow you to connect to your data. 

 

However, this is the latest Google docs on the process: https://developers.google.com/identity/sign-in/web/devconsole-project  Unfortunately there are no screenshots to walk you through this process. If you use the terms from the process above on getting the ID process and the updated doc link, you should be able to navigate through the new Google UI. I'm putting this on the backlog for update.

 

Treb Gatte | MVP | TumbleRoad.com | PowerBICertification.com

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

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!

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals
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

 

 

Anonymous
Not applicable

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
Memorable Member
Memorable Member

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors