Google spreadsheet integration

[mconcli]

I'm sorry for repeating this issue, but even looking at the TumbleRoad tutorial I wasn't able to do what I wanted.

I need to take my data from Google spreadsheets into PowerBI via API (in order to get data automatically), is it possible? Could someone post a tutorial?

Thank you.

[nancy1989]

Hello Mconcli 

I also suffered this type of problem when I found a blog which solved my all issues.  so go through the SSIS Facebook API hope it also works for you.

[v-yuezhe-msft · Accepted solution]

Hi @mconcli,

Based on your description, it seems that you follow the guide in this tutorial to connect to Google Spreadsheet in  Power BI Desktop. I test this process, and I only get a "Key" and "Column Heading" column with none of my sheets data.  What is the result in your scenario? I would recommend you contact @trebgatte for further help.

And as far as I know, there are no official articles or other blogs stating that using Google's API to connect to Google Spreadsheet. An idea about connecting to Google sheets in Power BI has been submitted in this link, please vote it up.

Currently, you can connect to files on Google Spreadsheet from Power BI Desktop via Get Data->Web entry. Assume that you connect to a Excel file, open it in Google Spreadsheet, go to File / Publish to Web,  then change the selection from Web Page to Microsoft Excel.  Copy the generated link and input the URL in Get Data->Web Window of Power BI Desktop, then enter your Google account and Password. For more details, please review the following screenshots.

Thanks,
Lydia Zhang

[majena]

Hi @v-yuezhe-msft I am following the instruction (while publishing to Web option "Require viewers to sign in with their [Company] account" was checked if that changes anything - I don't want this source to be widely available) and I get stuck cause I get invalid credentials account. What am I doing wrong? I am using my google login and pass that are correct (I retried several times and checked by simply logging in via Google Docs - it works).

[Anonymous]

I tested a lot, and I am sorry to tell you: the only way to make PBI read data from Google Sheets is when you publish to the web as a public document, where anyone can see the data.
credentials for google sheets does not work on PBI, and there is nothing planned in the near future...
this idea below is under review for more than 2 years! why is it taking so long?
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/6656584-connectivity-to-google-sh...

[paulob_involves]

I'm guessing Microsoft wants people to use Office Online instead (which integrates fine with Power BI).

I bet someone will find a workaround before an official integration with Google Spreadsheets is released. All we need is a service that is able to get data from a (private) google spreadsheet and throw it in a (private) Excel Online document. I tried to get Excel to do it directly, but got the same credential issue - therefore the need for an intermediate service.

[majena]

I am following the instruction provided, but I keep on getting the 'invalid credentials' error. I am using my google docs login and pass, the document while publishing to Web had the option "Require viewers to sign in with their [CompanyName] account" enabled - as I don't want the source to be widely available. What am I doing wrong? I am pretty sure there were no typos in credentials (I retried several times). @v-yuezhe-msft I would really appreciate some help here

[paulob_involves]

Same here.

Currently Google Spredsheets have an option for publishing only for users of your organization. I tried this to remain my document private, and follow the steps indicated by Lydia, but it didn't work. It pops a warning message saying the credentials cannot be used for this web source.

Still no solution as I cannot openly publish my documents.

[Jayadev]

I saw a video to connect powerbi to Google sheets.  I think it's more logical and should solve the problem.

https://www.youtube.com/watch?v=8lWR-_L52Qs

[Anonymous]

but I don´t want to publish, as my data is and must remain private.

I understand that the whole world will have access to my data at the moment I click on publish button... am I right?

I think I need to use google API to restrict access to my sheets, if yes, how can I use google API to make Power BI read my sheets?
if no, how can I make Power BI read my restricted sheets?

[ayami123]

you have the some question as I am, I don't want to publish my google sheet so i'm asking are there other solutions for this ?

[mpaler]

Lydia,

Are sure you're able to access a google sheet that has Access Restrictions? I find that i must give open world access to the sheet in order to access it via Power BI. Can you confirm that you're still able to use the method you describe above? What type of google account are you using? Any further information is greatly appreciated.

[abhitiwa]

@mpaler Even I am stuck here. I am unable to link a Google Sheet with Access Restrictions. I used my work account to create this sheet. How do I give open world access? please help

[mconcli]

Thank you for your help, I'll try to contact @trebgatte!