Unanswered

Triggering a Power Automate flow from Office Script sending HTTP request

(0) Share

Report

Posted on by MILL_753159

I have a Power Automate flow that is triggered by receiving an HTTP request. I have an Excel worksheet that has that URL inside and I want to have a button to click and send it. I created an Office Script that would grab that URL and send the request, and the script works fine when I have it saved on my OneDrive, but due to my organization sharing restrictions I cannot add a button when the script is on OneDrive. So, I saved a copy of the script on SharePoint and was able to add a button to my worksheet. It is the exact same script, but when I try to run it, it fails to send the http request.

Here is my Office Script Code:

function main(workbook: ExcelScript.Workbook) {

// Run HTTP request.

let httpRequest = new XMLHttpRequest();

let myPath = workbook.getActiveWorksheet().getRange("B2").getText();

httpRequest.open("GET", myPath, false);

httpRequest.send();

}

And here is the error (I have not shared the actual URL, but it does work fine, I have verified it many times):

Line 6: Failed to execute 'send' on 'XMLHttpRequest': Failed to load 'https://myURLisHere'

If I add in some error handling and change the request to asynchronous so it will complete it fails with httpRequest.status of 0, which doesn't tell me anything.

What am I missing about saving the Office Script on Sharepoint that is not allowing the http request to send?

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (1)

SudeepGhatakNZ 14,394 Most Valuable Professional on at

Like (0)

Report

@MILL_753159 ,

Did you test the same HTTP request directly (outside of Office Script) using tools like Postman or cURL. This will help isolate whether the issue lies with the script or the SharePoint environment.

The issue you're experiencing could also be due to restrictions related to Cross-Origin Resource Sharing (CORS) when trying to send an HTTP request from within an Office Script saved on SharePoint. When running scripts within SharePoint or from OneDrive, certain security restrictions apply, which can block the HTTP request.

Can you also check the property of the HTTP trigger? Try with this setting on.

Was this reply helpful? Yes No
MILL_753159 8 on at

Like (0)

Report

Thank you for the suggestion, I tried using cURL to test the HTTP request from the same SharePoint folder and it successfully triggered the flow. I do have the HTTP request trigger in the flow set to Anyone already. If this is a CORS issue, is there a way to solve that when the server side is controlled by Power Automate?

Was this reply helpful? Yes No
JOAS_Niels 180 on at

Like (0)

Report

Hi MILL_753159,

Did you manage to resolve this issue? I have the same problem.

Thnx!

Was this reply helpful? Yes No
Verified answer

AndreasN 318 Super User 2024 Season 1 on at

Like (0)

Report

Hi,

Have you tried creating this via VBA instead of an office script?

The following code will execute Power Automate from a e.g. button.

Sub RunPowerAutomate()

Dim myPath As String

myPath = Sheets("Sheet1").Range("B2").Value

With CreateObject("MSXML2.XMLHTTP")
.Open "GET", myPath, False
.Send
End With

End Sub

Remember to change "Sheet1" to the appropriate worksheet name. Assign the "B2" cell, to the cell where you place your GET URL from your Power Automate "When a HTTP request is received".

Don't share the URL, but this is where you copy it from in the action.

This URL you place in your "B2" cell, if that is where you choose to have the URL in the sheet. Again, remember to store the URL in the same cell, as the cell you provide in the VBA code.

In the URL you will find {User} and {Application}. You can concat the URL in your B2 cell, so it includes data from other cells.

E.g. =CONCAT("URL";N3;"/";M3;"URL")

Again I cannot share the URL I use, but the concat just replaces the {USER} value in the URL with the value in N3, and it replaces the {Application} value in the URL with M3.

These values are then send to Power Automate, when you run the script, given you've set up the flow correctly.

This approach, using VBA works just fine for me. For reference, this is the guide I used to set it up initially.

How to run Power Automate from Excel with Office Scripts or VBA

Was this reply helpful? Yes No
JOAS_Niels 180 on at

Like (1)

Report

Hi Andreas,

Thanks for your answer, that is exactly what I did in the meantime.

When I created my sheet I couldn't use VBA, because Power Automate couldn't read from Excel sheets with macros. But that's not the case anymore, so VBA is now an option again.

Was this reply helpful? Yes No
MILL_753159 8 on at

Like (0)

Report

Hi JOAS Niels,

I was in the same situation as you when I posted this, that Power Automate was not compatible with VBA, but as you mentioned, that is no longer the case. I was not able to get the script to work if it was saved in a Sharepoint directory, so I just saved it to OneDrive and run the script from the scripts list, not with a button. I think if you can only work with Sharepoint and you want a button, VBA will be the way to go.

AndreasN,

Thank you for the VBA tips, I will mark that as the answer, now that Power Automate works with .xlsm files.

Was this reply helpful? Yes No