“Did the sync run last night?” is a question that shouldn’t need you. But if the only answer lives in the Apps Script Executions page, every “is this data fresh?” turns into a Slack ping to the one person with editor access.
Put the answer in the sheet. One cell that says when the job last actually worked, and whether the latest run was OK or FAIL. Ops glance at it, you stay in flow.
TL;DR
- One status cell (or a small Status tab, one row per job) beats any dashboard you’ll never build
- Stamp in the spreadsheet’s timezone, as text, so nobody does timezone math
- Update “last OK” only after the real work succeeds — never at the top of the function
- On a throw, write
FAIL+ a short message, keep the old “last OK” time, then rethrow - Soft helper name:
stampLastSuccessfulRun(snippet + NitroGAS catalog)
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 the obvious version lies
The first version most of us write is:
function nightlySync() {
sheet.getRange('B1').setValue(new Date()); // "last run"
doTheWork_();
}
That stamp says the job started. If doTheWork_() dies at row 400, the cell still looks healthy. It’s also a raw Date, so it shows up in whatever format and zone the sheet decides.
A useful heartbeat answers one question: when did this last finish cleanly? Everything else is detail.
Where to put it
- Small job, one sheet: a status block near the top of the tab ops already use (e.g.
H1:K1). - Several jobs: a Status tab with one row per job — Job | Last OK | Status | Last run | Message.
- Name the cell (Data → Named ranges, e.g.
NightlySyncStatus) so moving it doesn’t break the script.
Keep it separate from your detailed log. If you already have a Log sheet you’ll actually read later, that’s where the story goes. The status cell is the headline.
What not to do
- Don’t stamp before the work — that’s a “started” time pretending to be a “success” time
- Don’t overwrite “last OK” on failure; a FAIL should make the old timestamp look stale, not vanish
- Don’t write raw
new Date()values — the script’s timezone and the sheet’s can differ - Don’t swallow the error after writing FAIL; rethrow so Executions (and your alerts) still see it
Workflow
1. Pick the status cell / Status row and name it
2. Wrap the job: try { work } then stamp OK
3. catch: stamp FAIL + short message, then rethrow
4. Add conditional formatting: FAIL = red, Last OK older than a day = amber
5. Tell ops where to look (and stop answering the Slack ping)
Stamp only after the work succeeds
function nightlySync() {
try {
var count = syncOrders_(); // the real work
SpreadsheetApp.flush(); // make sure writes landed before we claim success
stampLastSuccessfulRun('Status!B2', 'OK', { message: count + ' rows synced' });
} catch (err) {
stampLastSuccessfulRun('Status!B2', 'FAIL', { message: err.message });
throw err; // keep the failure visible
}
}
The stampLastSuccessfulRun snippet writes four cells starting at the target:
B2 Last OK | C2 Status | D2 Last run | E2 Message
2026-10-27 02:04:11 EDT | OK | 2026-10-27 02:04:11 EDT | 212 rows synced
On a failure, C2–E2 update and B2 keeps the last good time. At a glance: “FAIL, last worked yesterday at 2am.”
Spreadsheet timezone, stored as text
The helper formats with ss.getSpreadsheetTimeZone(), not the script project’s zone, and sets the cells to plain text so Sheets doesn’t reinterpret the string. Ops read the same wall-clock time they see everywhere else in that file.
(More on why the two zones drift in Timezone-safe dates in Sheets and Apps Script.)
var tz = ss.getSpreadsheetTimeZone();
var now = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd HH:mm:ss z');
Make stale obvious
A timestamp nobody compares to “now” is just decoration. Add a formula next to it:
F2: =IF(C2="FAIL","Check the job", IF(NOW()-DATEVALUE(LEFT(B2,10))>1.5,"Stale","Fresh"))
Then colour F2 with conditional formatting. It’s rough (date-level), but it turns “read a timestamp” into “see a red cell.”
Pair it with a real alert
The status cell is for people who look. For the nights nobody looks, email yourself when the nightly job fails — the catch block above is exactly where that email goes. Stamp first, email second, rethrow last.
Failure modes worth naming
- Timeout, not a throw — if Apps Script kills the run at the time limit, your catch never runs. The “last OK” going stale (and the stale flag) is what catches this.
- Someone types over the status cell — protect the Status tab, or at least that range.
- Standalone script —
getActive()is null; pass{ spreadsheet: SpreadsheetApp.openById(id) }. - Partial success — decide up front: is 390/400 rows OK? If not, throw so it stamps FAIL.
- Multiple jobs, one cell — give each job its own row. Shared cells hide which job broke.
Soft Co-Pilot note
NitroGAS keeps stampLastSuccessfulRun in the snippet catalog right inside the editor. Co-Pilot can wrap an existing job in the try / stamp / rethrow pattern if you paste the function and say where the status cell lives. Free core; Co-Pilot is a pass for when you want the draft.
Closing checklist
- One named status cell or Status row per job
- “Last OK” stamped only after the work (and
flush()) succeeds - FAIL + message on throw, old “last OK” kept, error rethrown
- Timestamps in the spreadsheet’s timezone, stored as text
- Stale / FAIL highlighted so ops don’t have to read it
Happy Coding!
