Checkpoint a long run so the next trigger finishes the job

TL;DR — Survive Apps Script’s ~6-minute wall: store a Script Properties cursor, exit early on a time budget, and let the next timed trigger resume — without restarting from row one.

You already batch setValues, respect quotas, and chunk CSV imports. Then a 45-minute export still dies at ~6 minutes with half the work done — and the next trigger starts over. The missing piece is a checkpoint: a tiny JSON blob in Script Properties that says “I got to cursor X.” Exit before the wall; the next run resumes.

This guide complements batch writes without timeouts and importing a Drive CSV in safe chunks. It does not rehash those patterns — it adds durable progress across executions.

TL;DR

Persist { key, cursor, updatedAt } (and optional metadata) in Script Properties
Process in small blocks; check a time budget (e.g. 4–5 minutes) every block
On budget exit: setCheckpoint and return cleanly
On full success: clearCheckpoint
Pair with a lock if two triggers can own the same job

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 “just run longer” fails

Apps Script caps a single execution. Time-driven triggers re-enter with a fresh global state. In-memory counters vanish. Without a durable cursor:

Retries redo expensive work
Partial side effects (emails sent, rows marked) get duplicated
Support tickets look like “the script is flaky” when it’s really wall-clock

A checkpoint is the bootstrapper answer: cheap, readable, no external DB.

Checkpoint helpers

function getCheckpoint(key) {
  var raw = PropertiesService.getScriptProperties().getProperty(key);
  if (!raw) return null;
  try { return JSON.parse(raw); } catch (e) { return null; }
}

function setCheckpoint(key, cursor, extra) {
  var payload = Object.assign(
    { key: key, cursor: cursor, updatedAt: new Date().toISOString() },
    extra || {}
  );
  PropertiesService.getScriptProperties().setProperty(key, JSON.stringify(payload));
  return payload;
}

function clearCheckpoint(key) {
  PropertiesService.getScriptProperties().deleteProperty(key);
}

Keep the payload small. Cursor can be a row number, a page token, or a Drive continuationToken — JSON-safe only.

Shape of a resumable trigger

var CP_KEY = 'job:pushRows';
var BLOCK = 100;
var BUDGET_MS = 4.5 * 60 * 1000;

function pushRowsTrigger() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(1000)) {
    console.log('pushRows: another run holds the lock');
    return;
  }
  try {
    var started = Date.now();
    var sheet = SpreadsheetApp.getActive().getSheetByName('Queue');
    var last = sheet.getLastRow();
    var cp = getCheckpoint(CP_KEY);
    var row = (cp && cp.cursor != null) ? Number(cp.cursor) : 2; // skip header

    while (row <= last) {
      if (Date.now() - started > BUDGET_MS) {
        setCheckpoint(CP_KEY, row, { last: last });
        console.log('checkpointed at', row, 'of', last);
        return;
      }

      var end = Math.min(row + BLOCK - 1, last);
      var values = sheet.getRange(row, 1, end - row + 1, sheet.getLastColumn()).getValues();
      processBlock_(values); // your UrlFetch / write-elsewhere work
      row = end + 1;
    }

    clearCheckpoint(CP_KEY);
    console.log('pushRows complete');
  } finally {
    lock.releaseLock();
  }
}

function processBlock_(values) {
  // … one block of side effects …
}

Notes:

Budget < wall. Leave headroom for the final setProperty and logging.
Lock + checkpoint. The lock prevents two resumes from double-processing the same cursor; the checkpoint survives across runs.
Idempotent blocks when you can (upsert by id) so a crash mid-block is recoverable.

What to store (and what not to)

Store	Skip
Cursor / page token	Entire working arrays
Job id / sheet name	Secrets (use Script Properties carefully; prefer not)
`updatedAt` for debugging	Full API responses

If you need richer state, store a pointer (Drive file id + offset) rather than megabytes in Properties.

Resume vs restart

Resume when the destination is append-only or upsert-safe and the cursor is trustworthy.
Restart (clear checkpoint) when the source snapshot changed incompatibly — e.g. someone sorted the sheet under you. Detect with a stored fingerprint (lastRow + hash of header row) in extra.

function headerFingerprint_(sheet) {
  return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].join('|');
}

// when starting a fresh intentional run:
// clearCheckpoint(CP_KEY);

Scheduling the next bite

A one-shot manual run is fine for testing; production usually wants a short-interval time-driven trigger (every 5–10 minutes) while a checkpoint exists, or a fixed nightly window that simply no-ops when there is nothing left:

function ensurePushTrigger_() {
  var handlers = ScriptApp.getProjectTriggers().map(function (t) {
    return t.getHandlerFunction();
  });
  if (handlers.indexOf('pushRowsTrigger') === -1) {
    ScriptApp.newTrigger('pushRowsTrigger').timeBased().everyMinutes(10).create();
  }
}

When clearCheckpoint runs, you can delete that trigger — or leave it; an empty queue should exit in milliseconds. Prefer boring schedules over clever “chain triggers from inside the run,” which are harder to audit.

Failure modes worth naming

Corrupt JSON in the property → treat as missing checkpoint (or email yourself and stop).
Cursor past lastRow → clear and stop; someone deleted rows.
Side effects before checkpoint write → prefer “process block → checkpoint past that block.” If you checkpoint first, a crash can skip work; if you process first, a crash can redo a block — pick the lesser evil for your domain and make blocks idempotent when redo is possible.

Soft Co-Pilot note

NitroGAS can pull in getCheckpoint / setCheckpoint; Co-Pilot helps wire the budget loop around your existing block processor — you still choose cursor meaning and idempotency. Free extension; Co-Pilot optional.

Closing checklist

Stable property key per job
Time budget with early return + setCheckpoint
clearCheckpoint only on full success
Lock when overlapping triggers are possible
Log cursor so “did it resume?” is answerable

Happy Coding!