Twelve rep tabs, one per person, and a manager who wants one list. combineTabsWithSource(ss, pattern, opts) reads every sheet whose name matches a regex, maps each row by its header names (so column order can differ between tabs), and returns one 2D array with a Source column first. The result goes straight into setValues.
When to use
- Rolling per-rep, per-region or per-month tabs into a Summary tab
- Tabs that were copied by hand and drifted: an extra column here, a reordered one there
- Any rollup where you need to know which tab a row came from
How to use this snippet
/**
* Combine every tab whose name matches `pattern` into one table.
* Rows are mapped by header name; the first column is the source tab.
*
* @param {Spreadsheet} ss
* @param {RegExp|string} pattern e.g. /^Rep - /
* @param {{sourceHeader?: string, headerRows?: number}} [opts]
* @return {Array[]} header row + data rows, ready for setValues
*/
function combineTabsWithSource(ss, pattern, opts) {
opts = opts || {};
var sourceHeader = opts.sourceHeader || 'Source';
var headerRows = opts.headerRows == null ? 1 : opts.headerRows;
var re = pattern instanceof RegExp ? pattern : new RegExp(pattern);
var headers = [];
var seen = {};
var records = [];
ss.getSheets().forEach(function (sh) {
var name = sh.getName();
if (!re.test(name) || sh.getLastRow() <= headerRows) return;
var values = sh.getDataRange().getValues();
var head = values[headerRows - 1].map(function (h) { return String(h).trim(); });
head.forEach(function (h) {
if (h && !seen[h]) { seen[h] = true; headers.push(h); }
});
for (var r = headerRows; r < values.length; r++) {
var row = values[r];
if (row.join('') === '') continue;
var rec = {};
head.forEach(function (h, c) { if (h) rec[h] = row[c]; });
records.push({ source: name, rec: rec });
}
});
var out = [[sourceHeader].concat(headers)];
records.forEach(function (x) {
out.push([x.source].concat(headers.map(function (h) {
return x.rec[h] === undefined ? '' : x.rec[h];
})));
});
return out;
}
Example
function buildRollup() {
var ss = SpreadsheetApp.getActive();
var table = combineTabsWithSource(ss, /^Rep - /);
var out = ss.getSheetByName('Summary') || ss.insertSheet('Summary');
out.clearContents();
out.getRange(1, 1, table.length, table[0].length).setValues(table);
}
// Source | Deal | Amount | Stage
// Rep - Ana | A | 5 |
// Rep - Ben | B | 7 | Won
Tips:
- Headers are the union of every matched tab, in the order they're first seen. A tab without a column gets blanks there.
- Fully blank rows are skipped. Tabs with only a header row are ignored.
- Make sure the pattern can't match the Summary tab itself, or each rerun will read in its own output.
headerRows: 2uses the second row as headers when the first row is a title.
Tip: NitroGAS Co-Pilot can wire this to a nightly trigger and a menu item. Paste your tab names and it'll write the regex.
Happy Coding!
