Our Retry Ledger Skipped the One Invoice That Failed
Idempotency keeps a retry from doing work twice. It also has to let the retry do the work once.
Our guide to Apps Script error handling and alerts is built around one idea: the expensive failure is the one that throws nothing. It gives you a wrapper that re-throws, a run log on a sheet, a deduplicated alert, a heartbeat with a watchdog, and a processOnce ledger so that a job which dies halfway does not send the same invoice twice.
I ran that code unchanged in Node against fakes of SpreadsheetApp, LockService, CacheService, PropertiesService and MailApp. Two scenarios, two findings:
- One failed send becomes an invoice that is never sent. A daily job sends the invoice for order 1001 and
MailAppthrows "Service invoked too many times for one day". The wrapper logs it and one alert goes out. The next three daily runs skip order 1001, because its key is already in the ledger asin_progress. Every run isok, the heartbeat isok, and the watchdog has nothing to report. Invoices sent for that order: 0. - A weekend outage sends 124 alert emails. One hourly job is broken from Friday 18:00 to Monday 08:00, and the watchdog runs on an hourly trigger.
alertOncemails on every run, because its dedup window (3,600 seconds) is the same as the job's interval. The watchdog mails on every run, because nothing remembers what it already reported. 48 of those emails land on Saturday, from a Gmail allowance of 100 recipients a day on a consumer account.
The layer the guide describes is the right one. The first finding matters most, because it is the failure the guide was written to prevent: silent, and found weeks later by a customer or an accountant.
Why the ledger skips the invoice
processOnce asks one question before it runs anything: is this key anywhere in column B? If it is, it returns { skipped: true }. Otherwise it appends the key as in_progress, runs fn(), and sets the row to done.
When fn() throws, nothing touches the row again. It stays in_progress, and on the next run the key is found and skipped.
The guide describes three states for that column: never attempted, finished, and “attempted but unknown”, and says a row stuck at in_progress for two hours "is a manual-review item". That is the right call for a run the platform killed mid-send, because nobody knows whether the email went out. A thrown exception is different. MailApp.sendEmail threw, so the invoice did not go. The code records both cases the same way, and no function reads in_progress rows back, so the manual review never happens.
Get Hayrullah Kar’s stories in your inbox
Join Medium for free to get updates from this writer.
People who build the same thing on other platforms make the same split. In a Make community thread about sending vouchers from Google Sheets, the original poster wrote: “We would prefer manual review over a possible duplicate whenever the delivery result is ambiguous.” The word that matters is ambiguous. A send that threw is not.
Step 1: a ledger that tells a failure from a crash
// Ledger tab: A at | B key | C state | D attempts | E error
const MAX_ATTEMPTS = 3;
const STUCK_MIN = 30;
function ledgerSheet_() {
return SpreadsheetApp.openById(LOG_SHEET_ID)
.getSheetByName('ledger');
}
function processOnce(key, fn) {
const claim = claimKey_(key);
if (!claim.run) return { skipped: true, state: claim.state };
try {
const result = fn();
markKey_(claim.row, 'done', claim.attempts, '');
return { skipped: false, result };
} catch (err) {
const state = claim.attempts >= MAX_ATTEMPTS
? 'gave_up' : 'failed';
markKey_(claim.row, state, claim.attempts,
String(err.message || err).slice(0, 200));
throw err;
}
}
// Holds the lock only while it reads and claims the key,
// not while fn() talks to Gmail or a payment API.
function claimKey_(key) {
const lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
const ledger = ledgerSheet_();
const last = ledger.getLastRow();
const rows = last
? ledger.getRange(1, 2, last, 3).getValues() : [];
const i = rows.findIndex(r => r[0] === key);
if (i === -1) {
ledger.appendRow([new Date(), key, 'in_progress', 1, '']);
return { run: true, row: ledger.getLastRow(), attempts: 1 };
}
const state = rows[i][1];
if (state !== 'failed') return { run: false, state };
const attempts = Number(rows[i][2]) + 1;
markKey_(i + 1, 'in_progress', attempts, '');
return { run: true, row: i + 1, attempts };
} finally {
lock.releaseLock();
}
}
function markKey_(row, state, attempts, error) {
const ledger = ledgerSheet_();
ledger.getRange(row, 1).setValue(new Date());
ledger.getRange(row, 3, 1, 3)
.setValues([[state, attempts, error]]);
}Three changes:
failedis its own state. A send that throws writesfailed, the error and the attempt number, then re-throws sorunGuardedstill logs and alerts. The next run'sclaimKey_seesfailedand runs it again. After the third failure the row becomesgave_upand is skipped from then on.in_progressnow means one thing: a run that started and never wrote an outcome. That is what the six-minute limit leaves behind, since, as the guide points out, a platform kill skipscatchandfinally. Those rows are not retried.- The lock covers the claim, not the send. The original holds the script lock for the whole of
fn(), so a slow send keeps every other job waiting on the same lock, includingflushLog, which gives up after 10 seconds. Here the lock is held only while one key is read and written.
Retrying a failed row is only safe if a send that threw really did not happen. For an HTTP call to a payment or messaging API, a timeout can throw after the provider accepted the request. Pair the retry with the provider's idempotency key, as the guide already recommends.
Step 2: a watchdog that reads the ledger and remembers what it said
const REMIND_EVERY_H = 24;
// Rows a person has to look at. 'failed' is left out:
// the next run retries it.
function ledgerProblems_() {
const ledger = ledgerSheet_();
const last = ledger.getLastRow();
if (!last) return [];
const now = Date.now();
return ledger.getRange(1, 1, last, 5).getValues()
.filter(r => r[2] === 'gave_up' ||
(r[2] === 'in_progress' &&
now - new Date(r[0]).getTime() > STUCK_MIN * 60000))
.map(r => ({
key: r[1] + ':' + r[2],
text: `${r[1]}: ${r[2]}, attempt ${r[3]} ${r[4]}`.trim()
}));
}
// Same checks as the original watchdog, but each problem has
// a stable key. The minutes go in the text, never in the key.
function staleJobs_() {
const props = PropertiesService.getScriptProperties();
const out = [];
Object.keys(EXPECTED_EVERY_MIN).forEach(job => {
const raw = props.getProperty('heartbeat:' + job);
if (!raw) {
out.push({ key: job + ':never',
text: `${job}: no successful run ever recorded` });
return;
}
const beat = JSON.parse(raw);
const ageMin = Math.round(
(Date.now() - new Date(beat.at).getTime()) / 60000);
const every = EXPECTED_EVERY_MIN[job];
if (ageMin > every * 2) {
out.push({ key: job + ':late',
text: `${job}: last run ${ageMin} min ago, ` +
`expected every ${every}` });
} else if (beat.status !== 'ok') {
out.push({ key: job + ':error',
text: `${job}: ran ${ageMin} min ago but errored` });
}
});
return out;
}
// Mails when the set of problems changes (including back
// to none), plus one reminder a day while it stays broken.
function watchdog() {
const props = PropertiesService.getScriptProperties();
const problems = staleJobs_().concat(ledgerProblems_());
const keys = problems.map(p => p.key).sort().join('|');
const lastKeys = props.getProperty('watchdog:keys') || '';
const sentAt = Number(props.getProperty('watchdog:sentAt'));
const due = keys !== '' &&
Date.now() - sentAt > REMIND_EVERY_H * 3600000;
if (keys === lastKeys && !due) return;
if (MailApp.getRemainingDailyQuota() < 5) {
console.error('watchdog mail suppressed: quota low');
return;
}
MailApp.sendEmail(ALERT_TO,
keys ? `Automation watchdog: ${problems.length} ` +
'problem(s) need attention'
: 'Automation watchdog: all clear',
keys ? problems.map(p => p.text).join('\n')
: 'Every watched job is back on schedule.');
props.setProperty('watchdog:keys', keys);
props.setProperty('watchdog:sentAt', String(Date.now()));
}ledgerProblems_turnsgave_uprows andin_progressrows older than 30 minutes into problems.failedrows are left out, because the next run retries them.- Each problem has a stable key, such as
invoice:1001:gave_upormagento_order_sync:late. The minutes since the last run go into the email text, never into the key. Otherwise "last run 125 min ago" and "last run 185 min ago" would be two different problems, and the dedup would do nothing. watchdogmails when the set of keys changes, once a day while it stays the same, and once with "all clear" when it empties. That last email also covers the guide's own point that an alert channel you never hear from looks the same as a broken one.- The quota check the guide put in
alertOnceis in the watchdog too.
alertOnce gets one change: cache.put(fingerprint, '1', 21600) instead of 3600. 21,600 seconds is the most CacheService accepts. With the 3,600-second window, an hourly job that keeps failing alerts every hour. With six hours it alerts four times a day, and the watchdog's daily reminder covers the rest.
The same scenarios, before and after
- Order 1001’s invoice throws once, and the daily job runs 3 more days. The guide’s code: 0 invoices sent, the row stays in_progress, every run reports ok. This version: 1 invoice sent on day 2, and the row is done after attempt 2.
- The invoice throws on every attempt. The guide’s code skips it after the first failure, and nothing reports it. This version tries 3 times, marks it gave_up, and the watchdog mails once, then daily.
- The run is killed mid-send. The guide’s code skips the row, and nothing reports it. This version does not retry it, and the watchdog reports it after 30 minutes.
- An hourly job is broken from Friday 18:00 to Monday 08:00, with the watchdog hourly. The guide’s code: 124 alert emails, 48 on Saturday. This version: 14, with 5 on Saturday.
- The problem is fixed. The guide’s code sends nothing. This version sends one “all clear” email.
Pitfalls
- Cache entries can leave early. Google’s reference for
putsays cached data "may be removed before this time". An evicted fingerprint means a duplicate alert, not a lost one, which is the safe direction. The watchdog keeps its memory in Script Properties, which do not expire. - A dedup key with a number in it does not dedup.
alertOncefingerprints the first 120 characters of the error message. If your message carries a row count, a cursor or a time ("fetched 37 orders but wrote none"), every run is a new fingerprint. Keep the variable parts out of the thrown message and put them in the log context. - Cache keys have a 250-character limit.
'alert:' + job + ':'plus the Base64 of a 120-character ASCII message is about 170 characters. Base64 of UTF-8 grows with non-ASCII text, so a long message in Turkish or Japanese can pass 250. I have not checked what Apps Script does then; ifcache.putthrows inside the wrapper'scatch, the original error is replaced by that one. - Writing
donecan fail after the send. IfmarkKey_throws after a successful send, the row staysin_progressand goes to a person after 30 minutes. That is the safe direction too: a missed tick, not a second invoice.
How it drifts once it is in use
gave_uprows pile up. Each one is a customer without an invoice. The watchdog reminds you daily. Someone has to fix the cause and set the row back tofailedto release it, or todoneif the invoice went out by hand.- A renamed job looks dead. Rename
invoice_mailerin the code but not inEXPECTED_EVERY_MIN, and the watchdog reports the old name as late from the next day on. Rename both in the same change. - The person who installed the triggers leaves. Installable triggers run as the account that created them. If the watchdog was installed by the same person as the jobs, it stops with them. Install it from a shared admin account. Our Workspace offboarding write-up is about the day that account gets suspended.
- The ledger grows.
claimKey_still reads the whole key column on every call. The guide's advice to shard the ledger by month applies unchanged.
What I tested, and what I did not
18 tests in Node against the same fakes, run under UTC, Europe/Istanbul, America/Los_Angeles and Asia/Tokyo. They cover a failed send retried once and marked done, the attempt count, gave_up after three failures, done and stuck in_progress rows never re-running, the lock being free while fn() runs, which rows the watchdog reports and after how long, no repeat email when only the minutes change, the daily reminder, the single "all clear", a new problem triggering a new email, the quota check, and the six-hour alert window. Then 15 deliberate breakages of the code, such as retrying in_progress rows, putting the minutes in the dedup key, or dropping the watchdog's memory. All 15 were caught.
The two findings at the top come from the guide’s code, unchanged, in the same fakes. The only line I left out is its one-line usage example, which refers to an order variable that does not exist when the file loads.
What I have not measured:
- A real Apps Script project. Everything above ran against fakes.
- Whether
MailApp.sendEmailcan throw after Gmail has accepted a message. - Google’s quota day. My fake resets the quota at UTC midnight.
- How long other jobs wait on the script lock in a real project while the original
processOnceholds it. - What happens to installable triggers when the account that created them is suspended.
- How often the watchdog should run. The guide does not say; I assumed hourly.
The takeaway
An idempotency ledger has two jobs: stop a retry from doing the work twice, and let it do the work once. Record a send that threw as failed, retry it a few times, and hand a person only the rows that nobody can know about.
The rest of the layer, from the wrapper to Cloud Logging costs, is in the original guide on the MageSheet blog.
