Answered

Excel Script works in Excel but Fails in Power Automate Runtime error: Line 11:

(0) Share

Report

Posted on by TravisCB1

Not Great at this stuff. I simply recorded me moving cell data up two rows to make room for the new data. It runs great in excel, but when I attached the script in PA, I get the following error.

Power Automate Error
"We were unable to run the script. Please try again.
Runtime error: Line 11: Cannot read properties of undefined (reading 'getRange')
clientRequestId: c3eec077-0165-4842-99c1-23a911ee5121"

Script in Excel

function main(workbook: ExcelScript.Workbook) {

// Refresh all data connections

workbook.refreshAllDataConnections();

let selectedSheet = workbook.getActiveWorksheet();

// Paste to range E1 on selectedSheet from range A1 on selectedSheet

selectedSheet.getRange("E1").copyFrom(selectedSheet.getRange("A1"), ExcelScript.RangeCopyType.all, false, false);

// Paste to range A1 on selectedSheet from range F1 on selectedSheet

selectedSheet.getRange("A1").copyFrom(selectedSheet.getRange("F1"), ExcelScript.RangeCopyType.values, false, false);

let source_Calculations = workbook.getWorksheet("Source Calculations");

// Clear ExcelScript.ClearApplyTo.contents from range B4:E5 on source_Calculations

source_Calculations.getRange("B4:E5").clear(ExcelScript.ClearApplyTo.contents);

// Paste to range B4 on source_Calculations from range B6:E7 on source_Calculations

source_Calculations.getRange("B4").copyFrom(source_Calculations.getRange("B6:E7"), ExcelScript.RangeCopyType.all, false, false);

// Paste to range B6 on source_Calculations from range B8:E11 on source_Calculations

source_Calculations.getRange("B6").copyFrom(source_Calculations.getRange("B8:E11"), ExcelScript.RangeCopyType.all, false, false);

// Paste to range B10 on source_Calculations from range B13:E14 on source_Calculations

source_Calculations.getRange("B10").copyFrom(source_Calculations.getRange("B13:E14"), ExcelScript.RangeCopyType.all, false, false);

}

I don't see any issue with line 11.

// Clear ExcelScript.ClearApplyTo.contents from range B4:E5 on source_Calculations

source_Calculations.getRange("B4:E5").clear(ExcelScript.ClearApplyTo.contents);

Categories:

Using flows

All responses (3)

Answers (2)

Sort by

Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @TravisCB1 ,

You've mentioned this script works fine if you run it in Excel Online - can you double-check that the Excel file you've selected in Power Automate contains the selected sheet(Source Calculations), with an exact match to the name?

I think this link will help you a lot:

https://powerusers.microsoft.com/t5/Building-Flows/Office-Script-getRange-Error-Cannot-read-property-getRange-of/td-p/2019087

Best Regards,

Bof
Verified answer

MichelleRanMSFT Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @TravisCB1, thanks for your question! Two things to note:

First, refreshAllDataConnections only works for Power BI when a script is run through Power Automate. See this article for details: Troubleshoot Office Scripts running in Power Automate - Office Scripts | Microsoft Learn

Second, when running an Office Script in Power Automate, it's best to avoid calling getActiveWorksheet. Because a flow can run while Excel isn't open, it's not always clear what the active worksheet is. Instead, you should get a specific worksheet by name. So, you'd modify the second line of your script to this:
let selectedSheet = workbook.getWorksheet("Your worksheet name here");
Let me know if that helps or if you have any questions!
TravisCB1 63 on at

Like
a
(0)

Report
Copy link

Link copied!

I can't believe I missed that. All fixed. Thank you both!