// Sheets · Apps Script

Use a Set to skip already-processed rows.

indexOf inside a row loop hits O(n²) complexity and walks straight into Apps Script's 6-minute execution limit. Switch to Set.has for O(1) lookups — just watch out for Date objects, which need getTime() to match correctly.

“I need to skip rows I've already processed without my script timing out on large sheets.”

The script

copy · paste · trigger

skipProcessed.gs

Apps Script

// Mark processed order IDs in column A; skip on re-run
function processNewOrders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders');
  var data = sheet.getDataRange().getValues();

  // Build a Set from the 'Done' log in column C
  var doneSet = new Set();
  for (var i = 1; i < data.length; i++) {
    if (data[i][2] === 'Done') {
      doneSet.add(String(data[i][0]));
    }
  }

  for (var j = 1; j < data.length; j++) {
    var orderId = String(data[j][0]);
    if (doneSet.has(orderId)) continue;
    // process row j here
    sheet.getRange(j + 1, 3).setValue('Done');
  }
}

Generate yours

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

Walkthrough

Why indexOf kills large sheets

The pattern looks harmless at first: you have an array of already-processed IDs and call processedIds.indexOf(currentId) on every row. At 500 rows that's 250,000 comparisons in the worst case. At 20,000 rows it's 200 million. Apps Script has a hard 6-minute execution limit, and V8 runtime or not, you will hit it before you finish.

indexOf walks the entire array on every call — that's O(n²) growth. The script doesn't slow down gracefully; it just dies mid-sheet, leaving you with a half-processed dataset and no clean resume point.

The fix is a Set built once before the loop. Set.has is a hash lookup: O(1) regardless of how many entries are in it. Ten entries or ten thousand, the cost is the same.

Building the Set correctly — the Date trap

getValues() returns cell values as JavaScript types: strings stay strings, numbers stay numbers, and date cells come back as Date objects. This is where most scripts quietly break.

JavaScript Set uses reference equality for objects. Two Date objects representing the same timestamp are not the same reference, so doneSet.has(someDateObject) always returns false even when the date genuinely matches. The first time I hit this I spent an hour convinced the Set logic was wrong before realizing every cell in my key column was a date.

The fix: normalize before inserting and before checking. For date keys, use date.getTime() to get the numeric millisecond value, or call date.toISOString() for a readable string. For anything that might be a number or string depending on how the cell is formatted, wrapping in String() is the safest single move — it's what the snippet above does for order IDs.

Never mix coercion strategies between the build pass and the lookup pass. If you insert getTime() values, check getTime() values. Inconsistency is the only way Set.has still fails after you've fixed the Date issue.

One Set build, not one per row

The single architectural rule: build the Set once outside the main loop, then call .has() inside it. A common mistake is rebuilding the lookup structure on every iteration — that recovers none of the O(1) benefit.

In the snippet, the first loop populates doneSet from column C status flags. The second loop does the actual work and calls doneSet.has() to skip rows. The two passes are deliberate. You could collapse them, but separating them makes the intent readable and avoids the temptation to add rows to the Set mid-loop in ways that produce incorrect skips.

If you're resuming a long job across multiple triggered runs (time-based triggers are the standard pattern for jobs that exceed 6 minutes), write 'Done' to the sheet at the end of each row. On the next trigger fire, the Set rebuild picks up all previously-completed rows and the script resumes cleanly from where it left off.

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

Does Set.has work with numbers from cells, or do I need to convert them?: It works, but only if you're consistent. If the cell returns a number and you add that number to the Set, .has() with the same number returns true. The danger is mixed sheets where the same column sometimes formats as text and sometimes as a number — getValue() returns different types and they won't match. Wrapping both sides in String() costs nothing and eliminates the mismatch.
My key column is a date. getTime() or toISOString()?: Either works. getTime() is a number (milliseconds since epoch) and is slightly faster to compare. toISOString() gives you a readable string in logs, which helps when debugging mismatches. The critical thing is that both sides of the lookup use the same form. I keep getTime() in a utils file since it's the less surprising choice when someone else reads the script.
Can I use a plain object as a lookup map instead of a Set?: Yes. obj[key] = true on insert, key in obj on lookup. Both are O(1). Set is cleaner for existence checks because the intent is explicit and you avoid the awkward 'true' value. If you need to store extra data per key — say, the row index where you first saw an ID — use a Map instead, which gives you .has() and .get() together.
Will this approach work if the sheet has more than 50,000 rows?: getDataRange().getValues() loads the entire sheet into memory as a 2D array in a single Sheets API call, which is fast. The real ceiling is Apps Script's memory limit (roughly 50–100 MB depending on runtime), not row count. At 50,000 rows with a handful of columns you're well within it. If you're pushing past that, batch with getRange(startRow, 1, batchSize, numCols).getValues() and rebuild the Set from your status column before each batch.