Design idempotent, re-runnable automations

An automation that is idempotent produces the same end state no matter how many times it runs. Run it once, run it ten times — the data looks identical and no side effect fires twice. This is one of the most important properties a real-world script can have, and the one beginners most often skip.

You will hit the need for it sooner than you expect. Time-based triggers occasionally fire twice, scripts time out halfway and get restarted, and you will absolutely re-run a function by hand while debugging. If any of those replays sends a second email, charges a card again, or appends a duplicate row, the automation is not finished — it is a liability.

The principle

Every script should produce the same end state whether run once or ten times.

The key shift is to stop thinking “do the work” and start thinking “make the world match the desired state”. A non-idempotent script blindly performs an action. An idempotent one first asks whether the action still needs doing, and only acts if the answer is yes.

That distinction matters most for side effects — anything that reaches outside the script and cannot be undone:

• Sending an email or a chat message.
• Creating a calendar event, a Drive file, or a folder.
• Charging a customer or calling an external API that mutates data.
• Appending rows to a log or a downstream sheet.

Reading data is always safe to repeat. It is the writes and the outbound messages that need protecting.

Patterns

A handful of patterns cover almost every case. Reach for whichever one fits the data you already have.

• Check before write. Before doing a unit of work, ask whether it is already done. If yes, skip it. This is the simplest and most common pattern.
• Use a “done” marker column. Stamp a processedAt timestamp (or a status value) on each row after its work completes. The marker is the record of what has been handled.
• Idempotent IDs. When you create downstream records, derive their ID from the input data — a hash of the order number, say — rather than a random UUID. Re-running then produces the same ID, so a duplicate insert is detectable or rejected outright.
• Upsert instead of insert. If a target row keyed by some ID already exists, update it; otherwise create it. The operation lands in the same final state regardless of how many times it runs.

Pattern	Best when	Cost
Check before write	You can cheaply test “is this done?”	One extra read
Done marker column	Processing rows in a sheet	One column, one write per row
Idempotent IDs	You create records in another system	Deterministic hashing
Upsert	The target is keyed and may already exist	A lookup before each write

Example

This loop processes order rows and is safe to run any number of times. The processedAt column is the marker: a row with a timestamp is skipped entirely.

function processOrders() {
  const sheet = SpreadsheetApp.openById('1abcOrdersId').getSheets()[0];

  // Read everything in one call — header row plus all data rows.
  const [h, ...rows] = sheet.getDataRange().getValues();

  // Build a name -> column-index map so code reads by header, not by number.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  rows.forEach((r, i) => {
    // The idempotency guard: if processedAt is already set, this row
    // was handled on a previous run — skip it and do nothing.
    if (r[col.processedAt]) return;

    // Do the actual work for this row (send mail, call an API, etc.).
    process(r);

    // Stamp the marker LAST, only after process() succeeded. The +2 maps
    // array index 0 to sheet row 2, since row 1 is the header.
    sheet.getRange(i + 2, col.processedAt + 1).setValue(new Date());
  });
}

The ordering is deliberate. The marker is written after the work, so if the script crashes mid-row the marker is absent and the next run retries that row. If you stamped the marker first, a crash would skip unfinished work forever.

Why it matters

• Trigger retries don’t double-charge customers. A duplicate trigger fire finds every row already marked and quietly does nothing.
• Manual re-runs are safe after debugging. You can hit Run repeatedly while fixing a bug without flooding inboxes or corrupting downstream data.
• Restarting after a timeout doesn’t redo work. When a long job exceeds the six-minute limit and you restart it, processed rows are skipped and the script resumes where it left off.

Common mistakes

• Stamping the marker before the work. If process() throws after the timestamp is written, that row is marked done but was never actually handled. Always mark after success.
• A marker write that is not atomic with the work. There is a tiny window between finishing the work and writing the marker where a crash causes a re-run. For truly critical side effects, prefer a check the target system can enforce — a unique key, an idempotency token on the API call.
• Using random IDs for created records. A UUID guarantees every re-run looks like a brand-new record, which is the opposite of idempotent. Derive IDs from the input.
• Forgetting that “check before write” reads stale data. If two runs overlap, both can read “not done” before either writes the marker. Avoid overlapping runs, or use a lock.
• Testing idempotency only on the happy path. Run the script twice in a row on the same data as a routine test. If the second run changes anything, it is not idempotent yet.