Analistable

How to combine workbooks with Office Scripts

By the Analistable team · Updated · 2 min read

An Office Script can only work on the workbook it runs in, so combining files takes two scripts and a Power Automate flow. Script 1 returns the data rows of a source workbook. Script 2 appends an array of rows to a master sheet. The flow lists the files in a folder and runs script 1 on each, then script 2 on the master. It's far faster than adding rows one by one.

Part of our guide: How to merge Excel files: every method compared

Requirements

  • A Microsoft 365 business or education licence that includes Office Scripts (the Automate tab in Excel for the web).
  • Source and master workbooks stored in OneDrive for Business or SharePoint.
  • Each source workbook's data on its first sheet, starting at A1, with the same column order.

Script 1: read the rows

In Excel for the web, open Automate → New Script, paste this and save it as Get rows:

function main(workbook: ExcelScript.Workbook): string[][] {
  const used = workbook.getWorksheets()[0].getUsedRange();
  if (!used) return [];
  // Drop the header row; the master sheet already has one.
  return used.getTexts().slice(1);
}

getTexts() returns what you see in the cells, so dates come through as displayed. Use getValues() instead if you need raw numbers.

Script 2: append the rows

Save this as Append rows. It writes the whole array below the last used row of a sheet called Combined, in one call:

function main(workbook: ExcelScript.Workbook, rows: string[][]) {
  if (rows.length === 0) return;
  const sheet = workbook.getWorksheet("Combined");
  const used = sheet.getUsedRange();
  const startRow = used ? used.getRowCount() : 0;
  sheet.getRangeByIndexes(startRow, 0, rows.length, rows[0].length).setValues(rows);
}

The flow

  1. Create an Instant or Scheduled cloud flow in Power Automate.
  2. Add List files in folder for the source folder.
  3. Add Apply to each over the files.
  4. Inside it, add Run script (Excel Online (Business)). Choose the current file by its Id and the script Get rows.
  5. Still inside the loop, add another Run script on the master workbook with Append rows. For the rows parameter, use the result of the previous step.
  6. Save and test.

Keep the master workbook out of the source folder, or the flow will read it too.

Why not just use Power Query?

Power Query is easier when someone opens the report in desktop Excel. Office Scripts with Power Automate suit merges that must run unattended in the cloud. For context on every option, see combining Excel files automatically and the Power Automate walkthrough.

Frequently asked questions

Can an Office Script open another workbook?
No. A script runs in the workbook it's started from. Use Power Automate to run scripts on several workbooks and pass data between them.
Do Office Scripts work in desktop Excel?
Office Scripts are available in Excel for the web and, for eligible Microsoft 365 business accounts, in recent Windows and Mac versions. Power Automate runs them in the cloud.
Is there a row limit?
Power Automate limits the size of data passed between actions. For very large files, process them in chunks or use Power Query instead.

Related guides