Excel Script Failing to Run

(1) Share

Report

Posted on by The_hooligan22

Hello all,

I'm trying to get a flow to run the flowing excel script:

1 function main(workbook: ExcelScript.Workbook) {

2 let selectedSheet = workbook.getActiveWorksheet();

3 // Set range B4 on selectedSheet

4 selectedSheet.getRange("B4").setFormulaLocal("=RAND()");

5 // Auto fill range

6 selectedSheet.getRange("B4").autoFill("B4:B96");

7 let interviewersEnglish = workbook.getTable("InterviewersEnglish");

8 // Paste to table interviewersEnglish cell in row 0 on column Rand from range B4:B96 on selectedSheet

9 interviewersEnglish.getColumn("Rand").getRangeBetweenHeaderAndTotal().getRow(0).copyFrom(selectedSheet.getRange("B4:B96"), ExcelScript.RangeCopyType.values, false, false);

10 }

When running the script in excel it runs fine, but when I try to run it in power automate, I get the following message:

We were unable to run the script. Please try again.
Office JS error: Line 6: Range autoFill: The argument is invalid or missing or has an incorrect format.
clientRequestId: 4bd5dc7d-467b-4a77-8c15-47a53afbd31b

I'm unsure as to what could be the problem. Thanks!

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

trice602

15,404 Super User 2025 Season 2 on at

Like (1)

Report

HI @The_hooligan22 ,

It looks like you are missing the autoFillType value and here's the documentation. ExcelScript.AutoFillType enum - Office Scripts | Microsoft Learn

Fields

fillCopy	Populates the adjacent cells with data based on the selected data.
fillDays	A version of "FillSeries" for dates that bases the pattern on either the day of the month or the day of the week, depending on the context.
fillDefault	Populates the adjacent cells based on the surrounding data (the standard AutoFill behavior).
fillFormats	Populates the adjacent cells with the selected formulas.
fillMonths	A version of "FillSeries" for dates that bases the pattern on the month.
fillSeries	Populates the adjacent cells with data that follows a pattern in the copied cells.
fillValues	Populates the adjacent cells with the selected values.
fillWeekdays	A version of "FillSeries" for dates that bases the pattern on the day of the week and only includes weekdays.
fillYears	A version of "FillSeries" for dates that bases the pattern on the year.
flashFill	Populates the adjacent cells by using Excel's Flash Fill feature.
growthTrend	A version of "FillSeries" for numbers that fills out the values in the adjacent cells according to a growth trend model.
linearTrend	A version of "FillSeries" for numbers that fills out the values in the adjacent cells according to a linear trend model.

Please mark as a solution and/or give me a thumbs up for replying and offering help! Always glad to help! Tom 👍🏽

Was this reply helpful? Yes No

trice602 15,404 Super User 2025 Season 2 on at

Like (0)

Report
Example:

Example: // Autofill the connected range. C2:C3 are filled in with dates. C4:C54 are blank. dataRange.autoFill("C2:C54", ExcelScript.AutoFillType.fillDays); }

Please mark as a solution and/or give me a thumbs up for replying and offering help! Always glad to help! Tom 👍🏽

Was this reply helpful? Yes No
The_hooligan22 22 on at

Like (1)

Report

So I added the autoFillType value so now line 6 is:
selectedSheet.getRange("B4").autoFill("B4:B96", ExcelScript.AutoFillType.fillDefault);
I'm still getting the same error from power automate: "Office JS error: Line 6: Range autoFill: The argument is invalid or missing or has an incorrect format."

Was this reply helpful? Yes No
trice602 15,404 Super User 2025 Season 2 on at

Like (1)

Report

Hi @The_hooligan22 ,

Can you confirm there is in fact data in the adjacent cells please? Everything else looks right and the last thing I can think of is there's no data next to it to autoFill. Always glad to help! Tom

Was this reply helpful? Yes No
The_hooligan22 22 on at

Like (1)

Report

Yea, there is data in the adjacent cell. I've also tried fillCopy & fillValues instead of fillDefault & I still get the same message in Power Automate. Though the script does run in excel.

Was this reply helpful? Yes No
trice602 15,404 Super User 2025 Season 2 on at

Like (1)

Report

Sorry, I wish I was more help here! Always glad to help! Tom

Was this reply helpful? Yes No
Verified answer

leemager 153 on at

Like (1)

Report

@The_hooligan22 as long as you know the range it's not much more effort to use a good old For Loop rather than the autofill function (which after looking online seems like it's a common problem with the office scripts API), attached is an example I've just tested which runs fine when called from Power Automate (might need to refresh your Excel file to see the results as it didn't update 'live' for me).

for (let i = 3; i <= 95; i++) {
        let cellAddress = "B" + (i + 1);
        selectedSheet.getRange(cellAddress).setFormulaLocal("=RAND()");
    }

rand loop office ...

Was this reply helpful? Yes No
The_hooligan22 22 on at

Like (1)

Report

@leemager Thank you so much. It seems to be working now!

Was this reply helpful? Yes No