What if two columns have the same header name?

The loop overwrites the earlier index with the later one, so you'll silently get the rightmost duplicate. If your sheet can have duplicate headers, check for collisions when building the map and throw an error early rather than letting the wrong column get read.

Does this work with getRange using A1 notation instead of row/column numbers?

Not directly — getRange with A1 notation takes a string like 'C2:C100'. You can convert a 1-based column index to a letter using a small helper (columnToLetter), but sticking to the numeric overload of getRange is simpler and avoids that conversion entirely.

My header row isn't row 1 — it's row 3. How do I adjust?

Change the first argument of getRange inside getHeaderMap from 1 to 3: sheet.getRange(3, 1, 1, sheet.getLastColumn()).getValues()[0]. Everything else in the function stays the same; just update the data reads to start from row 4 instead of row 2.

Is there a performance cost to calling getHeaderMap on every script run?

One extra Sheets API read per run — negligible for triggered scripts or manual runs. If you're running hundreds of executions per hour via time-based triggers, cache the result with CacheService.getScriptCache() and JSON.stringify/parse to avoid the repeated read.

// Sheets · Apps Script

Read a column by its header name, not its index.

Stop hard-coding column numbers in Apps Script. Build a header map from row 1 so your script survives inserted columns without silently returning wrong data.

“I want to look up a column's position by its header text so adding a column to my sheet doesn't break every getRange call in my script.”

The script

copy · paste · trigger

headerMap.gs

Apps Script

// Returns a map of { headerText: columnIndex } where index is 1-based.
// Call once per script run; don't call inside a loop.
function getHeaderMap(sheet) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const map = {};
  for (var i = 0; i < headers.length; i++) {
    map[headers[i]] = i + 1;
  }
  return map;
}

function processRows() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const col = getHeaderMap(sheet);
  const lastRow = sheet.getLastRow();
  const emails = sheet.getRange(2, col['Email'], lastRow - 1, 1).getValues();
  const statuses = sheet.getRange(2, col['Status'], lastRow - 1, 1).getValues();
  Logger.log(emails[0][0] + ' -> ' + statuses[0][0]);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why hard-coded column numbers always break eventually

The moment someone inserts a column to the left of your 'Email' column, col 3 becomes col 4 and your script silently reads the wrong data. No error, no warning — just garbage, or an empty string that passes your null check and corrupts downstream output. I've burned an afternoon tracking down exactly this after a client added a 'Phone' column without telling me.

The fix is to read row 1 once at the start of your script and build a plain object that maps each header string to its 1-based column index. After that, every getRange call uses col['Email'] instead of the magic number 3, and inserting columns becomes a non-event.

Building and using the header map

getHeaderMap reads the entire first row in one API call using getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]. That returns a flat array of strings. A simple for loop turns it into an object where each key is the header text and each value is the 1-based index getRange expects.

One thing worth noting: getValues() returns a 2D array even for a single row, so you need the [0] at the end to get the flat array. The resulting map object is cheap to pass around, so build it once at the top of your main function and pass it down to helpers — don't call getHeaderMap inside a loop or you'll rack up Sheets API quota (the free tier caps you at 300 read requests per minute per project).

With the map in hand, sheet.getRange(2, col['Status'], lastRow - 1, 1) reads the entire Status column starting from row 2. If someone renames the header or the column doesn't exist, col['Status'] is undefined and getRange throws immediately — which is the right failure mode. You'll catch it during development, not six weeks later in a production run.

Handling missing or inconsistent headers

If your sheet has optional columns that may not always be present, guard with a simple existence check before using the index: if (col['Notes']) { ... }. That's cleaner than wrapping everything in try/catch and much easier to read when you come back to the script three months later.

For sheets where the header row might vary in case ('email' vs 'Email'), normalize on the way in: map[headers[i].trim().toLowerCase()] = i + 1. Then access it as col['email'] everywhere. Pick one convention and stick to it — mixing normalized and raw keys in the same script is where the subtle bugs live.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

What if two columns have the same header name?: The loop overwrites the earlier index with the later one, so you'll silently get the rightmost duplicate. If your sheet can have duplicate headers, check for collisions when building the map and throw an error early rather than letting the wrong column get read.
Does this work with getRange using A1 notation instead of row/column numbers?: Not directly — getRange with A1 notation takes a string like 'C2:C100'. You can convert a 1-based column index to a letter using a small helper (columnToLetter), but sticking to the numeric overload of getRange is simpler and avoids that conversion entirely.
My header row isn't row 1 — it's row 3. How do I adjust?: Change the first argument of getRange inside getHeaderMap from 1 to 3: sheet.getRange(3, 1, 1, sheet.getLastColumn()).getValues()[0]. Everything else in the function stays the same; just update the data reads to start from row 4 instead of row 2.
Is there a performance cost to calling getHeaderMap on every script run?: One extra Sheets API read per run — negligible for triggered scripts or manual runs. If you're running hundreds of executions per hour via time-based triggers, cache the result with CacheService.getScriptCache() and JSON.stringify/parse to avoid the repeated read.

// one good script a week