Excel Script not working in Power Automate

(1) Share

Report

Posted on by ML-07021357-0

Hi,

I am experiencing an issue with an Excel Script on Power Automate.

When I run the Script from the Workbook everything is fine, but when I run it through Power Automate it goes in timeout and then fails.

Debugging, it seemed that the issue is on these lines of code:

let dataValidation = borderRange.getDataValidation();
    
    // set data Validation on borderRange
    dataValidation.setRule(dataValidationRule);
    // Data validation error alert changed on borderRange
    dataValidation.setPrompt({ showPrompt: true, message: "", title: "" });
    // Data validation error alert changed on borderRange
    dataValidation.setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.stop, message: "", title: "" });
    // Data validation ignoreblanks changed on borderRange
    dataValidation.setIgnoreBlanks(true);

When I comment the code before setting data Validation it works, so it seems the problem is the setRule()

How can I fix this issue?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

rzaneti 4,262 Super User 2025 Season 2 on at

Like (0)

Report

Hi,

You mentioned you are getting a timeout error. How long does the Run Script action take to run when timed out?

I'm asking it because there is a limitation for scripts to run from Power Automate: they may take no longer than 120 seconds. If you have a script that takes more than that, my recommendation is to break it down your code into two or more scripts, to ensure that it will always keep within the 120s.

Source: https://learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits?tabs=business

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Accepted Answer.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/
https://www.linkedin.com/in/raphael-haus-zaneti/

Was this reply helpful? Yes No
ML-07021357-0 10 on at

Like (0)

Report

Hi @rzaneti,

Thank you for your answer!

Without this piece of code, the script works properly on Power Automate (except, of course, the validation rules are missing), and if I run a script with only this piece of code, it still gives me timeout error, so I think it's not a matter of how much does it take, but it's this piece of code specifically that doesn't work on Power Automate, so breaking down the script doesn't solve the problem.

Was this reply helpful? Yes No
LM-17021756-0 2 on at

Like (0)

Report

I have exactly the same issue... When i try to run just that snippet of code that is breaking through the workbook it takes 1 second, but through the power automate flow it just gives Gateway timeout.

Have you found a solution yet?

Here's my piece of code:

const dataValidation = targetColumn.getRangeBetweenHeaderAndTotal().getDataValidation();

const dynrange = sheet2.getRange("A1:A16");

// Set the content of the drop-down list

dataValidation.setRule({

list: {

inCellDropDown: true,

source: dynrange

}

});

Was this reply helpful? Yes No
rzaneti 4,262 Super User 2025 Season 2 on at

Like (1)

Report

Hi folks,

Sorry for the delayed response: I wasn't notified about the new posts here, but luckily this thread came to the top of the list and I found it :)

I'm running some tests from my end and facing the same issue. I did an extensive review in the documentation and didn't find anything, so I raised an issue in the Official Docs GitHub. You can check it here: https://github.com/OfficeDev/office-scripts-docs/issues/779

I believe that it's probably a limitation in the connection between Power Automate and Office Scripts, but let's wait to see how our GitHub issue will be answered.

I will let you know as soon as I get a response from them.

Let me know if it works for you or if you need any additional help!

If this is the answer to your question, please mark the post as Accepted Answer.
If this answer helps you in any way, please give it a like.

Check more Power Platform content on my website.
Lets connect on LinkedIn.

Was this reply helpful? Yes No
ML-07021357-0 10 on at

Like (0)

Report

Hi everyone!

Thank you so much @rzaneti! This is very helpful, I hope it is not a limitation of Power Automate!

@LM-17021756-0 this is exactly what I am experiencing! Let's hope rzaneti finds a solution!

Was this reply helpful? Yes No
Verified answer

rzaneti 4,262 Super User 2025 Season 2 on at

Like (1)

Report
Hey @ML-07021357-0 and @LM-17021756-0,

Microsoft team is still investigating the bug. For @LM-17021756-0's issue, they offered a nice workaround in this meantime:

function main(workbook: ExcelScript.Workbook) { const ws = workbook.getWorksheet('Sheet1'); const cell = ws.getRange('B2'); const validationRng = ws.getRange("I3:I5"); const values = [].concat.apply([], validationRng.getTexts()).join(','); cell.getDataValidation().setRule({ list: { inCellDropDown: true, source: values} }); }

This piece of code is basically joining the values extracted from an Excel range into a single string separated by commas (const values), and using it as source for the data validation. The downside of this approach is that it won't capture any changes in the original validation range (I3:I5, in the sample code) until you run the script again.

@ML-07021357-0, I'm not sure on which data validation rules are you trying to apply with the dataValidationRule variable, but feel free to share it so we can try to troubleshoot it!

Let me know if it works for you or if you need any additional help!

If this is the answer to your question, please mark the post as Accepted Answer.
If this answer helps you in any way, please give it a like.

Check more Power Platform content on my website.
Lets connect on LinkedIn.

Was this reply helpful? Yes No
ML-07021357-0 10 on at

Like (1)

Report

Hi @rzaneti,

I didn't write the value of the variable "dataValidationRule", but actually it was built the same way @LM-17021756-0 did, so the workaround was good for me too and it worked!

I'll adopt it in the meantime and when the Microsoft team will fix the bug I'll change my piece of code again.

Should I mark your answer as "Correct answer" or should I wait for Microsoft team to fix the bug?

Was this reply helpful? Yes No
rzaneti 4,262 Super User 2025 Season 2 on at

Like (1)

Report

That's great news, @ML-07021357-0!

I would say that we can consider it as a verified answer, since it works and will be easily found by users that have the same issue in the future. In any case, I will monitor the GitHub issue and post any updates here, although I think it may take a weeks or months for they to figure it out.

Was this reply helpful? Yes No