Map columns by header, not by letter

TL;DR — Map Google Sheet columns by header name, not letter: build a header→index map once, read/write by name, and fail loud when a required column goes missing.

Column C is a rumor. The moment a client inserts "Middle Name" between A and B, every hard-coded letter in your script writes into the wrong field — or worse, looks fine until finance notices. Bootstrappers should map header → column index once per run and address data by name.

TL;DR

Read the header row once → buildHeaderMap(sheet) → { email: 2, status: 5, ... }.
Read/write with cols.email, never 'C' or magic 3.
Fail loud when a required header is missing (don't invent columns silently).
Pair with ensureHeaders at Setup so blank tabs get a schema before the first import.
Prefer rowsToObjects when you're transforming whole tables in memory.

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 do letter indexes rot?

Sheets are living documents. Sales adds a column. Ops renames Stage to Status. Someone sorts the header row while "cleaning up." Your script still writes to column 3. The bug report arrives two weeks later as "the sync is flaky."

Named columns survive reordering. They don't survive silent renames — and that's good. A missing-key error on run one beats corrupt data on run thirty.

How do you build the map?

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('Duplicate header "' + name + '"');
    }
    map[name] = i + 1; // 1-based for getRange
  }
  return map;
}

function requireCols(map, names) {
  var missing = names.filter(function (n) { return !map[n]; });
  if (missing.length) {
    throw new Error('Missing columns: ' + missing.join(', '));
  }
}

Call requireCols(cols, ['email', 'status', 'updatedAt']) before any writes.

What does a named write look like?

function markSynced(sheet, rowNumber) {
  var cols = buildHeaderMap(sheet);
  requireCols(cols, ['syncedAt', 'syncStatus']);
  sheet.getRange(rowNumber, cols.syncedAt).setValue(new Date());
  sheet.getRange(rowNumber, cols.syncStatus).setValue('ok');
}

For bulk updates, still batch:

function stampAllSynced(sheet) {
  var cols = buildHeaderMap(sheet);
  requireCols(cols, ['syncedAt']);
  var last = sheet.getLastRow();
  if (last < 2) return;

  var height = last - 1;
  var values = [];
  for (var i = 0; i < height; i++) values.push([new Date()]);
  sheet.getRange(2, cols.syncedAt, height, 1).setValues(values);
}

Named start column + block setValues beats cell-by-cell setValue in a loop.

When should you use rowsToObjects instead?

If the job is "filter active rows and rewrite a tab," convert once:

function activeOnly() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('People');
  var values = sheet.getDataRange().getValues();
  var people = rowsToObjects(values);
  var active = people.filter(function (p) { return p.Status === 'Active'; });
  var out = objectsToRows(active, values[0]);
  // write out somewhere…
}

Use the header map when you need surgical column access (stamping one field, reading a key column for upsert). Use objects when the whole row is the unit of work. Both beat letters.

How do you fail loud without being fragile?

Exact string match is strict on purpose. Soften only at the edges you control:

function normalizeHeader(h) {
  return String(h || '').trim().toLowerCase().replace(/\s+/g, ' ');
}

function buildHeaderMapLoose(sheet) {
  var raw = buildHeaderMap(sheet); // still detects duplicates on raw trimmed names
  // Remap to normalized keys if your project standardizes casing
  var loose = {};
  Object.keys(raw).forEach(function (k) {
    loose[normalizeHeader(k)] = raw[k];
  });
  return loose;
}

Pick one convention per project. Document it in Setup (ensureHeaders(['email', 'name', 'status'])) so the sheet and the script agree.

What about inserted columns and extras?

Extra columns are fine — ignore them. Missing required columns must throw. Duplicate headers must throw. Blank header cells should be skipped (or treated as a schema smell in Setup). That policy keeps imports boring.

function prepareLeads() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Leads') || ss.insertSheet('Leads');
  ensureHeaders(sheet, ['email', 'name', 'status', 'updatedAt']);
  return buildHeaderMap(sheet);
}

How does this pair with upserts?

upsertRowByKey (or your equivalent) should take a key header name, not a column letter. Internally it builds the map once, finds the key column, and scans. Your job as the bootstrapper author is to make sure Setup ran ensureHeaders so the key exists before the first nightly sync.

Minimal test plan

Import with columns in order A–D → counts match.
Insert a column at B → re-run → still correct (map shifted).
Rename email → Email Address without updating script → fails loud with missing column.
Duplicate two status headers → fails at map build.
Empty sheet → ensureHeaders creates row 1; map succeeds.

Should you cache the map across functions?

Yes within a single execution — build once at the top of sync() and pass cols down. Don't stash it in Script Properties or CacheService across runs; headers change and a stale map is how you get confident wrong writes. Fresh map, every run, milliseconds of sheet I/O well spent.

If several functions need it, a small getCols_(sheet) memo on a script-global for that execution is enough:

var COLS_MEMO = null;
function getCols_(sheet) {
  if (!COLS_MEMO) COLS_MEMO = buildHeaderMap(sheet);
  return COLS_MEMO;
}

Clear the memo if Setup rewrites headers mid-session (rare).

Soft Co-Pilot note

Client schemas drift — NitroGAS includes buildHeaderMap, ensureHeaders, and rowsToObjects as snippets, and Co-Pilot helps alias odd header labels without hard-coding letters again. Free extension; Co-Pilot optional. The map-by-name approach above is copy-paste ready either way.

Closing checklist

No raw 'C' / getRange(r, 3) for business fields
buildHeaderMap + requireCols on every entry point
ensureHeaders in Setup / first-run
Duplicates and missing required headers throw
Bulk paths still use block getValues / setValues
Tested after inserting a column in the middle

Happy Coding!