Hard-coding column letters (C, D) breaks the moment a client inserts a column. Build a { headerName: columnIndex } map once from row 1, then read and write by name. Pair with ensureHeaders so missing columns fail at setup, not mid-import.
What you'll need
- A
Sheetwith a header row (default row 1) - Exact header strings (trimmed; case-sensitive)
Why a map beats indexOf in a loop
Looking up one column with indexOf is fine. Looking up five columns on every row re-scans the header array every time. One map, then cols.email / cols.status everywhere.
How to use this snippet
function buildHeaderMap(sheet, headerRow) {
headerRow = headerRow || 1;
var lastCol = sheet.getLastColumn();
if (lastCol < 1) return {};
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
var map = {};
for (var i = 0; i < headers.length; i++) {
var name = String(headers[i] == null ? '' : headers[i]).trim();
if (!name) continue;
if (map.hasOwnProperty(name)) {
throw new Error('buildHeaderMap: duplicate header "' + name + '"');
}
map[name] = i + 1; // 1-based for SpreadsheetApp ranges
}
return map;
}
Example
function stampSyncedAt() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');
var cols = buildHeaderMap(sheet);
if (!cols.email || !cols.syncedAt) {
throw new Error('Need email and syncedAt columns');
}
var last = sheet.getLastRow();
if (last < 2) return;
var emails = sheet.getRange(2, cols.email, last - 1, 1).getValues();
var out = emails.map(function () { return [new Date()]; });
sheet.getRange(2, cols.syncedAt, last - 1, 1).setValues(out);
}
Tips:
- Values are 1-based column indexes so they plug straight into
getRange. - Blank header cells are skipped; duplicate non-blank names throw — fix the sheet instead of guessing.
- For object-row workflows, prefer
rowsToObjectsafter a singlegetValues; use this map when you need targeted column reads/writes without loading the whole grid.
Happy Coding!
