Apps Script consumer accounts cap around six minutes. Long queues need checkpoints so the next run resumes.
processWithCheckpoint reads CHECKPOINT_ROW (default 2), processes BATCH rows, and stops when MAX_MS elapses.
On pause it stores the next row; on completion it deletes the property so the following run starts clean.
Combine with a frequent time-driven trigger or the continuation-trigger pattern.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Value | Purpose |
|---|---|---|
| WorkQueue | A–C | Work rows; col C status |
| Property | CHECKPOINT_ROW | Next row to process |
| BATCH | 200 | Rows per inner chunk |
| MAX_MS | 4.5 minutes | Soft deadline |
What this script does
Resumable processing with a property-backed cursor.
Prerequisites
WorkQueue sheet; Script Properties available.
- Status column writable
- Idempotent row work
- Trigger cadence shorter than backlog growth
Walkthrough
Fill 5k rows, run twice, watch CHECKPOINT_ROW move then clear.
Edge cases
Manual edits shifting rows break cursors — prefer stable IDs + status filters.
- deleteProperty on done
- BATCH should match setValues comfort
How to test
Set MAX_MS very low temporarily to force checkpoints.
Hardening for production
Store checkpoint per sheet name; use LockService for overlapping triggers.
Variations
DocumentProperties for multi-user; CacheService for short-lived cursors.
Full code: processWithCheckpoint()
Run processWithCheckpoint repeatedly (or on a trigger) until status is done.
/**
* Checkpoint progress in Script Properties to survive the 6-minute limit.
*/
const PROP_KEY = "CHECKPOINT_ROW";
const SOURCE = "WorkQueue";
const BATCH = 200;
const MAX_MS = 4.5 * 60 * 1000; // leave headroom under 6 minutes
function processWithCheckpoint() {
const props = PropertiesService.getScriptProperties();
const sheet = SpreadsheetApp.getActive().getSheetByName(SOURCE);
const startRow = Number(props.getProperty(PROP_KEY) || 2);
const lastRow = sheet.getLastRow();
const t0 = Date.now();
let row = startRow;
while (row <= lastRow) {
if (Date.now() - t0 > MAX_MS) {
props.setProperty(PROP_KEY, String(row));
Logger.log("Checkpoint saved at row %s", row);
return { status: "checkpoint", nextRow: row };
}
const end = Math.min(row + BATCH - 1, lastRow);
const values = sheet.getRange(row, 1, end, 3).getValues();
for (let i = 0; i < values.length; i++) {
// pretend work
values[i][2] = "DONE";
}
sheet.getRange(row, 1, end, 3).setValues(values);
row = end + 1;
}
props.deleteProperty(PROP_KEY);
Logger.log("Completed all rows");
return { status: "done" };
}- Line 4: Script property holding the resume row.
- Line 7: Soft stop before the hard 6-minute kill.
- Line 6: Chunk size for getValues/setValues.
- Line 19: Persists resume point when time runs low.
- Line 33: Clears checkpoint after full completion.
- Line 21: Return value for callers/continuations.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: checkpoints
- 1Work is idempotent if a row is retried
- 2CHECKPOINT_ROW starts at 2 or is absent
- 3MAX_MS leaves headroom under 6 minutes
- 4Avoid overlapping triggers without locks
- 5Monitor Logger for checkpoint rows
- 6Plan a manual reset property delete if stuck
Frequently asked questions
Hard kills can interrupt mid-setValues. Soft checkpoints exit cleanly.
Script Properties suit shared automation. User Properties are per account.
Row cursors skew — filter WHERE status<>DONE instead of row numbers.
JSON.stringify a cursor object into the property value.
Yes — prevent two triggers from processing the same window.
Some accounts allow longer runs; still checkpoint for safety.
Delete CHECKPOINT_ROW in Project Settings → Script properties.