Some rows just won't go through: a bad email, an API that keeps saying 422, a customer ID that doesn't exist. Retrying them forever clogs the job. appendDeadLetterRow(sheetOrSs, rowObj, err, opts) parks the row on a DeadLetter tab with _failedAt, _attempts and _error columns, creating the tab and headers the first time. Pass opts.key and it updates the existing dead-letter row for that key (bumping _attempts) instead of adding a duplicate.
When to use
- Queue-style jobs where a row has failed too many times to keep retrying
- Webhook receivers that can't process a payload but mustn't lose it
- Any nightly job where “skip and log it somewhere visible” beats “throw and stop”
How to use this snippet
/**
* Park a failed row on a DeadLetter tab.
*
* @param {Sheet|Spreadsheet} sheetOrSs any sheet in the spreadsheet, or the spreadsheet
* @param {Object} rowObj the row as { header: value }
* @param {Error|string} err what went wrong
* @param {Object=} opts { key: 'Id' (upsert by this field), tabName: 'DeadLetter' }
* @return {{row: number, attempts: number}}
*/
function appendDeadLetterRow(sheetOrSs, rowObj, err, opts) {
opts = opts || {};
var ss = sheetOrSs.getSheetByName ? sheetOrSs : sheetOrSs.getParent();
var name = opts.tabName || 'DeadLetter';
var sh = ss.getSheetByName(name) || ss.insertSheet(name);
var meta = ['_failedAt', '_attempts', '_error'];
// Headers: first use, or new fields since last time
var headers = sh.getLastColumn() ? sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0] : [];
Object.keys(rowObj).concat(meta).forEach(function (h) {
if (headers.indexOf(h) === -1) headers.push(h);
});
sh.getRange(1, 1, 1, headers.length).setValues([headers]);
var message = String(err && err.message ? err.message : err).slice(0, 500);
var target = -1, attempts = 1;
if (opts.key && sh.getLastRow() > 1) {
var keyCol = headers.indexOf(opts.key);
var data = sh.getRange(2, 1, sh.getLastRow() - 1, headers.length).getValues();
for (var i = 0; i < data.length; i++) {
if (String(data[i][keyCol]) === String(rowObj[opts.key])) {
target = i + 2;
attempts = Number(data[i][headers.indexOf('_attempts')] || 0) + 1;
break;
}
}
}
var out = headers.map(function (h) {
if (h === '_failedAt') return new Date();
if (h === '_attempts') return attempts;
if (h === '_error') return message;
return rowObj[h] === undefined ? '' : rowObj[h];
});
if (target === -1) target = sh.getLastRow() + 1;
sh.getRange(target, 1, 1, out.length).setValues([out]);
return { row: target, attempts: attempts };
}
Example
function processQueue() {
var sh = SpreadsheetApp.getActive().getSheetByName('Queue');
var job = claimNextQueueRow(sh); // your claim helper
if (!job) return;
try {
pushToCrm_(job.data);
} catch (err) {
appendDeadLetterRow(sh, job.data, err, { key: 'Id' });
}
}
Tips:
- Keep the original columns in the same names so a retry can copy rows straight back to the queue.
_erroris trimmed to 500 characters so a huge API response doesn't swamp the cell.- Run it under a lock (
withScriptLock) if more than one trigger can fail at the same time.
Tip: NitroGAS Co-Pilot can wrap your job's failure path with appendDeadLetterRow and add a “Retry dead letters” menu. Paste the job function.
Happy Coding!
