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

@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

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors