Treat a sheet like a job queue (claim → work → done)

TL;DR — Treat a Google Sheet like a job queue in Apps Script: claim the next pending row under a lock, do the work, then mark done or error without double-processing.

Spreadsheets make terrible message buses — until you need a visible, editable backlog that non-engineers can triage. A Jobs tab with pending → claimed → done|error is the bootstrapper pattern that survives Friday deploys: one worker claims a row under a lock, does the slow work outside the lock, then writes a terminal status. Here's how to wire it without inventing a second database.

TL;DR

Columns: identity + payload + status (+ optional claimedBy / claimedAt / lastError).
Claim the first pending row under a document lock; return null when empty.
Do UrlFetch / Drive work after the claim; then set done or error.
Never "find pending and process" without flipping status first — that's how doubles happen.
Menu + time-driven trigger can share the same processOneQueueJob entry point.

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 use a sheet as a queue at all?

Because your operators already live there. They can:

Paste tomorrow's batch as rows
Re-open a failed job by setting status back to pending
Filter error without opening Cloud Logging

You're trading throughput for visibility. For dozens-to-low-hundreds of jobs per day, that's usually the right trade. Past a few thousand hot rows, graduate to a proper queue — until then, keep the sheet honest.

What columns does a job queue need?

Minimum that works:

Rules of thumb:

status values are a small enum: pending, claimed, done, error (lowercase, exact).
payload can be a JSON string or a few typed columns — pick one style and stick to it.
Don't delete done rows on day one; archive weekly if the tab gets heavy.

function ensureJobsSheet_(ss) {
  var sheet = ss.getSheetByName('Jobs');
  var headers = [
    'id', 'type', 'payload', 'status',
    'claimedBy', 'claimedAt', 'lastError', 'updatedAt'
  ];
  if (!sheet) {
    sheet = ss.insertSheet('Jobs');
    sheet.appendRow(headers);
    sheet.setFrozenRows(1);
    return sheet;
  }
  ensureHeaders(sheet, headers);
  return sheet;
}

How do you claim the next job safely?

Two workers reading "first pending" at the same millisecond will both process it unless the claim is atomic. Apps Script gives you LockService — use it for the read + status flip, not for the entire UrlFetch.

function claimNextQueueRow(sheet, statusHeader, claimedBy, options) {
  options = options || {};
  var pending = options.pendingStatus || 'pending';
  var claimed = options.claimedStatus || 'claimed';
  var headerRow = options.headerRow || 1;

  return withDocumentLock(function () {
    var lastCol = sheet.getLastColumn();
    var lastRow = sheet.getLastRow();
    if (lastRow <= headerRow) return null;

    var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
    var statusCol = headers.indexOf(statusHeader);
    if (statusCol === -1) {
      throw new Error('claimNextQueueRow: missing status header "' + statusHeader + '"');
    }

    var values = sheet.getRange(headerRow + 1, 1, lastRow - headerRow, lastCol).getValues();
    for (var i = 0; i < values.length; i++) {
      if (String(values[i][statusCol]).toLowerCase() !== String(pending).toLowerCase()) {
        continue;
      }
      var row = headerRow + 1 + i;
      sheet.getRange(row, statusCol + 1).setValue(claimed);
      if (options.claimedByHeader) {
        var byCol = headers.indexOf(options.claimedByHeader);
        if (byCol !== -1) {
          sheet.getRange(row, byCol + 1).setValue(
            claimedBy || Session.getActiveUser().getEmail() || 'unknown'
          );
        }
      }
      if (options.claimedAtHeader) {
        var atCol = headers.indexOf(options.claimedAtHeader);
        if (atCol !== -1) sheet.getRange(row, atCol + 1).setValue(new Date());
      }
      var obj = { _row: row };
      for (var c = 0; c < headers.length; c++) {
        var key = String(headers[c] == null ? '' : headers[c]).trim();
        if (!key) continue;
        obj[key] = values[i][c];
      }
      obj[statusHeader] = claimed;
      return obj;
    }
    return null;
  });
}

