#PowerPlatformTip 157: ‘Run Office Scripts from Power Automate – 25 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 hit the 50-item concurrency cap, 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.
• Run script from SharePoint library → for scripts stored in a team’s SharePoint site.

License note: Office Scripts in Power Automate require a business license of Microsoft 365. Office 365 E1 and F3 can run scripts via Power Automate but don’t get the in-Excel Power Automate integration.

🔧 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. Any main parameter shows up as an input field; anything you return becomes dynamic content for later steps (e.g. rows).

5. Test it. Use the Test button and allow access when prompted. A 202/200 with the returned object means the script ran on the workbook.

📋 25 Everyday Office Script Use Cases (so you stop building workarounds)

🗂️ Table & data hygiene

1. Reset a data range – wipe all data rows while keeping headers, formats and formulas intact. Perfect for a “clear and refill” flow that repopulates a report every morning without you rebuilding the layout by hand.
2. Remove duplicate rows based on one or more key columns in a single pass. Great after merging exports from several systems, where a native flow would need a slow loop and a lookup table to spot repeats.
3. Delete empty rows and columns left behind by a system export or a copy-paste. The script tidies the whole sheet at once so later actions like List rows don’t trip over blank records.
4. Convert a loose range into a real Excel Table with a proper name and headers. Many connector actions (Add a row, List rows) only work against tables, so this turns a raw dump into something Power Automate can actually address.
5. Trim & clean text in bulk – strip leading/trailing spaces, remove line breaks and normalise UPPER/lower casing. This fixes the messy free-text that breaks lookups and grouping, without a Compose-and-loop workaround for every column.

➕ Writing & updating (kills the Apply to each loops)

1. Append a row (or many rows) to a named table in one call. It replaces a fragile Apply to each + Add a row pattern and stays fast even when you’re writing hundreds of records.
2. Bulk-write hundreds of cells at once by handing the script a whole array from the flow. Instead of dozens of Update a row actions that hit throttling limits, one script writes the entire block in a single operation.
3. Upsert – find a row by a key value and update it, or add it if it doesn’t exist yet. This is the classic “sync my list into Excel” job that normally needs a loop, a condition and two branches, all collapsed into one step.
4. Copy data from one sheet to another (e.g. staging → master) with the columns you choose. The script handles the mapping internally, so you avoid reading every row into the flow and writing it back one by one.
5. Fill down formulas or calculated columns such as XLOOKUP, SUMIFS or a totals row after new data arrives. Power Automate can’t write live formulas cleanly, but a script sets them on the whole column and lets Excel recalculate.

🔍 Sorting, filtering & lookups

1. Sort a table by one or several columns in the order you need. This keeps “newest first” reports or ranked lists correct without pulling all rows into the flow just to reorder them.
2. Apply an AutoFilter and return only the matching rows as JSON to the flow. You get exactly the subset you care about back as dynamic content, ready to email or post to Teams.
3. Return a filtered subset to the flow based on parameters you pass in (date range, status, region). It replaces the common List rows + Condition inside Apply to each pattern that runs slowly and eats API calls.
4. Cross-reference two sheets or workbooks and flag mismatches or missing entries. The script does the comparison in memory and hands back a clean list of discrepancies for a validation or reconciliation flow.
5. Look up a single value – for example a price by SKU or a manager by employee ID – and return it to the flow. It’s a lightweight alternative to loading an entire table just to read one cell.

🎨 Formatting & presentation

1. Auto-format a table with a bold header, borders, banded rows and autofit columns in one shot. This makes an automated export look report-ready without any manual polishing after each run.
2. Apply number, date, currency or percent formats consistently across columns. It guarantees that amounts and dates look right in every generated file, which the plain Excel connector simply can’t control.
3. Add conditional formatting to highlight overdue dates, negative values or cells over a threshold. Reviewers instantly see what needs attention, and the rules travel with the workbook instead of living in your flow logic.
4. Freeze panes, set the print area and configure page setup so the file is ready to print or share. These layout tweaks are impossible from the standard connector but trivial in a script.
5. Protect or unprotect a sheet, or lock specific cells before the file goes out. This safeguards formulas and headers so recipients can only edit the areas you intend.

📊 Structure, sheets & reporting

1. Create a new monthly (or per-project) worksheet from a template tab and rename it automatically. A scheduled flow can spin up “July”, “August” and so on, so you never manually prepare the next reporting sheet again.
2. Split one sheet into multiple sheets by category, region or owner. The script partitions the data internally, replacing a tangle of filters and copy actions you’d otherwise build in the flow.
3. Merge multiple sheets into one summary table for consolidated reporting. It gathers rows from every tab and stacks them cleanly, ready for a Pivot or a dashboard.
4. Build or refresh a PivotTable and read its results back into the flow. You get aggregated numbers without recreating the pivot logic in expressions, and the summary is available for the next action.
5. Return KPIs and aggregates (sum, count, max, average) directly as script output. Feed those values straight into an approval, an adaptive card, an email or a Teams message – no extra calculation steps in the flow.

🎉 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, runs faster (no 50-item cap), and even works when the workbook is closed – 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

Q1: 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.

Q2: What license do I need? A business license of Microsoft 365. Office 365 E1 and F3 can run scripts through Power Automate but lack the in-Excel integration.

Q3: My script’s parameters or outputs don’t show up in the flow – why? The script’s signature is stored on the connector when you add it. If you changed the main parameters or return type afterwards, remove and re-add the Run script action to refresh it. Also make sure your parameter/return types are supported.

Q4: 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.

Q5: Are there security considerations? Yes – the Run script action gives broad access to the workbook, and scripts making external API calls carry extra risk. Admins can restrict Office Scripts via the Microsoft 365 admin controls if needed.

🔗 Related Tips

• #PowerPlatformTip 117: Optimize Parallel Object Processing, for beating the 50-item Apply to each cap 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.