Pitfalls of a dead-man's switch (heartbeat monitor) that keeps job heartbeats in Google Sheets, and how to fix them

Setup

• Scheduled jobs (on a PC or in the cloud) send {job, slot, status: started|ok|failed, summary} to a GAS Web App.
• GAS upserts one row per job + slot in a sheet (columns: job, slot, at, status, summary, checks).
• A GAS time trigger checks the expected table [{job, slot, due}] against the sheet. Anything missing past due, left as started, or failed is posted as an alert.

Pitfall 1: the sheet turns slot 0930 into the number 930

• The sender writes '0930', but setValues / appendRow stores it as the number 930. Reading it back gives '930'.
• The expected table says '0930', so the lookup fails and the job is reported as "missing" even though it sent its heartbeat.
• Fix: normalize the key on both sides (sender and evaluator) before comparing. Do the same normalization on reads and on the existing-row search inside upsert.

function normalizeSlot(slot) {
  if (slot === null || slot === undefined) return slot;
  var s = String(slot).trim();
  return /^\d{1,4}$/.test(s) ? ('0000' + s).slice(-4) : s;   // 930 -> "0930", "daily" stays as is
}

• An alternative is to set the column to plain text format (@). That alone does not fix rows already stored as numbers, so you still need normalization.

Pitfall 2: duplicate rows from before the fix hide the latest status

• While the keys did not match, upsert could not find the existing row and kept appending. The sheet now has several rows for the same job::slot.
• If the evaluator does "a later row overwrites an earlier one", an old row further down the sheet hides the newest ok. The job looks failed even after the fix.
• Fix: (a) for duplicate keys, the evaluator keeps the row with the latest at. (b) after each write, upsert deletes the other rows with the same job::slot. Delete from the bottom up so row indices do not shift.

Pitfall 3: mixing "did the monitor run?" with "is the business healthy?"

• If a heartbeat's status also counts the following as failure, the monitor stays red every day and people stop reading it:
  • Business validity warnings (for example, "no new orders for 14 days"). These are a state to report, not a monitor failure.
  • Expected "could not confirm" by design (for example, a value is deliberately not used because it cannot be verified, or a channel has no target configured).
  • Failures of other jobs (follow-on review jobs or probes). They have their own heartbeat slots.
• Fix: record findings in three separate lists:
  • issues: the monitor could not collect its own evidence. Only this sets the heartbeat to failed.
  • unconfirmed: not used, by design. Still included in the notification.
  • validityWarnings: the business state. Show it as a separate notification section, and do not change status.
• Write the same split into the status file the watchdog reads (such as ok). If the status file and the heartbeat disagree, nobody can tell which part is broken.

Pitfall 4: setting retries to zero

• A GAS Web App occasionally returns a one-off non-JSON response or Unauthorized. With zero retries on read-only calls, one blip causes a whole-run failure (every item becomes unknown).
• Fix: allow 2–3 retries on read-only calls with backoff. Keep writes at zero retries (idempotency is not guaranteed).

Pitfall 5: parsing LLM output fenced in ```json

• With stdout.replace(/^```json\s*|\s*```$/g,'').trim(), a trailing newline after the closing fence stops $ from matching, and JSON.parse fails every time.
• Providers that return no fence get through, which hides the bug.
• Fix: run trim() first, then strip the fences.

Pitfall 6: the scheduled task runs a different checkout

• If a nightly job is set up to fetch and then checkout --detach origin/main in a pinned copy, heartbeat code added to the working tree has no effect at night until it lands on main.
• Fix: add a wrapper that sends heartbeats before and after the run, and point the task at the wrapper. The computation itself stays the pinned version.

Verification checklist

1. Have the sender send 0930. Confirm that the read-back slot is 0930 and that it is not reported as missing.
2. Add a test with duplicate keys in both orders, new→old and old→new, and confirm the newest row wins.
3. Run once with only a business validity warning present. The heartbeat must be ok and the warning must still be in the notification.
4. Check that the status file and the heartbeat agree.