Empty queue → null. That's success for a polling trigger, not an error.

How should the worker loop look?

Claim → work → terminal status. Catch failures onto the row so operators can retry.

function processOneQueueJob() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ensureJobsSheet_(ss);
  var job = claimNextQueueRow(sheet, 'status', null, {
    claimedByHeader: 'claimedBy',
    claimedAtHeader: 'claimedAt'
  });
  if (!job) {
    ss.toast('Queue empty', 'Jobs', 3);
    return null;
  }

  try {
    var result = runJob_(job); // UrlFetch, Drive, sheet writes — outside the claim lock
    writeJobStatus_(sheet, job._row, 'done', '');
    return result;
  } catch (err) {
    writeJobStatus_(sheet, job._row, 'error', err.message || String(err));
    emailErrorAlert('queue job failed', err, { row: job._row, id: job.id, type: job.type });
    throw err;
  }
}

function writeJobStatus_(sheet, row, status, lastError) {
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  function col(name) { return headers.indexOf(name); }
  sheet.getRange(row, col('status') + 1).setValue(status);
  if (col('lastError') !== -1) sheet.getRange(row, col('lastError') + 1).setValue(lastError || '');
  if (col('updatedAt') !== -1) sheet.getRange(row, col('updatedAt') + 1).setValue(new Date());
}

How do you drain multiple jobs without timeouts?

Apps Script executions have a wall clock. Prefer one job per trigger tick for reliability, or a bounded loop:

function processQueueBatch(maxJobs) {
  maxJobs = maxJobs || 5;
  var done = 0;
  for (var i = 0; i < maxJobs; i++) {
    var result = processOneQueueJob();
    if (result === null && i === 0) break; // empty on first claim
    // If processOneQueueJob returns null only when empty *before* work,
    // track a boolean instead — adjust to your helper.
    done++;
  }
  return done;
}

For long batches, a 1–5 minute time-driven trigger that processes N jobs beats a single 5-minute mega-run that dies at 99%.

What about retries and poison messages?

Operator retry: set status back to pending, clear lastError.
Auto-retry: increment an attempts column; after 3 failures leave it error and alert.
Poison payload: don't infinite-loop — terminal error plus email is the grown-up move.

Stuck claimed rows (worker died mid-flight) need a sweeper: if claimedAt is older than 30 minutes and status is still claimed, either re-queue or mark error with reason stale-claim.

function requeueStaleClaims_(sheet, maxAgeMs) {
  maxAgeMs = maxAgeMs || 30 * 60 * 1000;
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var statusCol = headers.indexOf('status');
  var atCol = headers.indexOf('claimedAt');
  var now = Date.now();
  for (var r = 1; r < data.length; r++) {
    if (String(data[r][statusCol]).toLowerCase() !== 'claimed') continue;
    var at = data[r][atCol];
    if (!(at instanceof Date)) continue;
    if (now - at.getTime() < maxAgeMs) continue;
    sheet.getRange(r + 1, statusCol + 1).setValue('pending');
  }
}

Minimal test plan

Two overlapping processOneQueueJob calls claim different rows (or one gets null)
Failed work leaves error + lastError, not a silent claimed
Empty queue returns quietly
Manual pending reset re-runs the job once
Stale claimed sweeper only touches old rows

Soft Co-Pilot note

If the claim/lock boundary is fuzzy in your workbook, NitroGAS ships claimNextQueueRow and lock helpers as snippets — Co-Pilot can help adapt status enums to an existing Ops tab. Free extension; Co-Pilot optional.

Closing checklist

Jobs tab headers frozen; status enum documented for operators
Claim under document lock; work outside it
Terminal done / error writes include timestamps
Alert on failure (sheet log + email)
Stale-claim policy written down (even if manual at first)
Trigger schedule matches batch size (don't boil the ocean)

Happy Coding!