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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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

71 REPLIES 71

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors