A sheet can act as a durable queue when you lack Cloud Tasks. The hard part is concurrent claim safety.
enqueueTask appends PENDING rows with UUID, type, and JSON payload.
claimAndRunOne locks the document, marks a row RUNNING, releases the lock, then executes handleTask_ so long work does not hold the lock.
Failures set ERROR with the message in column F; successes set DONE with finished time.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Queue | A id | UUID |
| Queue | B type | Handler key |
| Queue | C payload | JSON string |
| Queue | D status | PENDING/RUNNING/DONE/ERROR |
| Queue | E startedAt | Claim time |
| Queue | F finishedOrError | Done time or error text |
What this script does
Producer/consumer queue on Sheets with lock-protected claims.
Prerequisites
Queue sheet; triggers calling claimAndRunOne.
- Document lock available
- handleTask_ routes by type
- JSON payloads small
Walkthrough
enqueueTask('ping', {n:1}); run claimAndRunOne; confirm DONE.
Edge cases
If the script dies after RUNNING mark, add a reaper that resets stale RUNNING older than N minutes to PENDING.
- Release lock before long UrlFetch
- tryLock timeout 10s
How to test
Fire two claimAndRunOne nearly together; only one should claim each row.
Hardening for production
Add attempts column; dead-letter after 5 ERROR retries.
Variations
User lock for per-user queues; Script lock for project-global.
Full code: enqueueTask + claimAndRunOne()
Call enqueueTask from producers; schedule claimAndRunOne on a short cadence.
/**
* Sheet-as-queue: claim the next PENDING row with LockService.
*/
const Q = "Queue";
function enqueueTask(type, payload) {
const sheet = SpreadsheetApp.getActive().getSheetByName(Q);
const id = Utilities.getUuid();
sheet.appendRow([id, type, JSON.stringify(payload || {}), "PENDING", "", ""]);
return id;
}
function claimAndRunOne() {
const lock = LockService.getDocumentLock();
if (!lock.tryLock(10000)) throw new Error("Could not obtain lock");
try {
const sheet = SpreadsheetApp.getActive().getSheetByName(Q);
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][3] !== "PENDING") continue;
sheet.getRange(i + 1, 4, i + 1, 5).setValues([["RUNNING", new Date()]]);
const id = data[i][0];
const type = data[i][1];
const payload = JSON.parse(data[i][2] || "{}");
lock.releaseLock();
try {
handleTask_(type, payload);
sheet.getRange(i + 1, 4, i + 1, 6).setValues([["DONE", data[i][4] || new Date(), new Date()]]);
} catch (err) {
sheet.getRange(i + 1, 4).setValue("ERROR");
sheet.getRange(i + 1, 6).setValue(String(err.message || err));
}
return id;
}
return null;
} finally {
if (lock.hasLock()) lock.releaseLock();
}
}
function handleTask_(type, payload) {
Logger.log("handle %s %s", type, JSON.stringify(payload));
}- Line 6: Producer API appending PENDING rows.
- Line 14: Serializes claim selection across concurrent runs.
- Line 21: Claim marker before doing work.
- Line 25: Released before handleTask_ so work can take time.
- Line 27: Dispatch by type.
- Line 30: Failure path stores message in column F.
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: sheet queue
- 1Queue headers match A–F
- 2Producers only append — never edit status casually
- 3Workers use claimAndRunOne
- 4Plan stale RUNNING recovery
- 5Keep payloads under a few KB
- 6Monitor ERROR rows daily
Frequently asked questions
Document lock coordinates all scripts on the spreadsheet. Script lock is project-local.
Holding locks during UrlFetch blocks other claims and risks timeouts.
At-least-once. Make handleTask_ idempotent.
Yes — mark several PENDING rows RUNNING under one lock, then process after release.
Scan for high-priority types first, or maintain separate queue sheets.
Store a sheet range A1 or Drive file id instead of inline JSON.
Fine for low volume. Move to a real queue when QPS grows.