Copying last month's client tab inherits junk formulas, stale sample rows, and mystery filters. Keep a clean template tab, then copyTemplateSheet duplicates it, clears data under the header, and optionally stamps a Created cell. Complements getOrCreateSheet without overlapping archive / move-row flows.
What you'll need
- A spreadsheet with a template tab (headers + formatting only — or sample rows you're willing to wipe)
- A unique
newName(throws if the tab already exists) - Optional
createdHeadermatching an exact header string
How to use this snippet
function copyTemplateSheet(ss, templateName, newName, options) {
options = options || {};
var template = ss.getSheetByName(templateName);
if (!template) {
throw new Error('copyTemplateSheet: missing template "' + templateName + '"');
}
if (ss.getSheetByName(newName)) {
throw new Error('copyTemplateSheet: sheet already exists "' + newName + '"');
}
var copy = template.copyTo(ss).setName(newName);
if (options.clearDataRows !== false) {
var lastRow = copy.getLastRow();
var lastCol = copy.getLastColumn();
if (lastRow > 1 && lastCol > 0) {
copy.getRange(2, 1, lastRow - 1, lastCol).clearContent();
}
}
if (options.createdHeader) {
var headers = copy.getRange(1, 1, 1, copy.getLastColumn()).getValues()[0];
var idx = headers.indexOf(options.createdHeader);
if (idx !== -1) {
copy.getRange(2, idx + 1).setValue(new Date());
}
}
return copy;
}
Example
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Clients')
.addItem('New client from template', 'menuNewClientSheet')
.addToUi();
}
function menuNewClientSheet() {
var ui = SpreadsheetApp.getUi();
var name = ui.prompt('New client sheet name', 'e.g. Acme — 2026-09', ui.ButtonSet.OK_CANCEL);
if (name.getSelectedButton() !== ui.Button.OK) return;
var newName = String(name.getResponseText() || '').trim();
if (!newName) return;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = copyTemplateSheet(ss, 'Client Template', newName, {
createdHeader: 'Created'
});
ss.setActiveSheet(sheet);
ss.toast('Created ' + newName, 'Clients', 4);
}
Tips:
- Hide the template tab so operators don't edit it by accident.
- Pass
{ clearDataRows: false }only when the template's sample rows are intentional starter data. - For Drive-level workbook clones, use Drive copy APIs — this helper is tab-scoped inside one spreadsheet.
Happy Coding!
