Power Platform Community Forum Thread Details

Excel script fails probably because of formulas

I'm trying to automate a new workbook each 1st working day of the month which create a table with all the working days of that month.

Using the record functionality I was able to create the script and the script is working when I run the script in Excel (desktop and online).

However, when I try to create a power automate flow using this script I get an error:

"We were unable to run the script. Please try again.
Office JS error: Line 4: Range setFormulasLocal: The argument is invalid or missing or has an incorrect format."

Script:

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Set range A1:B3 on selectedSheet

selectedSheet.getRange("A1:B3").setFormulasLocal([["Week","Date"],["=WEEKDAY(B2)","=WORKDAY(EOMONTH(TODAY();-1);1)"],[null,"=WORKDAY($B$2;SEQUENCE(NETWORKDAYS($B$2;EOMONTH($B$2;0))-1))"]]);

// Set format for range B:B on selectedSheet

selectedSheet.getRange("B:B").setNumberFormatLocal("d-m-jjjj");

// Auto fill range

selectedSheet.getRange("A2").autoFill();

// Set range A2 on selectedSheet

selectedSheet.getRange("A2").setFormulaLocal("=WEEKNUM(B2)");

// Auto fill range

selectedSheet.getRange("A2").autoFill();

It looks like the way the formula was used in the script is not working in the power automate flow.

Anyone has more experience with this or have an alternative?

Hi @QueNgo ,

It looks like a Script error, so it ideally should not be working neither in Power Automate, nor in Excel directly by suing the "Automate" Pane.

According to the error message, the problem is in line 4, which is the following:

selectedSheet.getRange("A1:B3").setFormulasLocal([["Week","Date"],["=WEEKDAY(B2)","=WORKDAY(EOMONTH(TODAY();-1);1)"],[null,"=WORKDAY($B$2;SEQUENCE(NETWORKDAYS($B$2;EOMONTH($B$2;0))-1))"]]);

I ran some tests here and it looks like your error is caused by the semicolons in the formulas. I recommend you to change them for commas directly in the script, save it, and try to run it again.

Also, for further reference about how to start to work with Office Scripts (sometimes it can be tricky!), I recommend you to check this blog post: http://digitalmill.net/2023/06/19/get-started-with-office-scripts/

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 Solved.
If this answer helps you in any way, please give it a like.

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

Quick Links

Leaderboard > Power Automate