When a backlog cannot finish in one execution, process a fixed TAKE per run and leave the rest PENDING.
splitWorkAcrossRuns scans Jobs for PENDING/blank status, processes each, marks DONE with a timestamp, and stops at TAKE.
A five-minute trigger naturally finishes thousands of jobs over an hour without checkpoints by row number.
processJob_ is the seam where you call APIs or write child sheets.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Jobs | A jobId | Stable id |
| Jobs | B payload | Work payload |
| Jobs | C priority | Optional sort key |
| Jobs | D status | PENDING / DONE |
| Jobs | E finishedAt | Completion time |
| TAKE | 150 | Per-run limit |
What this script does
Status-column queue drain with a per-run cap.
Prerequisites
Jobs sheet seeded with PENDING rows.
- Idempotent processJob_
- Trigger every few minutes
- DONE never reprocessed
Walkthrough
Insert 400 PENDING rows, run thrice, confirm ~150 DONE each time until clear.
Edge cases
Scanning from the top each time is fine for modest sheets; for huge sheets query with filters or TextFinder.
- Empty status treated as PENDING
- Priority unused unless you sort first
How to test
Set TAKE=5 and verify more:true until drained.
Hardening for production
Sort PENDING by priority before processing; move failures to ERROR status.
Variations
Separate sheets per job type; or use Cloud Tasks for true queues.
Full code: splitWorkAcrossRuns()
Fill Jobs with PENDING rows and run on a schedule until more is false.
/**
* Split a large sheet job across multiple executions via status column.
*/
const QUEUE = "Jobs";
const TAKE = 150;
function splitWorkAcrossRuns() {
const sheet = SpreadsheetApp.getActive().getSheetByName(QUEUE);
const values = sheet.getDataRange().getValues();
let processed = 0;
for (let i = 1; i < values.length; i++) {
if (values[i][3] === "PENDING" || values[i][3] === "") {
processJob_(values[i]);
sheet.getRange(i + 1, 4).setValue("DONE");
sheet.getRange(i + 1, 5).setValue(new Date());
processed++;
if (processed >= TAKE) {
Logger.log("Processed %s jobs; remaining will wait for next run", TAKE);
return { processed: processed, more: true };
}
}
}
return { processed: processed, more: false };
}
function processJob_(row) {
// row: [jobId, payload, priority, status, finishedAt]
Logger.log("Processing %s payload=%s", row[0], row[1]);
}- Line 5: Max jobs per execution.
- Line 13: Status values eligible for work.
- Line 14: Replace with real work.
- Line 15: Marks progress so the next run skips the row.
- Line 28: Column E timestamp.
- Line 20: Signals callers that another run is needed.
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: split work
- 1Jobs headers include status and finishedAt
- 2processJob_ is idempotent
- 3TAKE sized for runtime + API quotas
- 4Trigger installed
- 5ERROR handling path defined
- 6Monitor backlog size over a day
Frequently asked questions
Status columns tolerate inserts/deletes better than raw row cursors.
Sort PENDING by column C before processing, or maintain separate high-priority sheets.
Catch in processJob_, set status ERROR and message in column F.
Yes — reduce TAKE when UrlFetch latency is high.
On very large sheets, read only status columns or use the Sheets API with filters.
If two triggers overlap, yes — lock around the scan/mark section.
Set status back to PENDING and clear finishedAt.