Excel Script not working in Power Automate

Like (1) Share

Report

Posted on 7 Feb 2025 14:01:09 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:

Business Process Flows

All responses (8)

Answers (1)

rzaneti 3,984 Super User 2025 Season 1 on 21 Feb 2025 at 12:26:57

Like (1)

Report

Excel Script not working in Power Automate

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.
ML-07021357-0 10 on 21 Feb 2025 at 08:45:17

Like (1)

Report

Excel Script not working in Power Automate

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?
Verified answer

rzaneti 3,984 Super User 2025 Season 1 on 21 Feb 2025 at 00:01:15

Like (1)

Report
Excel Script not working in Power Automate

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.
ML-07021357-0 10 on 18 Feb 2025 at 13:30:42

Like (0)

Report

Excel Script not working in Power Automate

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!
rzaneti 3,984 Super User 2025 Season 1 on 18 Feb 2025 at 12:58:39

Like (1)

Report

Excel Script not working in Power Automate

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.
LM-17021756-0 2 on 17 Feb 2025 at 18:00:20

Like (0)

Report

Excel Script not working in Power Automate

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

}

});
ML-07021357-0 10 on 10 Feb 2025 at 14:03:11

Like (0)

Report

Excel Script not working in Power Automate

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.
Suggested answer

rzaneti 3,984 Super User 2025 Season 1 on 10 Feb 2025 at 12:54:07

Like (0)

Report

Excel Script not working in Power Automate

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/

Community site session details

Excel Script not working in Power Automate

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates

Community site session details

Excel Script not working in Power Automate

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates