Skip to main content

How to Pass a List of Records to an Office Script from Power Automate

· 7 min read
Jagdish Kumawat
Founder @ Dewiride

To put many rows into a workbook from a flow, don't run a script once per row: pass the whole list to one script call. The script declares what a record looks like and takes an array of them, and the Run script action takes the flow's array in one piece once you select Switch to input entire array.

Syntax​

General form
interface <Record> {
<field>: <type>;
<field>: <type>;
}

function main(workbook: ExcelScript.Workbook, <parameter>: <Record>[]) {
// Use <parameter> here: a list of <Record>s.
}

From Microsoft's Get user input for scripts:

  • interface <Record>: the shape of one record, a name for each field and its type. Objects must have their interface or class definition defined in the script.
  • <field>: <type>: each field is a string, number or boolean, an array, or another object made of those.
  • <Record>[]: an array of records. Arrays (both [] and Array<T> styles) of the previously listed types are supported.

The flow's records must use the same field names, because the script reads each value by its name.

Prerequisites​

The Example​

Office Scripts Example.xlsx, a workbook in my OneDrive, has a table called Fruits with the columns Fruit and Price, and three rows: Apple 1.2, Banana 0.5 and Cherry 3.5. The flow sends two more fruits, Kiwi at 0.8 and Mango at 1.5, and a script called Add Fruits adds them to the table.

Step 1: Write the Script​

Create Add Fruits in the workbook (How to Create and Run an Office Script in Excel):

Add Fruits
interface Fruit {
name: string;
price: number;
}

/**
* Adds one row per fruit to the end of the Fruits table.
* @param fruits The fruits to add, each with a name and a price.
*/
function main(workbook: ExcelScript.Workbook, fruits: Fruit[]) {
const rows = fruits.map((fruit) => [fruit.name, fruit.price]);
workbook.getTable("Fruits").addRows(-1, rows);
}
  • interface Fruit says a fruit has a name, text, and a price, a number.
  • fruits: Fruit[] is the input: a list of fruits.
  • fruits.map(...) turns each fruit into a row, [name, price], in the table's column order.
  • addRows(-1, rows) adds all the rows to the end of the table in one go (How to Add Rows to an Excel Table with an Office Script).

Step 2: Make the List in the Flow​

In a flow with the trigger Manually trigger a flow, add a Compose action called Fruits (How to Use the Compose Action in Power Automate), with this in Inputs:

Fruits → Inputs
[{"name": "Kiwi", "price": 0.8}, {"name": "Mango", "price": 1.5}]

The Compose action named Fruits with the JSON array of Kiwi 0.8 and Mango 1.5 in Inputs

In a real flow, the list usually comes from earlier steps, for example a Select that keeps the fields the script needs from each item (How to Keep Several Fields from Each Item with Select in Power Automate).

Step 3: Add Run script and Switch to the Entire Array​

Add Run script from Excel Online (Business) after Fruits and choose the workbook and Add Fruits (How to Run an Office Script from Power Automate). The field for the parameter, ScriptParameters/fruits, offers Add new item, which builds the list one fruit at a time. Select the button at its top right, Switch to input entire array:

The Run script action with Script Add Fruits and the ScriptParameters/fruits field showing Add new item, with the Switch to input entire array button outlined in red at its top right

The field becomes one box. Put the cursor in it and pick Outputs of Fruits from dynamic content:

The ScriptParameters/fruits field as a single box holding the Outputs token from Fruits

Microsoft's Troubleshoot Office Scripts running in Power Automate explains why: The default is to pass single elements, which builds the array in the flow. For scripts or other connectors that take entire arrays as arguments, you need to select the Switch to input entire array button.

Step 4: Run the Flow​

Save the flow and test it (How to Test a Cloud Flow Manually in Power Automate). Run script took 5 seconds for both fruits:

The run of Pass Fruits to a Script with the message Your flow ran successfully., and green ticks on Manually trigger a flow, Fruits and Run script, 5s

The Result​

The Fruits table now ends with Kiwi 0.8 and Mango 1.5:

The Fruits table with five rows: Apple 1.2, Banana 0.5, Cherry 3.5, Kiwi 0.8 and Mango 1.5

Common Mistakes​

MistakeWhat happensDo this instead
Leaving the field on Add new itemThe flow builds the list item by item instead of taking yoursSwitch to input entire array
Field names in the flow that differ from the interface, such as Name for nameThe script finds no value under the names it readsUse the interface's names exactly
Running the script once per record in a loopMany script calls, each counted against the daily limitOne call with the whole list

Troubleshooting​

SymptomLikely causeFix
The parameter field doesn't appearThe interface isn't in the script, or a field has a type scripts can't takeDefine the interface in the script with supported types
Prices arrive as textThe flow's values are strings, such as "0.8"Send numbers without quotes, or convert them in the script
The field still shows the old parameterThe action was added before the parameter changedDelete the Run script action and add it again

FAQ​

How many records can I pass?​

Microsoft's Platform limits puts the maximum size of parameters passed to the Run script action at 30,000,000 bytes (28.6MB), and a script call times out after 120 seconds.

Can a record contain another object?​

Yes. Microsoft's article shows an Employee with a job field that is itself a Position object, as long as every type in it is supported.

Can I define the record type inline instead of with an interface?​

Yes: contact: {name: string, email: string} is allowed, as in Microsoft's article. An interface is easier to read once the record has several fields.

Conclusion​

Declare the record with an interface, take an array of it in main, and in Run script select Switch to input entire array and pass the flow's list. One script call adds every record.

Additional Resources​