Clone a template sheet for every new client

TL;DR — Clone a Google Sheets client template tab with Apps Script: menu duplicate, clear sample rows, stamp Created, and stop copying last month’s junk tabs.

Freelancer / ops pain: you keep copying last month's tab and inheriting junk — stale sample rows, half-edited formulas, a filter that still says "Acme". Keep one clean Client Template tab. Add a menu that duplicates it with a dated or client name, clears data under the headers, and stamps a Created cell. Practical bootstrapper workflow — not a CRM.

TL;DR

Maintain one hidden template tab (headers + formatting only).
copyTemplateSheet(ss, templateName, newName) → copy, clear rows 2+, optional Created stamp.
Menu prompt for the new tab name; refuse collisions.
Pair with getOrCreateSheet thinking for helper tabs — this guide is about client workbooks' sheets.
Don't copy last month's live tab ever again.

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

Why is "Duplicate" in the UI a trap?

Sheets' built-in Duplicate is fast and wrong for onboarding. It copies:

Sample / leftover data
Named ranges aimed at the wrong place
Filter views and frozen panes you forgot about
Comments that name the previous client

A template tab you control is the contract: headers, validation, column widths, maybe a header color. Everything else starts empty.

What should the template tab contain?

Minimum viable template:

email	name	status	notes	Created
(empty after clone)				(stamped)

Optional: data validation on status, conditional formatting, a frozen header row. Avoid volatile sample emails that someone will ship to production by accident.

Hide the template (sheet.hideSheet()) so operators open it on purpose.

How do you copy the template in Apps Script?

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;
}

clearContent keeps formatting and validation while wiping values — usually what you want. Use clear() only if you also need to drop formatting from sample cells.

How do you wire a menu for operators?

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Clients')
    .addItem('New client from template', 'menuNewClientSheet')
    .addToUi();
}

function menuNewClientSheet() {
  var ui = SpreadsheetApp.getUi();
  var tz = Session.getScriptTimeZone();
  var suggestion = 'Client — ' + Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');

  var res = ui.prompt(
    'New client sheet',
    'Name for the new tab (e.g. Acme — 2026-09):',
    ui.ButtonSet.OK_CANCEL
  );
  if (res.getSelectedButton() !== ui.Button.OK) return;

  var newName = String(res.getResponseText() || '').trim() || suggestion;
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  try {
    var sheet = copyTemplateSheet(ss, 'Client Template', newName, {
      createdHeader: 'Created'
    });
    ss.setActiveSheet(sheet);
    ss.toast('Created ' + newName, 'Clients', 4);
  } catch (err) {
    ui.alert(err.message || String(err));
  }
}

Collision throws instead of silently renaming — good. Operators can pick another name.

Should you clone a whole spreadsheet instead?

Sometimes. If each client needs their own file (sharing, Drive permissions, separate Apps Script projects), copy the file with DriveApp and then clear sheets inside the copy. This guide stays tab-scoped because most bootstrapper ops workbooks are one spreadsheet with many client tabs — cheaper to search, one script project, one trigger set.

Rule of thumb:

Same team, shared ops book → template tab + copyTemplateSheet
Client gets editor access to only their data → separate spreadsheet copy

How do you keep the template from drifting?

Treat template edits like schema changes:

Unhide Client Template
Add the column / validation
Re-hide
Note the change in a tiny README tab or Log line

Existing client tabs won't auto-gain new columns — that's fine. New clones pick up the new schema. If you must backfill, write a one-off ensureHeaders pass (see the header-map guides) instead of hand-editing twenty tabs.

How should you name new tabs?

Pick a convention and stick to it:

Acme — 2026-09
2026-09-22 Acme
Acme

Include enough to sort and search. Avoid characters Sheets tolerates poorly in exports (/ in some contexts). If operators forget the year, bake it into the prompt default with Utilities.formatDate.

var tz = Session.getScriptTimeZone();
var defaultName = 'Client — ' + Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');

What about charts, images, and script-bound bits?

copyTo copies sheet-local charts and images. It does not duplicate spreadsheet-level Apps Script — your menu/code stay on the project. Named ranges that were spreadsheet-scoped can surprise you; prefer sheet-local ranges on the template or recreate them in Setup.

After clone, optionally jump the user to cell A2:

sheet.getRange(2, 1).activate();

Can you batch-create tabs from a roster?

Yes — read a Clients roster sheet and loop. Still use copyTemplateSheet per row so collisions throw instead of overwriting.

function provisionFromRoster() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var roster = ss.getSheetByName('Clients');
  var values = roster.getDataRange().getValues();
  var headers = values[0];
  var nameIdx = headers.indexOf('sheetName');
  for (var r = 1; r < values.length; r++) {
    var newName = String(values[r][nameIdx] || '').trim();
    if (!newName || ss.getSheetByName(newName)) continue;
    copyTemplateSheet(ss, 'Client Template', newName, { createdHeader: 'Created' });
  }
}

Skip existing names so re-runs are safe. Pair with a Dry Run property if the roster is long.

Minimal test plan

Clone → new tab, headers only, Created stamped.
Clone same name again → error, no partial sheet.
Template has sample row → cleared after clone.
Unhide template, add column, re-hide → new clone has column; old tabs unchanged.
Menu cancel → no sheet created.

Soft Co-Pilot note

Naming collisions, Created stamps, and "should I clear row 2 only?" are the kind of small decisions that stall a Friday setup. NitroGAS includes copyTemplateSheet and getOrCreateSheet as snippets; Co-Pilot can help adapt the menu prompt to your naming scheme. Free extension; Co-Pilot optional.

Closing checklist

One Client Template tab (hidden) with correct headers
Menu item calls copyTemplateSheet with clear-data default on
Created column stamped when present
Duplicate names fail loud
Operators never Duplicate last month's live tab
Template changes documented for future you

Happy Coding!