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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
birddragonman
New Member

This video solve the issue https://youtu.be/3jgdk9rVPw0?feature=shared 

ImkeF
Super User
Super User

Hi there, meanwhile the native Power BI connection to Google Sheets is in preview:
Power Query Google Sheets connector - Power Query | Microsoft Docs

 

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

adelheni
Helper III
Helper III

Hi, i found this article that details how to connect google sheets and power bi.
https://windsor.ai/how-to-connect-power-bi-to-google-sheets/

Anonymous
Not applicable

Hello, 

is there any possibility to connect google sheet if i do not have access to Google API (company limitation).

and i can not setup document availible to anybody (as well company limitation).

 

in fact when i insert the link, it gives me an erreur and i can see in Web View that Google indentification required.

 

 

Hi!

 

I had the same issue - what I did was that I created the project from my private Google Account. You can create a project, add the API:s and download the credentials. Then you just have to share the Sheet with the email in the credentials you downloaded! 🙂

hi, i have created a workbook on google sheets and i want to link it to power bi. This works for me, but as soon as I publish the report to power bi online and share it with someone, the data is not automatically updated. Is it possible to publish the report online and at the same time to update the data from the google workbook? Thanks

MWinter225
Advocate IV
Advocate IV
Honza
Regular Visitor

Hi guys,

 

to connect fully securely, you can use my Power BI - Google Spreadsheets custom connector:

 

https://github.com/fluf1024/PQGoogleSpreadsheet

Hi,

 

I successfully connected to my Google Sheets data using this connector yesterday. However, when I'm hitting refresh I get the following error messages:

 

Capture.JPG

 

Capture.JPG

No columns have changed in my source data, except that new rows got added. How can I resolve this issue?

Is the sheet connected to a form? 

Is the column renamed in the source file? 

Did anything happen to the source file that would change title, position or structure of column? 

Also how many rows in the google sheet?

 

Open the "Advanced Editor" in Query Editor and double check all of your syntax and see if any changes are being applied to that date column in the query.

 

Last piece of advice which usually yields results for me: walk backwards step-by-step of all your data prep steps and see if you can deduce a pattern or where the error ocurred. Usually the errors are so miniscule us humans miss them.

Anonymous
Not applicable

Has anyone managed to get this working with scheduled refresh and personal gateway?

 

I'm getting the following error:error.JPG

 

 

 

@Anonymous See earlier in the thread where @mike_honey talks about using anonymous web access in powerbi service once you first build and create in power bi desktop ...

 

@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 functionalityin app.powerbi.com (e.g., connect to web sources) even though the original functionality can only be built via Power BI desktop.

GGetty
Advocate II
Advocate II

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.

 

Thank You!

 

It's working.

Hi,

pbigooglesheets.pngTried 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/

 

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

hey thanks, can we connect


@ImkeF wrote:

 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/

 



google sheets linked to company id with power bi?

Hi @PowerBI_AB ,

I don't understand what you mean with "linked to company id " (I'm not very familiar with GoogleSheets actually)

 

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

Anonymous
Not applicable

Should this be working as well with Power BI Report Server?

 

Best Regards

Hi @Anonymous ,

you can publish the report also on the report server, but you cannot schedule a refresh unfortunately, due to current limitations with OAuth: https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh  

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.