How to merge sheets with Google Apps Script
By the Analistable team · Updated · 5 min read
Open Extensions → Apps Script, paste a function that reads each source tab with getDataRange().getValues(), collects the rows and writes them to a Combined tab with one setValues call, then run it. Add a time-driven trigger to merge automatically every hour or day. Unlike formulas, the result is plain values, so large merges stay fast.
Part of our guide: How to combine data in Google Sheets
Merge tabs in the same spreadsheet
function mergeTabs() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sources = ['Jan', 'Feb', 'Mar'];
let header = null;
const rows = [];
sources.forEach(name => {
const values = ss.getSheetByName(name).getDataRange().getValues();
if (values.length === 0) return;
if (!header) header = ['Source', ...values[0]];
values.slice(1)
.filter(r => r.join('') !== '') // skip empty rows
.forEach(r => rows.push([name, ...r]));
});
const out = ss.getSheetByName('Combined') || ss.insertSheet('Combined');
out.clearContents();
out.getRange(1, 1, 1, header.length).setValues([header]);
if (rows.length) out.getRange(2, 1, rows.length, header.length).setValues(rows);
}All source tabs need the same columns in the same order. setValues writes everything in one call, which is much faster than appending row by row.
Merge other spreadsheets
Replace the tab lookup with files opened by ID:
const files = ['1AbC…north', '1AbC…south']; // spreadsheet IDs
files.forEach(id => {
const sheet = SpreadsheetApp.openById(id).getSheetByName('Sales');
const values = sheet.getDataRange().getValues();
// …same header/rows logic as above, with the file name as Source:
// SpreadsheetApp.openById(id).getName()
});The first run asks you to authorise access to your spreadsheets.
Run it on a schedule
- In the Apps Script editor, open Triggers (the clock icon).
- Click Add Trigger.
- Choose the function
mergeTabs, event source Time-driven, and an interval such as every hour or every day. - Save.
Limits to know
- A single script execution can run for at most 6 minutes. Very large merges need to be split into batches.
- A spreadsheet holds up to 10 million cells; the combined tab counts towards that.
- Scripts run with the permissions of the person who set up the trigger.
For live results without a script, use formulas — see merging Google Sheets. For combining Google Sheets with Excel files, see the Sheets & Excel combiner.
Worked result
| Source | Date | Product | Units |
|---|---|---|---|
| Jan | 2026-01-04 | Desk | 12 |
| Jan | 2026-01-09 | Chair | 30 |
| Feb | 2026-02-02 | Lamp | 14 |
| Mar | 2026-03-11 | Desk | 11 |
Make it safer
- Check that each source sheet exists:
const sh = ss.getSheetByName(name); if (!sh) return;avoids an error when a tab is renamed. - Compare headers: skip or log a tab whose first row differs from the first tab's, instead of writing misaligned rows.
- Write a timestamp to a cell (
out.getRange('Z1').setValue(new Date())) so readers know when the merge last ran. - Use Executions in the Apps Script editor to see failed runs of the trigger.
Step by step: first run
- Open the spreadsheet that holds the Jan, Feb and Mar tabs.
- Choose Extensions → Apps Script. A new project opens in a browser tab.
- Delete the empty
myFunction, paste themergeTabscode and click Save. - Select
mergeTabsin the function dropdown and click Run. - Approve the authorisation prompt. If Google shows an “unverified app” warning for your own script, choose to continue to the project.
- Return to the spreadsheet: the Combined tab now holds every row with a Source column.
Merge every tab automatically
Instead of listing tab names, merge every tab except the output and any you exclude, so new monthly tabs are picked up without editing the code:
const skip = ['Combined', 'Settings'];
const sources = ss.getSheets()
.map(sh => sh.getName())
.filter(name => !skip.includes(name));Replace the sources line in mergeTabs with this. Tabs are processed in the order they appear in the tab bar.
Merge every file in a Drive folder
function mergeFolder() {
const folder = DriveApp.getFolderById('FOLDER_ID');
const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
let header = null;
const rows = [];
while (files.hasNext()) {
const file = files.next();
const sheet = SpreadsheetApp.open(file).getSheets()[0];
const values = sheet.getDataRange().getValues();
if (values.length < 2) continue;
if (!header) header = ['Source', ...values[0]];
values.slice(1).forEach(r => rows.push([file.getName(), ...r]));
}
const out = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Combined');
out.clearContents();
if (!header) return;
out.getRange(1, 1, 1, header.length).setValues([header]);
if (rows.length) out.getRange(2, 1, rows.length, header.length).setValues(rows);
}The folder ID is the last part of the folder's URL. This reads the first tab of every Google Sheets file in the folder; Excel files in the same folder are skipped because of the type filter. A new regional file dropped into the folder is included on the next run.
Append only new rows
Rewriting the whole Combined tab is simplest, but if other tabs reference rows on it, or the merge is slow, append only rows that aren't there yet. Keep a unique ID in each row, read the IDs already on Combined into a Set, and push only rows whose ID isn't in the set, then write them below the last row with out.getRange(out.getLastRow() + 1, 1, newRows.length, width).setValues(newRows). Edits to rows that were already copied are not picked up this way, so a full rebuild once a week is still a good idea.
Troubleshooting
| Error or symptom | Cause | Fix |
|---|---|---|
| TypeError: Cannot read properties of null (reading 'getDataRange') | A tab name in the list doesn't exist | Check spelling or skip missing tabs |
| The number of columns in the data does not match the number of columns in the range | Tabs have different widths | Pad or trim rows to the header length |
| Exceeded maximum execution time | Too many files or rows for one run | Process files in batches across several runs |
| Dates appear as numbers or long text | Formatting not copied with values | Format the date column on Combined once |
Frequently asked questions
- Can Apps Script merge multiple Google Sheets into one?
- Yes. Open each file with SpreadsheetApp.openById, read its values, and write all rows to one sheet with setValues.
- How do I run an Apps Script automatically?
- Add a time-driven trigger in the Apps Script editor's Triggers panel.
- Is a script better than IMPORTRANGE?
- For large or many files, yes: it writes static values once, so the combined sheet doesn't slow down recalculating imports.
- Does the script copy formatting and formulas?
- No. getValues and setValues copy values only, which keeps the merge fast. Format the Combined tab once; the formatting stays when the script clears contents.
- Who can run the merge script?
- Anyone with edit access to the spreadsheet can run it from the Apps Script editor or a custom menu. The script can only read source files that the person running it, or the trigger owner, can open.