Using Web.Contents with dynamic Google Sheet URL

[primolee]

Hello everyone,

I have the following code to create a query retrieving Google Sheet data.

let
    filePath = "https://docs.google.com/spreadsheets/d",
    relativePath = "Google_Sheet_ID",
    test = Excel.Workbook(Web.Contents(filePath, [RelativePath=relativePath&"/export", Query=[format="xlsx"]]))
in
    test

It will only work if I have filePath and relativePath using equal sign and a text followed by it.  But if I make it a function and throw in filePath and relativePath, Formula.Firewall error occurs.

(filePath as text, relativePath as text) =>
let
    test = Excel.Workbook(Web.Contents(filePath, [RelativePath=relativePath&"/export", Query=[format="xlsx"]]))
in
    test

Because I have several Google Sheet files, so the file ID will change and I want to make it work with dynamic file ID.  Is there a way around it?  Thank you.

Best regards,

David

[v-jingzhang]

Hi @primolee 

Maybe you can try my function code

(FileID as text) =>
let
    //FileID = "2PACX-1vR-QypdKpKG.........._KZe0mn4",
    BasicURL = "https://docs.google.com/spreadsheets/d/e/",
    RelativePathString = FileID & "/pub?output=xlsx",
    Source = Excel.Workbook(Web.Contents(BasicURL, [RelativePath=RelativePathString]), null, true)
in
    Source

I followed the method in this video (Connect Google Sheet to Power BI - YouTube) to connect to a google sheet. And I got a link in the following format. Then tranform it into above function. This function works as I expect.

https://docs.google.com/spreadsheets/d/e/2PACX-1vxxxxxxxxxxxxxNA0u14IXPGozIzu/pub?output=xlsx

Steps to get the link:

1. First you must share the document. Choose “Anyone with the link can VIEW”
2. Then in the menu choose “File” => “Publish to the web”
3. Choose “Entire document” (or the sheet you want to share) and “Microsoft Excel (.xlsx)”, click Publish

And you will see a link there.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[primolee]

Hello Jing,

Thanks for the help.  Just gave it a try and I guess the main difference is me not publishing Google Sheet to the web.  As data in my Google Sheet is sensitive, I cannot publish it to the web.  I can only share it as "Anyone with the link can access".

https://docs.google.com/spreadsheets/d/{Google Sheet ID}/export?format=xlsx

However, funny thing is that stand-alone query works but don't work if it is a function.

Guess there is no way out of it and I will have to manually add separate queries...

Thank you so much for the help!

Best regards,

David

[v-jingzhang]

@primolee Maybe you can try pass the whole URL ("http://docs.google.com/........format=xlsx") as a parameter to Web.Contents() function? 

[primolee · Accepted solution]

Hello Jing,

I got it to work!  Man, this is tricky.

1. In Power BI Desktop, need to go to Option > Privacy and select "Ignore Privacy Level Setting"
2. In Web.Contents, use relative path to work in Power BI Service as well
3. In Web.Contents, use "https://docs.google.com" as the basic URL and put the rest in RelativePath.

(FileID as text) =>
let
    //FileID = "Google Sheet File ID",
    BasicURL = "https://docs.google.com/",
    RelativePathString = "spreadsheets/d/" & FileID & "/export?format=xlsx",
    Source = Excel.Workbook(Web.Contents(BasicURL, [RelativePath=RelativePathString]), null, true)
//    Source = Excel.Workbook(Web.Contents(BasicURL&RelativePathString))
in
    Source

Reason for point 1 is to bypass Formula.Firewall issue.  And reason for point 3 is, if you have anything else in the basic URL (such as https://docs.google.com/spreadsheets/d/), Power BI Service will ask for authentication.  However, https://docs.google.com can be accessible using anonymous.

Moreover, we don't need to publish Google Sheet to the web, and it works fine also.

Tada~~~

Thank you for your help!!!!!!!

Best regards,

David

[v-jingzhang]

@primolee Wow, it's amazing!!! Thank you for sharing this solution. I'm sure it will help a lot of people!!!!!😄

Best regards,

Jing

[primolee]

Hello Jing,

Yes it will work but won't work in Power BI Service when refreshing.  Following error will occur:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

And I remember vaguely that this is the reason why relative path is needed when using Web.Contents so that refresh will work in Power BI Service.

Any other idea?

Best regards,

David