#PowerPlatformTip 157: 'Run Office Scripts from Power Automate – 20 Everyday Excel Automations'

TL;DR: Record or write an Office Script in Excel, then call it from Power Automate with the Excel Online (Business) connector’s Run script action. One flow step does the formatting, filtering, bulk updates or calculations that would otherwise need long chains of connector actions and Apply to each loops.

As a citizen developer you keep hitting the same wall: Power Automate’s built-in Excel actions (List rows, Add a row, Update a row) are fine for one row at a time, but the moment you need to format a table, remove duplicates, rebuild a sheet, or update 500 cells, you end up building fragile workarounds with loops, Compose steps and Condition blocks. Office Scripts move that logic into Excel itself – you write it once, and Power Automate just triggers it.

💡 Challenge

Native Excel connector actions are row-by-row. Anything structural – formatting, sorting, de-duplicating, bulk writes, PivotTables, multi-sheet handling, calculations – forces you into slow Apply to each loops that are limited to 50 parallel iterations, run for minutes, and break as soon as the table layout changes. Citizen developers rebuild the same workarounds in flow after flow instead of reusing one clean solution.

✅ Solution

Put the Excel logic in an Office Script stored in your OneDrive (or a SharePoint library) and call it from the Excel Online (Business) connector. A script can accept parameters from the flow and return values back to it, so the flow stays short and the heavy lifting happens inside Excel – even when the workbook is closed.

• Run script → for scripts saved in the default OneDrive location (the Office Scripts folder of your OneDrive).
• Run script from SharePoint library → for script files (.osts) stored in a SharePoint library, for example on a team site.

The workbook itself must be a file in OneDrive for Business, a SharePoint library or a Teams/Group library. A file on your local disk cannot be picked.

License note: Office Scripts in Power Automate require a business license of Microsoft 365.

🔧 How It’s Done

1. Write (or record) the script in Excel. In Excel on the web → Automate tab → New Script (or record your clicks). Add parameters to the main function so the flow can pass data in and read results out:

function main(
  workbook: ExcelScript.Workbook,
  newRow: string[]        // value passed from Power Automate
): { rows: number } {     // value returned to Power Automate
  const sheet = workbook.getWorksheet("Data");
  const table = sheet.getTables()[0];

  // 1) Append a new row (bulk-safe, no Apply to each)
  table.addRow(-1, newRow);

  // 2) Format the header + autofit in one pass
  const header = table.getHeaderRowRange();
  header.getFormat().getFont().setBold(true);
  table.getRange().getFormat().autofitColumns();

  // 3) Return a value the flow can use later
  return { rows: table.getRowCount() };
}

2. Save the script. It lands in your OneDrive under /Documents/Office Scripts/ by default.

3. Add the connector action in your flow. In the flow builder → Add an action → search “Excel run script” → Excel Online (Business) → Run script. Set:

🔸 Location: OneDrive for Business

🔸 Document Library: OneDrive

🔸 File: your workbook (.xlsx) – pick it with the file browser

🔸 Script: your saved script

4. Map parameters and outputs. Every parameter after workbook in main appears as an input field in the action (the action’s ScriptParameters object). Whatever you return becomes dynamic content for later steps, for example rows from the script above.

Part	How it works
workbook: ExcelScript.Workbook	Always the first parameter. Power Automate fills it with the workbook you picked. Do not map it.
Further parameters	Typed parameters such as string, number, boolean, arrays and objects. Each one becomes an input in the action.
Return value	Typed return, for example { rows: number }. The properties show up as dynamic content after the action.
Excel objects	Objects like Range or Table are not valid parameters or return values. Return the values (range.getValues()) instead.

5. Test it. Use the Test button and allow access when prompted. Once the run is green, the properties your script returned are available as dynamic content in every later action.

6. Need a different script location or file? Use Run script from SharePoint library when the .osts file lives in a SharePoint library. It has two extra fields (script source and script library) next to the file location of the workbook, so script and workbook can sit in different libraries.

📋 Everyday Office Script Use Cases

Each case below is a single Run script step instead of a loop. The right column names the Office Scripts API you start with.

