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
NWBI
Frequent Visitor

@GGetty Does using the "export?format=xlsx&id=" publish the sheet to wthe web for access?

 

Just want to make sure, before I use it, that this solution doesn't cause our sheet to be published or available to anyone other than who it has been shared with. 

 

If this maintians the security of the sheet this is a real slick solution. 

 

Look forward to hearing back from you!

@NWBI I think you did have to publish if you wanted to use the "export?format...." solution. You should definitely test first with a spreadsheet that doesn't have sensitive data. Caveat emptor. This thread is nearly two year old. Other folks in the thread talked about setting up a Google Developer ID for more secure access - you might want to try one of those solutions.

NWBI
Frequent Visitor

This worked just fine on an unpublished sheet. 

 

Interesting.

 

Good solution, thanks!

@NWBI glad to hear it. I imagine it all worked smoothly as you as the author were logged in to Power BI and Google with correct credentials. It would be interesting to hear what happens if other users attempt to view the Power BI visualizations on PowerBI.com or by opening the Power BI .PBIX file and clicking refresh.

 

Glad you got what you needed. Cheers.

Hello,

I have succesfully connected a published Google Sheet to Power BI Desktop, published the report to app.powerbi.com and set up Gateway for it. When refreshing data I get:

 

Something went wrong
 
Cannot connect to the mashup data source. See error details for more information.
Please try again later or contact support. If you contact support, please provide these details.
 
Underlying error messageThe Web.Page function requires Active Scripting to be enabled in Internet Explorer options. See https://go.microsoft.com/fwlink/?LinkId=506565 for details on how to enable Active Scripting.
 
Has anyone experienced this?

Many thanks,
Viktor

@NWBI glad to hear it. I imagine it all worked smoothly as you as the author were logged in to Power BI and Google with correct credentials. It would be interesting to hear what happens if other users attempt to view the Power BI visualizations on PowerBI.com or by opening the Power BI .PBIX file and clicking refresh.

 

Glad you got what you needed. Cheers.

Hi GGetty, thanks a ton for this post. It was immensely helpful and worked like a wonder. Cheers!

This is amazing! It actually works, unlike the others 

I just want to make a slight correction to GGetty's solution, which is AWESOME!

 

At step 4, DO NOT remove the slash "/" before the edit?usp=sharing

 

Keep it, and then do step 5 so it should look like:

 

""https://docs.google.com/spreadsheets/d/1nWV8adkjfadkfHWDIAa3ad/export?format=xlsx&id=1nWV8adkjfadkfHWDIAa3ad"

GGetty, it works fine for me! Thanks! Smiley Very Happy

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 ?

Hi @RasikaOgale 

 

I wasn't clear in my original post on this, but I meant to start the data import from Power BI Desktop, not app.powerbi.com. 

 

Also note my post from 10 minutes earlier on getting the right URL.

Not the publish again =_= I don't want people to see what I put on my Google Spreadsheet =_=

are there any other way ?

In case anyone needs this, here's a secure way to access Google Sheets. 

 

https://tumbleroad.com/2016/07/07/free-marquee-google-sheets-template/ 

Hey @trebgatte I can follow all these steps but the only thing that loads is your 100 columns - none of my Google Sheets data. Am I doing something wrong.

Did you get your own developer id? 

I got asked by another client to look into a secure/API connection direct to Power BI again. 

 

My conclusion is that the only Google API Authorization option now is the dreaded oAuth2, which is beyond the capabilities of Power BI. 

 

I did have a look at Treb's blog post, but that seems to get tripped up by the same change in the Google API.

 

FWIW my proposed workaround is to use Microsoft Flow to read from Google Sheets and write the rows to an Excel file stored in OneDrive, running on a schedule. I got a crude prototype running quickly so it seems viable.

 

Hope this saves others some time.

 

Obviously the best solution would be for Microsoft to add Google Sheets connectivity with integrated authorization - if you agree please add your votes and comments here - maybe it will be delivered before it's 3rd birthday ...

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6656584-connectivity-to-google-sh...

 

Deleted the thread because the "solution" actually didn't work.

 

 

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

delete

Hi @dosansil and @Anonymous:

I made a stupid mistake here, so the solution doesn't work unfortunately: I didn't recognize that the sheet I was referrig to was still published to web. So despite me restricting the access in the "share"-area, there was still the published web-option which accepted my credentials. But the problem here is, that I actually didn't need credentials, as the anonymus connection would have worked as well.

 

I'm very sorry to have wasted your time here!! (Will edit/delete my orignal post so that others will not get fooled as well)

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

Hello, ImkeF. Don't worry. You are always helpful and gently. I'm grateful for your try and good assistance. If you discovery a solution, let me know. 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