Forum Discussion

primolee's avatar
primolee
Icon for Helper V rankHelper V
5 years ago
Solved

Using Web.Contents with dynamic Google Sheet URL

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

Need Help
  • primolee's avatar
    primolee
    5 years ago

    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

6 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      primolee
      Icon for Helper V rankHelper V
      5 years ago

      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