Analistable

How to merge sheets with Google Apps Script

By the Analistable team · Updated · 2 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

  1. In the Apps Script editor, open Triggers (the clock icon).
  2. Click Add Trigger.
  3. Choose the function mergeTabs, event source Time-driven, and an interval such as every hour or every day.
  4. 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

Combined tab written by mergeTabs()
SourceDateProductUnits
Jan2026-01-04Desk12
Jan2026-01-09Chair30
Feb2026-02-02Lamp14
Mar2026-03-11Desk11

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.

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.

Related guides