How to Automate Excel with Office.js

You can automate Excel with Office.js to handle the repetitive data work that fills a day: populating templates, validating input, pulling numbers from an API, and pushing results back out. The Excel JavaScript API gives you direct access to ranges, tables, charts, and custom functions, all from code that runs inside the workbook and works the same on Windows, Mac, and the web. This guide covers the patterns that hold up in production — batching with the request context, reacting to user edits with events, moving data between Excel and a service, and the performance traps that catch new builds.

The core pattern: Excel.run and context.sync

Every Office.js operation in Excel runs inside a request context. You queue the changes you want, then call context.sync() once to send them to Excel as a single round trip. Batching this way is the single biggest factor in how fast an add-in feels.

await Excel.run(async (context) => {
  const sheet = context.workbook.worksheets.getActiveWorksheet();
  const range = sheet.getRange("A1:B2");
  range.values = [
    ["Product", "Units"],
    ["Widget", 1200],
  ];
  await context.sync();
});

To read data you have to load the properties you want first, then sync before you can use them. The proxy objects you get back are empty until that sync resolves:

await Excel.run(async (context) => {
  const used = context.workbook.worksheets.getActiveWorksheet().getUsedRange();
  used.load("values, rowCount");
  await context.sync();
  console.log(`Read ${used.rowCount} rows`, used.values);
});

Reading and writing ranges, tables, and charts

Tables are usually the right structure for automation because they grow with your data and keep formatting and formulas consistent. You can add rows, read a column, or rebuild a table from a fresh dataset without touching individual cells:

await Excel.run(async (context) => {
  const sheet = context.workbook.worksheets.getActiveWorksheet();
  const table = sheet.tables.add("A1:C1", true);
  table.getHeaderRowRange().values = [["Date", "Region", "Revenue"]];
  table.rows.add(null, [
    ["2026-02-01", "EMEA", 48200],
    ["2026-02-01", "AMER", 61750],
  ]);
  await context.sync();
});

Charts attach to a range the same way, so a reporting add-in can drop in fresh figures and regenerate a chart in one pass.

Reacting to user edits with event handlers

Automation that responds to what the user does is what separates an add-in from a macro. The onChanged event on a worksheet fires when values change, which lets you validate input or recalculate the moment data lands:

await Excel.run(async (context) => {
  const sheet = context.workbook.worksheets.getActiveWorksheet();
  sheet.onChanged.add(handleChange);
  await context.sync();
});

async function handleChange(event) {
  await Excel.run(async (context) => {
    // event.address tells you what changed — validate or react to it
    console.log("Changed range:", event.address);
    await context.sync();
  });
}

Move data between Excel and a service

The high-value workflows usually cross a boundary: refresh figures from an internal API on open, validate them, then push a result to SharePoint or a database. The pattern is the same in both directions — fetch outside Excel, write inside the Excel.run context, and sync once.

await Excel.run(async (context) => {
  const data = await fetch("/api/sales/latest").then((r) => r.json());
  const sheet = context.workbook.worksheets.getActiveWorksheet();
  sheet.getRange("A2").getResizedRange(data.length - 1, 1).values =
    data.map((row) => [row.region, row.revenue]);
  await context.sync();
});

For builds that connect to live business systems, our custom Excel add-in development team wires this into CRMs, ERPs, and reporting backends. When the data lives in Microsoft 365 itself, the Microsoft Graph API is the cleaner route.

Custom functions for live calculations

Custom functions let users call your logic with =MYNS.GETRATE(...) straight in a cell, which is ideal for pulling a live rate, a lookup, or a streamed value. You declare them with a JSDoc tag and register a namespace in the manifest:

/**
 * Returns the latest exchange rate for a currency pair.
 * @customfunction
 * @param {string} pair Currency pair, e.g. "USD/EUR"
 * @returns {number} The current rate
 */
async function getRate(pair) {
  const res = await fetch(`/api/fx/${pair}`);
  return (await res.json()).rate;
}

What slows an Excel add-in down the most?

Calling context.sync() inside a loop. Each sync is a round trip to Excel, so syncing per row turns a fast operation into a slow one. Queue every change, then sync once after the loop. Loading only the properties you actually use, instead of the whole object, helps too.

Frequently asked questions

Can an Office.js add-in replace Excel VBA macros?

For most workflows, yes — and it runs cross-platform, which VBA cannot. Office.js covers ranges, tables, charts, events, and custom functions. The migration is usually rebuilding the logic against the modern API rather than a line-for-line port.

Does an automated Excel add-in work in Excel on the web?

Yes. The same Office.js code runs in Excel on Windows, Mac, and the browser. A few host-specific APIs vary, so test across the platforms you support, but the core automation behaves the same everywhere.

How do I run code automatically when a workbook opens?

Register your initialization inside Office.onReady(), and attach the worksheet or workbook events you need there. That gives you a reliable hook to refresh data or set up handlers as soon as the add-in loads.

Need this built for your team?

We build Excel add-ins development for data and operations teams like yours. Get a free 24-hour scoping call.

If you have an Excel workflow that should run itself, a discovery call is the right place to scope what to automate first.