connect google spreadsheet to power bi without publishing the document

[bbbt123]

Hi, I want to use google spreadsheet as source data. I can see this is possible if I publish the document to the web but this is not ideal since the document contains sensitive information. I am using power bi with my corporate account and the google spreadsheet is also created with my corporate accunt, how can I use google spreadsheet as a source? Thank you in advance

[v-alq-msft]

Hi, @bbbt123 

In Google spreadsheet, you require private access to the sheet data source instead of public access. At present, I think It is unlikely to be achieved except to build a custom connector. I tried to download it using the API Key. The basic method is not available. The RESTFul API of Google sheet cannot call Export function.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft · Accepted solution]

Hi, @bbbt123 

Based on your description, the easiest way is to Get Data / From Web, then enter the URL to your google sheet with "&output=xls" on the end. Power BI then analyses the resulting Excel file, showing the tabs as tables , which you can edit and manipulate.

About limitations to publish to web, it is unsupported to use row level sercurity. It is suggested to use other ways to share with colleagues. You may refer the following links.

Publish to web:

https://docs.microsoft.com/en-us/power-bi/service-publish-to-web

Ways to share:

https://docs.microsoft.com/en-us/power-bi/service-how-to-collaborate-distribute-dashboards-reports

Samples:

https://community.powerbi.com/t5/Power-Query/How-to-connect-google-sheet-to-Power-BI/td-p/14587/page...

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Pick the excel file tha you want to connect from Google spreadsheet. go to the file option in excel and click on share and Copy the link add at end of the link we need to remove the edit?usp=sharing to  /export?format=xlsx and connect it from web url source from desktop.

make sure no spaces in link.

Regards,

Rajendra

[kitkit929]

Hi @rajendraongole1 , I've replaced the edit?usp=sharing to  /export?format=xlsx as per your instructions. However, power query is only picking up "Document" but not the worksheets/tabs in my file? Is there a way I can get it to show the worksheet/tabs in the Navigaotr pane once I've choosen to get the power query data from Web - google sheet? Thanking you in advance. 

[bbbt123]

Hi, thank you for replying. It doesn't work, I get an empty table with this message Column2

Navigation to the webpage was cancelled

What you can try:

Refresh the page.

[bbbt123]

Hi, to be more precise, it works if I share the link with anyone, however I have to restrict the access to anyone, only people in the company can be able to access the link

[rajendraongole1]

Hi

Can you try the below possibility?

https://community.powerbi.com/t5/Power-Query/Connecting-to-a-Google-spreadsheet/m-p/166657

Last time when i try to connect the google spreadsheet, i have followed the explained process. Check and let us know if the above link is useful.