Importers and upserts assume row 1 is headers. Empty tabs and "someone deleted column C" both break that. This helper creates the header row when the sheet is blank, or throws a clear error when expected columns are missing — so you fail at the start, not after writing 400 wrong rows.
What you'll need
- A
Sheetobject - An ordered array of expected header names (exact string match)
Why validate up front
appendObjectAsRow / upsertRowByKey map by header name. If row 1 is empty, every key becomes a blank cell. If a client renamed email to Email Address, you'll write into the wrong columns (or nowhere). Catching that in one place saves the archaeology.
How to use this snippet
function ensureHeaders(sheet, expectedHeaders) {
expectedHeaders = expectedHeaders || [];
if (!expectedHeaders.length) {
throw new Error('ensureHeaders: expectedHeaders required');
}
var lastCol = Math.max(sheet.getLastColumn(), expectedHeaders.length);
var existing = lastCol < 1 ? [] : sheet.getRange(1, 1, 1, lastCol).getValues()[0];
var hasAny = existing.some(function (c) {
return c !== '' && c !== null;
});
if (!hasAny) {
sheet.getRange(1, 1, 1, expectedHeaders.length).setValues([expectedHeaders]);
sheet.setFrozenRows(1);
return { created: true, headers: expectedHeaders.slice() };
}
var missing = expectedHeaders.filter(function (h) {
return existing.indexOf(h) === -1;
});
if (missing.length) {
throw new Error('ensureHeaders: missing columns: ' + missing.join(', '));
}
return { created: false, headers: existing.slice(0, expectedHeaders.length) };
}
Tips:
- Matching is exact and case-sensitive — normalize headers upstream if clients are sloppy with casing.
- Extra columns beyond
expectedHeadersare allowed; only missing required names fail. - Pair with
clearSheetKeepHeaderson refresh imports so the header row you just ensured survives the wipe.
Example
function prepareLeadsTab() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Leads') || ss.insertSheet('Leads');
ensureHeaders(sheet, ['email', 'name', 'status', 'updatedAt']);
return sheet;
}
Happy Coding!
