Fixed schedules waste idle runs. Continuation triggers schedule the next slice only when more work remains.
continuationWorker clears prior self-triggers, drains part of Jobs, and if more is true creates an after(DELAY_MS) trigger.
clearContinuationTriggers_ prevents stacking duplicate workers if a run overlaps.
Use DELAY_MS of 30–60 seconds to stay polite to quotas while finishing bursts quickly.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Item | Value | Purpose |
|---|---|---|
| Handler | continuationWorker | Trigger function name |
| DELAY_MS | 60000 | Delay before next slice |
| Jobs | status col D | PENDING/DONE queue |
| Trigger type | timeBased().after | One-shot continuation |
What this script does
Self-scheduling worker that stops creating triggers when the queue is drained.
Prerequisites
Jobs sheet; permission to manage triggers; authorized ScriptApp.
- Avoid infinite PENDING creation
- Monitor Triggers page
- Cap total continuations if needed
Walkthrough
Seed 500 PENDING rows, run continuationWorker once, watch new triggers appear until DONE.
Edge cases
Trigger clock skew can fire slightly late — acceptable for most queues.
- Delete triggers by handler name only
- after() minimum granularity is ~1 minute in practice
Safety rails
Always clear existing HANDLER triggers at start. Orphaned triggers are the main operational hazard of this pattern.
How to test
Log trigger UUIDs; ensure only one HANDLER trigger exists mid-run.
Hardening for production
Add a maxContinuations counter in Properties; alert if exceeded.
Variations
Clock triggers every N minutes as a heartbeat backup if continuations fail.
Full code: continuationWorker()
Authorize once, seed Jobs, run continuationWorker(). Inspect Triggers while it drains.
/**
* If work remains, create a one-shot time-based trigger to continue soon.
*/
const HANDLER = "continuationWorker";
const DELAY_MS = 60 * 1000;
function continuationWorker() {
clearContinuationTriggers_();
const result = splitWorkAcrossRuns(); // reuse queue drain from sibling pattern
if (result.more) {
ScriptApp.newTrigger(HANDLER)
.timeBased()
.after(DELAY_MS)
.create();
Logger.log("Scheduled continuation in %s ms", DELAY_MS);
} else {
Logger.log("Queue drained — no continuation");
}
}
function clearContinuationTriggers_() {
ScriptApp.getProjectTriggers().forEach(function (t) {
if (t.getHandlerFunction() === HANDLER) ScriptApp.deleteTrigger(t);
});
}
// Minimal local drain so this file is runnable standalone
function splitWorkAcrossRuns() {
const sheet = SpreadsheetApp.getActive().getSheetByName("Jobs");
const values = sheet.getDataRange().getValues();
let processed = 0;
const TAKE = 100;
for (let i = 1; i < values.length; i++) {
if (values[i][3] !== "PENDING") continue;
sheet.getRange(i + 1, 4).setValue("DONE");
processed++;
if (processed >= TAKE) return { more: true, processed: processed };
}
return { more: false, processed: processed };
}- Line 8: Removes prior HANDLER triggers to avoid piles.
- Line 9: Partial drain returning more:true/false.
- Line 11: Creates the next one-shot continuation.
- Line 4: Must match the function name string exactly.
- Line 39: Stops the chain when the queue is empty.
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: continuations
- 1Handler function name matches HANDLER constant
- 2Jobs queue uses PENDING/DONE
- 3Inspect Triggers after a test burst
- 4Plan a manual clearContinuationTriggers_ if stuck
- 5Quota: triggers/day limits exist — do not set DELAY_MS tiny forever
- 6Authorize ScriptApp trigger management
Frequently asked questions
Prevents duplicate workers if a continuation fires while another still schedules.
Apps Script documents minute-level scheduling; sub-minute values may round up.
Not via trigger args — store state in Properties/Sheets.
No continuation is scheduled — use a backup hourly heartbeat trigger.
Yes for bursty workloads; heartbeats are simpler for steady streams.
There is a per-project cap — clearing HANDLER triggers keeps you under it.
ScriptApp.newTrigger creates installable triggers; that is required here.