How to connect google sheet to Power BI

[christianjon17]

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

[NWBI]

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

[GGetty]

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

This worked just fine on an unpublished sheet. 

Interesting.

Good solution, thanks!

[GGetty]

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

[ViktorL]

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

[GGetty]

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

[kamalmsharma]

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

[DavidBenaim]

This is amazing! It actually works, unlike the others 

[jkaemmerling]

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"

[dosansil]

GGetty, it works fine for me! Thanks! 

[RasikaOgale]

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 ?

[mike_honey]

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.

[ayami123]

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

are there any other way ?

[trebgatte]

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/ 

[GGetty]

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.

[trebgatte]

Did you get your own developer id? 

[mike_honey]

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

[ImkeF]

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

[Anonymous]

delete

[ImkeF]

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)

[dosansil]

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!