Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
Hello Jing,
I got it to work! Man, this is tricky.
(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
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:
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.
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
@primolee Maybe you can try pass the whole URL ("http://docs.google.com/........format=xlsx") as a parameter to Web.Contents() function?
Hello Jing,
I got it to work! Man, this is tricky.
(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
@primolee Wow, it's amazing!!! Thank you for sharing this solution. I'm sure it will help a lot of people!!!!!😄
Best regards,
Jing
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.