How to combine workbooks with Office Scripts
By the Analistable team · Updated · 5 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
- Create an Instant or Scheduled cloud flow in Power Automate.
- Add List files in folder for the source folder.
- Add Apply to each over the files.
- Inside it, add Run script (Excel Online (Business)). Choose the current file by its Id and the script
Get rows. - Still inside the loop, add another Run script on the master workbook with
Append rows. For therowsparameter, use the result of the previous step. - Save and test.
Keep the master workbook out of the source folder, or the flow will read it too.
Adding the file name to each row
To record which workbook each row came from, give Get rows a second parameter and prepend it to every row. Add a Source header as the first column of the Combined sheet:
function main(workbook: ExcelScript.Workbook, fileName: string): string[][] {
const used = workbook.getWorksheets()[0].getUsedRange();
if (!used) return [];
return used
.getTexts()
.slice(1)
.filter((row) => row.some((cell) => cell !== "")) // skip blank rows
.map((row) => [fileName, ...row]);
}In the flow, the Run script action now shows a fileName box; fill it with the Name of the current file from List files in folder. The filter also drops rows that are completely empty, which otherwise appear when someone has formatted cells below the data.
Matching columns by header instead of position
If the source files don't all have the same column order, read them by header name. This version returns a fixed list of columns in a fixed order and leaves a blank where a file lacks one:
function main(workbook: ExcelScript.Workbook): string[][] {
const wanted = ["Order ID", "Date", "Customer", "Amount"];
const used = workbook.getWorksheets()[0].getUsedRange();
if (!used) return [];
const texts = used.getTexts();
const header = texts[0].map((h) => h.trim());
const positions = wanted.map((name) => header.indexOf(name));
return texts
.slice(1)
.map((row) => positions.map((i) => (i >= 0 ? row[i] : "")));
}Every returned row now has exactly four values, so Append rows always writes a rectangle that fits the master's four columns. Header names must match exactly after trimming, including capitalisation.
Clearing the master before each run
A scheduled flow that only appends will duplicate everything on its second run. Save this third script as Reset combined and run it on the master workbook before the loop. It clears everything below the header row:
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook.getWorksheet("Combined");
const used = sheet.getUsedRange();
if (!used || used.getRowCount() < 2) return;
used
.getOffsetRange(1, 0)
.getResizedRange(-1, 0)
.clear(ExcelScript.ClearApplyTo.contents);
}getOffsetRange(1, 0) shifts the used range down one row and getResizedRange(-1, 0) makes it one row shorter, so the result covers exactly the data rows under the header.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Error about the input array not matching the range size | Rows of different lengths, or a first row shorter than the rest | Use the header-mapping script so every row has the same width |
| Cannot read properties of undefined | No sheet called Combined in the master | Rename the sheet or change the name in the script |
| Script doesn't appear in the Run script list | Saved under another account or location | Open it in Excel for the web under Automate and save it to your own scripts |
| Run script times out on big files | Too much data in one call | Split the file, or read and write in batches of rows |
| Numbers arrive as text in the master | getTexts returns strings | Use getValues for numeric columns, or convert after loading |
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.
- How do I stop the flow adding the same rows every time it runs?
- Run a reset script on the master workbook at the start of the flow to clear the data rows below the header, or write each run into a fresh copy of a template workbook.