“Did the nightly job run?” shouldn’t mean opening Executions. stampLastSuccessfulRun(target, status, opts) writes a spreadsheet-timezone timestamp into a fixed cell (A1 notation like Status!B2 or a named range) plus a status (OK / FAIL), last-run time, and optional message in the three cells to its right. A FAIL never overwrites the last OK time — so the cell always answers “when did this last actually work?”
When to use
- Nightly syncs, imports, and digests that ops people glance at in the sheet
- A small Status tab listing one row per job
- Anywhere you want a visible heartbeat without building a dashboard
How to use this snippet
/**
* Stamp a job's status into a fixed cell block, in the spreadsheet's timezone.
* Layout starting at target: [last OK time] [status] [last run time] [message]
* The last OK time only changes when status is OK.
*
* @param {string} target named range (e.g. 'NightlySyncStatus') or A1 like 'Status!B2'
* @param {string=} status 'OK' (default) or 'FAIL' (any string works)
* @param {{spreadsheet?: GoogleAppsScript.Spreadsheet.Spreadsheet,
* message?: string, format?: string}=} opts
* @return {string} the formatted timestamp that was written
*/
function stampLastSuccessfulRun(target, status, opts) {
opts = opts || {};
status = String(status || 'OK').toUpperCase();
var ss = opts.spreadsheet || SpreadsheetApp.getActive();
// Resolve the target: named range first, then A1 notation with a sheet name.
var range = ss.getRangeByName(target) || ss.getRange(target);
var cell = range.getCell(1, 1);
// Format "now" in the SPREADSHEET's timezone, not the script project's.
var tz = ss.getSpreadsheetTimeZone();
var now = Utilities.formatDate(new Date(), tz, opts.format || 'yyyy-MM-dd HH:mm:ss z');
var sheet = cell.getSheet();
var row = cell.getRow();
var col = cell.getColumn();
// Plain text format so Sheets doesn't re-parse the string into a date in another zone.
sheet.getRange(row, col, 1, 4).setNumberFormat('@');
// Only a real success moves the "last OK" time.
if (status === 'OK') {
cell.setValue(now);
}
// Status, when this run happened, and an optional short message.
var message = String(opts.message || '').slice(0, 500);
sheet.getRange(row, col + 1, 1, 3).setValues([[status, now, message]]);
return now;
}
Example
// Status tab: B2 = last OK | C2 = status | D2 = last run | E2 = message
function nightlySync() {
try {
var count = syncOrders_(); // your real work
stampLastSuccessfulRun('Status!B2', 'OK', { message: count + ' rows synced' });
} catch (err) {
stampLastSuccessfulRun('Status!B2', 'FAIL', { message: err.message });
throw err; // keep the failure visible in Executions / failure emails
}
}
Tips:
- Stamp after the real work (and after
SpreadsheetApp.flush()if the job writes a lot) — a stamp at the top of the function lies. - Use a named range (Data → Named ranges) so moving the Status block doesn’t break the script.
- Add conditional formatting on the status cell (red for
FAIL) and ops will notice without reading anything. - Running from a standalone script? Pass
{ spreadsheet: SpreadsheetApp.openById(id) }.
Tip: NitroGAS Co-Pilot can wrap an existing job function in the try/catch + stamp pattern if you paste the function and the status cell.
Happy Coding!
