setName and insertSheet reject characters Sheets reserves (: \ / ? * [ ]), and long client names blow past the 100-character tab limit. sanitizeSheetName trims, strips illegal chars, collapses whitespace, and falls back to a safe default — so menu-driven clones don't die on Acme / Q3: Final*. Pair with copyTemplateSheet after you pick a display name.
What you'll need
- A raw string from a prompt, form, or CRM field
- Optional
maxLength(Sheets hard-caps around 100) andfallback - A uniqueness check afterward (this helper does not de-dupe tab names)
How to use this snippet
function sanitizeSheetName(name, options) {
options = options || {};
var maxLen = options.maxLength || 100;
var fallback = options.fallback || 'Sheet';
var s = String(name == null ? '' : name).trim();
// Sheets forbids: : \ / ? * [ ]
s = s.replace(/[:\\\/\?\*\[\]]/g, ' ');
s = s.replace(/\s+/g, ' ').trim();
// Leading apostrophe is a Sheets quoting quirk — drop it
if (s.charAt(0) === "'") s = s.substring(1);
if (!s) s = fallback;
if (s.length > maxLen) s = s.substring(0, maxLen).trim();
if (!s) s = fallback;
return s;
}
Example
function menuNewClientSheet() {
var ui = SpreadsheetApp.getUi();
var res = ui.prompt('New client sheet name', 'Client or project name', ui.ButtonSet.OK_CANCEL);
if (res.getSelectedButton() !== ui.Button.OK) return;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var base = sanitizeSheetName(res.getResponseText(), { fallback: 'Client' });
var name = base;
var n = 2;
while (ss.getSheetByName(name)) {
name = sanitizeSheetName(base + ' (' + n + ')');
n++;
}
copyTemplateSheet(ss, 'Client Template', name, { createdHeader: 'Created' });
ss.toast('Created ' + name, 'Clients', 4);
}
Tips:
- Sanitize first, then resolve collisions (
Name,Name (2), …). - Don't silently map every bad name to
"Sheet"without a toast — operators need to know you changed it. - Workbook file names use Drive rules; this helper is for tab names only.
- Complements
copyTemplateSheetandgetOrCreateSheet.
Happy Coding!
