writeGroupsToTabs(ss, groups, headers) takes rows grouped by a key, like one group per owner, region, or client, and writes each group to its own tab. It creates tabs that don't exist and clears ones that do, then writes the header row and the group's rows in one setValues call per tab.
When to use
- Splitting a master list into one tab per person or team
- Rebuilding per-client tabs from a single data tab every night
- Any "filter view for each X" that should be a real tab
How to use this snippet
/**
* Write each group of rows to its own tab, named after the group key.
* Creates missing tabs, clears existing ones, writes headers and freezes row 1.
*
* @param {GoogleAppsScript.Spreadsheet.Spreadsheet} ss
* @param {Object<string, Array<Object|Array>>} groups key -> rows (objects or arrays)
* @param {string[]} headers header row; also the field order for object rows
* @return {string[]} the tab names written
*/
function writeGroupsToTabs(ss, groups, headers) {
var written = [];
Object.keys(groups).forEach(function (key) {
var name = String(key || '(blank)').replace(/[\[\]\*\?:\/\\]/g, '-').slice(0, 100);
var sheet = ss.getSheetByName(name) || ss.insertSheet(name);
sheet.clearContents();
var rows = groups[key].map(function (r) {
return Array.isArray(r) ? r : headers.map(function (h) { return r[h] === undefined ? '' : r[h]; });
});
var values = [headers].concat(rows);
sheet.getRange(1, 1, values.length, headers.length).setValues(values);
sheet.setFrozenRows(1);
written.push(name);
});
return written;
}
Example
function splitByOwner() {
var ss = SpreadsheetApp.getActive();
var rows = rowsToObjects(ss.getSheetByName('Master').getDataRange().getValues());
var groups = groupObjectsByKey(rows, 'Owner');
writeGroupsToTabs(ss, groups, ['Task', 'Due', 'Status']);
}
Notes
- Group with Group Objects By Key first.
- Tab names are the group keys with the characters Sheets doesn't allow replaced, cut to 100 characters. A blank key becomes
(blank). clearContentskeeps formatting but removes values. Don't point it at a tab people type into.- Tabs for keys that no longer exist are left alone, not deleted.
Happy Coding!
