Office Scripts for Excel on the Web and Power Automate

People who automate Excel with VBA find one rule hard to live with: it only runs in desktop Excel, and only when someone has the file open. Microsoft's answer for the cloud is Office Scripts, a way to automate workbooks in Excel on the web with TypeScript, callable from Power Automate. This post shows what an Office Script is, how to record and edit one, how to run it on a schedule, and where it stops. It is a spoke of automating Excel work without clicking.

What an Office Script is

An Office Script is a TypeScript function stored as a file in your OneDrive and run against a workbook in Excel on the web. It has no macro security problem: there is no code inside the workbook, and the script runs in a sandbox that Microsoft hosts. It uses an object model called ExcelScript, which resembles the VBA object model in outline: workbooks, worksheets, ranges, tables, charts, pivot tables, named items.

The entry point is a function named main that receives the workbook as an argument:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const range = sheet.getRange("A1:C1");
  range.getFormat().getFont().setBold(true);
}

A script can read and write cells, create and format tables, add charts, sort, filter and so on. It runs in the cloud, so it can run while your laptop is closed. It cannot read files from your disk, call web services directly or use external libraries, because the sandbox allows none of that.

Requirements and where it is available

Office Scripts need a work or school Microsoft 365 plan that includes Excel on the web and the feature enabled by the tenant administrator. They are not available for personal accounts or the desktop Excel for local files in the same way. The workbook must be stored in OneDrive for Business or SharePoint. Check the Automate tab in Excel on the web: if you see it, you probably have access. If not, ask the administrator, since the tenant can turn the feature off.

Because licensing and policy differ, confirm access before you design around it. A workflow that depends on a feature your organization has switched off is a dead end.

Record a script

The Action Recorder turns clicks into code. In Excel on the web choose Automate, then Record Actions. Do the task once, for example format the header row, add a table and sort it. Stop the recording and save it. The recorder produces a main function with one line per action.

Recorded code is a good draft and a poor final version. It often contains hard-coded ranges ("A1:D200") that break when the data grows, and sheet names typed in. Edit it to work with what is actually there. Instead of a fixed range, use the used range:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Data");
  const used = sheet.getUsedRange();
  const table = sheet.addTable(used, true);
  table.setName("SalesData");
  table.getSort().apply([{ key: 2, ascending: false }]);
}

getUsedRange() returns the range that contains data. The true says the first row is a header. Use names for sheets and tables, not positions, so a reordered workbook still works.

Read and write data efficiently

Like VBA, a script is slow when it touches cells one at a time. Read a block of values, process it in memory and write it back once:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Data");
  const range = sheet.getUsedRange();
  const values = range.getValues();

  const header = values[0];
  const rows = values.slice(1).filter(r => r[0] !== "");

  const out = workbook.getWorksheet("Clean") ?? workbook.addWorksheet("Clean");
  out.getUsedRange()?.clear();
  out.getRangeByIndexes(0, 0, rows.length + 1, header.length).setValues([header, ...rows]);
}

This filters rows with an empty first cell and writes the result to a Clean sheet in one call. getValues() returns a two-dimensional array, which is plain TypeScript to work with. For large ranges there are size limits per call, so very big sheets need chunking; Microsoft's documentation lists the current limits.

Run it from Power Automate

A script does its best work when something else starts it. Power Automate has actions for Excel Online (Business): Run script and Run script from SharePoint library. A flow might be:

1. Trigger: a recurrence every weekday at 7:00, or a new file in a SharePoint folder.

2. Action: Run script, choosing the workbook and the script, and passing parameters.

3. Action: use the returned value, for example send an email with the result.

Scripts can take parameters declared in main after the workbook, and return a value, which Power Automate then uses in later steps:

function main(workbook: ExcelScript.Workbook, region: string): number {
  const table = workbook.getTable("SalesData");
  const rows = table.getRangeBetweenHeaderAndTotal().getValues();
  return rows.filter(r => r[1] === region).length;
}

The flow passes a region and receives a row count. With this pattern a flow can drive many workbooks with one script, loop over a list of regions, and gather results. Runs are subject to time limits and daily call quotas that depend on your plan, so check them for high-volume jobs.

What it cannot do

Know the limits before you start.

• No local files and no network access from inside the script. Data must be in the workbook, or passed in as a parameter by the flow.

• No refreshing of Power Query in the same way as desktop. Support for refresh and for some features differs from desktop Excel; check the current documentation for your data sources before relying on it (refreshing Power Query data on a schedule).

• Feature gaps. Not every desktop feature has an ExcelScript method; pivot tables and charts are supported to different degrees.

• Size and time limits. Large workbooks and long computations run into timeouts.

• Cloud only. The workbook has to be in OneDrive or SharePoint, which suits shared team files and excludes local ones.

• TypeScript only, and a smaller community than VBA or Python, so examples are fewer.

Compared with Python, the main difference is the sandbox. A Python script can read a database, call an API and use pandas. An Office Script works with what is inside the workbook, and Power Automate supplies the rest. That makes it a good fit for tidy-ups of shared team workbooks and a poor fit for data pipelines.

Debugging and sharing

The Code Editor shows the script, a run button and an output panel for console.log messages. When something fails, the run history shows the error and the line. Scripts can be shared with colleagues, who run them on their own workbooks, and organizations can keep shared scripts in a SharePoint library so that a flow can call them by name and they have an owner and a history. Treat them like code: put a header comment with purpose, inputs and the date, and keep a copy in a repository if the job matters, since the editor is not a version control system.

A realistic example

A sales team receives a workbook every morning from a system that pastes data into a sheet called Raw. A script formats the header, converts text numbers in column D to numbers, removes blank rows, sorts by date and copies a summary to a sheet called Summary. A Power Automate flow runs the script when the file changes and posts the summary to a Teams channel. No one opens the file, no desktop Excel is involved, and nothing needs installing. The same job with VBA would require a person to open the file and enable macros.

If the file instead needs data from a database every morning, the flow might fetch the rows with a database connector and write them into the sheet before the script runs. The pieces are Power Automate's; the script handles the workbook.

Checks

Verify the output by reading it back. A flow can run a second script that returns the row count of the target table, and compare it with the count the first script reported. Include a run date in the workbook so a stale file is visible. And watch the flow history: a failed run emails the owner by default, which is only useful if that person still works there. Assign a team mailbox as the owner when possible.

When Office Scripts do not fit

The workbook is local or on a network share. Scripts run only on cloud-stored workbooks.

You need a database or an API inside the script. Use Power Automate connectors around the script, or choose Python.

Your organization has not enabled the feature. An administrator can switch it off, and that settles it.

The data is very large. Past a few hundred thousand rows, move the work to a database or a script that runs outside Excel (analyzing more than a million rows without Excel).

See Microsoft's documentation on Office Scripts in Excel and its Power Automate integration. For a comparison with the other options, read VBA versus Python for Excel automation. To convert a small result to JSON, use the CSV to JSON converter.