#	Case	What the script does	Key API
1	Reset a data range	Wipes the data rows of a table and keeps headers and formats	table.getRangeBetweenHeaderAndTotal().clear(ExcelScript.ClearApplyTo.contents)
2	Remove duplicates	Deletes rows with repeated key values in one pass	range.removeDuplicates()
3	Convert a range to a table	Gives a loose range a name and headers so connector actions can address it	worksheet.addTable()
4	Trim and clean text	Reads the values, trims and normalises them in code and writes them back	range.getValues() / range.setValues()
5	Append rows	Adds one or many rows to a named table in one call	table.addRow() / table.addRows()
6	Bulk write	Writes a whole 2D array from the flow into a range in one operation	range.setValues()
7	Fill formulas	Sets formulas such as SUM or XLOOKUP on a whole column	range.setFormula() / range.setFormulas()
8	Sort a table	Sorts by one or several columns	table.getSort().apply()
9	Filter a table	Applies an AutoFilter to the table	table.getAutoFilter().apply()
10	Return a subset	Filters rows in code by your parameters and returns them as JSON	range.getValues() plus filter()
11	Look up one value	Finds a value, for example a price by SKU, and returns only that value	range.getValues()
12	Auto-format a table	Bold header, table style, autofit columns	getFormat().getFont().setBold(), table.setPredefinedTableStyle(), autofitColumns()
13	Number and date formats	Applies number, date, currency or percent formats to columns	range.setNumberFormat()
14	Conditional formatting	Highlights overdue dates, negatives or values over a threshold	range.addConditionalFormat()
15	Freeze panes and print area	Freezes the header row, sets the print area	worksheet.getFreezePanes().freezeRows(), worksheet.getPageLayout().setPrintArea()
16	Protect a sheet	Protects the sheet or locks selected cells before the file goes out	worksheet.getProtection().protect(), range.getFormat().getProtection().setLocked()
17	New worksheet per period	Copies a template tab and renames it, for example per month	worksheet.copy(), worksheet.setName()
18	Add a worksheet	Creates an empty sheet for results or a summary	workbook.addWorksheet()
19	PivotTable	Builds a PivotTable from a table	workbook.addPivotTable()
20	KPIs as script output	Sums, counts and averages in code and returns them to the flow	range.getValues() plus reduce()

Cases that need a row-by-row decision (upsert, cross-referencing two sheets, splitting one sheet into several) are plain TypeScript logic on top of getValues(), setValues() and addRow(). The API names in the table are the starting points.

🎉 Result

Your flows shrink from dozens of Excel actions and nested loops to one Run script step. The logic is reusable across flows, survives layout changes, needs no Apply to each loop, and runs on the workbook in the cloud without you opening Excel – exactly the kind of building block a citizen developer wants instead of rebuilding workarounds every time.

🌟 Key Advantages

🔸 One step, not a loop: structural Excel work (format, sort, de-dupe, bulk write) runs inside Excel in a single action.

🔸 Reusable: write the script once, call it from any flow or on a schedule.

🔸 Parameters in, values out: pass flow data into the script and return results as dynamic content.

🔸 Standard, no-code-friendly: record scripts by clicking in Excel – no premium connector required.

🛠️ FAQ

1. Do I need to be a developer to use Office Scripts?

No. Use the Automate tab in Excel on the web to record your actions into a script, then tweak it. You call it from Power Automate with the standard Run script action.

2. What license do I need?

A business license of Microsoft 365.

3. My script’s parameters or outputs don’t show up in the flow – why?

The script’s signature is read when you add the action. If you changed the main parameters or return type afterwards, remove and re-add the Run script action to refresh it. Return plain values (strings, numbers, booleans, arrays, objects), not Excel objects such as a Range.

4. OneDrive or SharePoint?

Use Run script for scripts in your OneDrive, and Run script from SharePoint library for scripts shared on a team site. The workbook itself can live in either.

5. Are there security considerations?

Yes. A script can read and change everything in the workbook it runs on, so only run scripts you wrote or reviewed. Admins can restrict Office Scripts in the Microsoft 365 admin controls.

🔗 Related Tips

• #PowerPlatformTip 150: JSON & HTML to Markdown with Office Scripts, a complete Office Script for Markdown output.
• #PowerPlatformTip 117: Optimize Parallel Object Processing, for handling the Apply to each parallelism limit another way.
• #PowerPlatformTip 120: PowerAutomate, Text Processing 2.0, advanced text handling inside flows.
• #PowerPlatformTip 100: Bypassing Complexity in PowerAutomate, simplifying flows instead of building workarounds.