You’re offline. This is a read only version of the page.
Hi,
SUMMARY
I wrote an Office Script and it is running fine when you manually run it on Excel Online.
However, when I create a flow and run it, it gives me a fetch undefined error.
Do I need to require a fetch? Do I need to add another line of code?
Or is this a bug in Power Automate and Office Script connection?
DETAILS
The script I coded is to retrieve data from an external API. I got no problem accessing and retrieving the data when running the script manually on Excel Online.
Response from the API GET request is successful...
Script completes...
I also made sure to catch the error when requesting from the API source. This is my Office Script code which causes the error "fetch is undefined."
Share
Report
36async function listItems(_url: string, _page? : number): Promise<Response> {
const page = (_page) ? '&page=' + _page.toString() : '';
try {
//Note: This works when running it manually on Excel Online
const response = await fetch(encodeURI(_url + page), {
method: 'GET',
headers: {
'Accept': 'application/json',
'Authorization': `Bearer ${theCog}`
}
}
);
//Power Automate flow doesnt even reach here, which means the error occurs on fetch
console.log(`Request URL: ${response.url}`);
console.log(`Response Status: ${response.status} ${response.statusText}`);
return response;
} catch (e) {
console.log('Fetch Error: ', e);
throw new Error('HTTP Bad Request | Reason: ' + e);
}
}
{
"message": "We were unable to run the script. Please try again.\nRuntime error: HTTP Bad Request | Reason: ReferenceError: fetch is not defined\r\nclientRequestId: <redacted>",
"logs": [
"[2021-06-16T14:42:43.1160Z] Script has started...",
"[2021-06-16T14:42:43.5380Z] Folder ID: 19<redacted>68(BICOL)",
"[2021-06-16T14:42:43.5530Z] Retrieving data from API source...",
"[2021-06-16T14:42:43.5840Z] Fetch Error: "
]
}
My flow is pretty simple and my script DO NOT require additional parameters.
What is missing here? I welcome any suggestion or solutions. 😀
Note that my issue above occurs when running Office Script via Power Automate. Below statement proves this.
My alternative was to create a Custom Connector to retrieve the API data.
"Any external API call fails when a script is run with Power Automate. This is a behavioral difference between running a script through the Excel application and through Power Automate. Be sure to check your scripts for such references before building them into a flow.
You'll have to use HTTP with Azure AD or other equivalent actions to pull data from or push it to an external service."
Is there an example of this somewhere in actual working code? Say you have a simple Excel Script like the following:
All responses (
Answers (async function main(wb: ExcelScript.Workbook) {
const sheet = wb.getActiveWorksheet()
const body = getRequestBodyFromSheetData(sheet)
const res = await fetch(endpoint, { method: 'POST', body: JSON.stringify(body) })
populateResponseData(sheet, await res.json())
}How the heck are you supposed to convert that into a "HTTP with Microsoft Entra ID (preauthorized)" connector? And then how do you call that connector from your TypeScript code?
Can you elaborate in detail? How does a custom connector fix this issue within an Office Script? You still have to have a web address in your Office Script. I'm not getting what a custom connector solves. The web address I am calling (which works perfectly when running inside Excel Online just not Power Automate) is an auto-generated URL from an HTTP trigger flow. The HTTPS flow just gets data from SharePoint and returns a JSON response - that's it. I am not understanding why PA is rejecting it.
After spending all day writing a script that works great inside of Excel Online, I now find I can't even use it in Power Automate as planned. I don't get this. My "external API" is a Power Automate flow itself! I have a flow that executes the office script, and the web request URL it is using in the script is from a flow with "when a http request is received" action. It's two freaking flows in same tenant. Heck, they are in the same SOLUTION. Makes no sense. Every shiny new feature we get from MS seems to be half-baked and rushed to market with a slew of limitations. *sigh*
Note that my issue above occurs when running Office Script via Power Automate. Below statement proves this.
My alternative was to create a Custom Connector to retrieve the API data.
"Any external API call fails when a script is run with Power Automate. This is a behavioral difference between running a script through the Excel application and through Power Automate. Be sure to check your scripts for such references before building them into a flow.
You'll have to use HTTP with Azure AD or other equivalent actions to pull data from or push it to an external service."
Hi there,
Do you happen to have a reference from Microsoft to confirm that fetch via Office Script is actually not supported?
Thanks
It seems that fetching data from an external source through an Office Script is currently not supported by the Power Platform.
The only way is to create a custom connector or use the premium HTTP connector.
