Claim the Next Queue Row

A sheet full of pending jobs will double-process the moment two triggers overlap. claimNextQueueRow finds the first matching status under a document lock, flips it to claimed, optionally stamps who/when, and returns the row as an object (or null if the queue is empty). Pair with a later done / error write — this helper is claim only.

What you'll need

A queue tab with a status column (exact header string)
withDocumentLock (or inline LockService.getDocumentLock())
Optional claimedBy / claimedAt headers if you want an audit trail

How to use this snippet

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;
  });
}

Example

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

  try {
    doWork_(job); // your real work
    sheet.getRange(job._row, headerIndex_(sheet, 'status') + 1).setValue('done');
  } catch (err) {
    sheet.getRange(job._row, headerIndex_(sheet, 'status') + 1).setValue('error');
    emailErrorAlert('queue job failed', err, { row: job._row, id: job.id });
    throw err;
  }
}

Tips:

Claim under a lock; do the slow API work outside the lock when you can.
Empty queue → null is success — don't throw.
Prefer header names over column letters so template clones keep working.
Complements getActiveRowAsObject (UI selection) and upsertRowByKey (keyed write).

Happy Coding!

Claim the Next Queue Row

What you'll need

How to use this snippet

Example

Tags

NitroGAS Chrome Extension

What you'll need

How to use this snippet

Example

Tags

NitroGAS Chrome Extension

Other Helpful Code Snippets: