// Sheets · Apps Script

Combine multiple tabs into one sheet in Google Sheets.

Use Apps Script to merge every tab's data into one sheet, dropping duplicate headers automatically, sized to the exact row count — no manual copy-paste.

“I have data split across multiple tabs and I want to merge it all into one sheet without copying rows by hand every time the source tabs change.”

The script

copy · paste · trigger

combineTabs.gs

Apps Script

// Merges every sheet except the target into one flat table.
// First row of Sheet 1 is kept as the header; all others are dropped.
function combineTabs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetName = 'Combined';
  var target = ss.getSheetByName(targetName) || ss.insertSheet(targetName);
  target.clearContents();

  var sheets = ss.getSheets();
  var merged = [];

  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    if (sheet.getName() === targetName) continue;
    var rows = sheet.getDataRange().getValues();
    if (merged.length === 0) {
      merged = rows; // keep header from first source tab
    } else {
      merged = merged.concat(rows.slice(1)); // drop header on subsequent tabs
    }
  }

  if (merged.length > 0) {
    target.getRange(1, 1, merged.length, merged[0].length).setValues(merged);
  }
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why slice(1) is the whole trick

Every source tab has a header row. If you concatenate raw getValues() arrays, you end up with a header embedded in the middle of your data every time a new tab starts. The fix is a single slice(1) call on every tab after the first, which returns the array starting at index 1, skipping row 0 entirely. The first tab keeps its header intact; everyone else donates only their data rows.

The first time I hit this, I spent ten minutes wondering why my VLOOKUP kept breaking halfway down the Combined sheet. It was a buried header acting as a value. slice(1) is the entire solution.

Sizing the write range to the merged array

A common mistake is writing with a fixed range like getRange(1, 1, 1000, 10) and relying on Sheets to ignore blank cells. It does not ignore them cleanly — it writes empty strings over anything that was there before, and if your columns vary across tabs you can get a dimension mismatch error that crashes the whole run.

The correct pattern is target.getRange(1, 1, merged.length, merged[0].length).setValues(merged). You derive both dimensions from the merged array itself. merged.length gives you the exact row count after all the slices and concatenations. merged[0].length gives you the column count from the first row. The range and the data are always the same shape, so setValues never throws.

Running it on a trigger or by hand

Open Extensions > Apps Script, paste the function, save, and run combineTabs() directly from the editor the first time. Sheets will ask for permission to access your spreadsheet — approve it. After that, the Combined tab appears (or is cleared and rewritten if it already existed).

If your source tabs update on a schedule, wire this to a time-driven trigger: in the Apps Script editor go to Triggers (the clock icon on the left), add a trigger for combineTabs, and choose a time interval. For daily refreshes, an overnight time-based trigger costs zero quota. The script uses one getValues() call per source sheet and one setValues() call total, so even 20 tabs stays well under Apps Script's 6-minute execution limit and the 100 calls-per-100-seconds Sheets API quota.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

What happens if my tabs have different numbers of columns?: The merged array will have rows of different lengths, and setValues() requires a rectangular array — it will throw 'The number of columns in the data does not match the number of columns in the range.' Pad each row to a consistent column count before merging, or restrict the script to tabs that share the same schema.
Can I exclude certain tabs from the merge, not just the target?: Add an exclusion list: var skip = ['Combined', 'Config', 'Lookup']; then replace the single getName() check with if (skip.indexOf(sheet.getName()) !== -1) continue; That covers any number of tabs you want to leave out.
The script ran but the Combined tab is empty — why?: The most likely cause is that every sheet returned an empty getDataRange(). A sheet with no data returns a range of one cell (A1), and getValues() on that returns [['']], which passes the length check but produces a one-row merged array of empty strings. Add a guard: if (rows.length === 1 && rows[0][0] === '') continue; to skip genuinely empty tabs.
Does this work if some tabs have blank rows in the middle of the data?: Yes. getDataRange() returns everything from A1 to the last cell with content, including internal blank rows. Those blank rows will appear in Combined exactly as they do in the source tab. If you want them stripped, filter the merged array: merged = merged.filter(function(row) { return row.some(function(cell) { return cell !== ''; }); }); before calling setValues.

// one good script a week