A Log sheet you’ll actually read later

TL;DR — Build an Apps Script Log sheet you will actually read later: timestamp, level, message, and JSON context so you can skip Executions archaeology entirely.

Logger.log is fine until you need yesterday's failure and the Executions page shrugged. A tiny Log sheet — timestamp, level, message, context JSON — turns "it broke for the client" into something you can filter, share, and audit. Here's the pattern we drop into bootstrapper projects so the trail survives the next person opening the file.

TL;DR

Use columns: timestamp | level | message | context (context = JSON string).
One logWrite_(level, message, contextObj) helper for everything.
Levels: DEBUG, INFO, WARN, ERROR (keep it boring).
Trim / rotate so the Log tab doesn't become a 100k-row museum.
Never put secrets in context — treat the sheet like email you might forward.

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.

The failure mode

Production sync fails Friday night. Monday you open Executions, squint at a red X, and discover:

The log line you needed scrolled off the truncated viewer
Two people ran overlapping jobs and stdout is interleaved mush
The client only has edit access to the spreadsheet — not your Apps Script project — so they can't see Executions at all
Someone cleared Cloud logs thinking it was "cleanup"

We've been that person reconstructing failures from half-written rows. A Log sheet isn't glamorous. It's a seatbelt.

Why a sheet instead of (only) Logger

Need	Logger / Executions	Log sheet
Client can see it	No	Yes (if they can open the file)
Filter by day / level	Painful	Filter views / QUERY
Survives "who ran it?"	Per-execution	Append-only history
Attach structured context	String mash	JSON column

Keep Logger.log for interactive debugging. Persist what you'll need later to the sheet.

Sheet shape

Create a tab named Log with this header row:

timestamp | level | message | context

Optional extras if you outgrow four columns: runId, user, function. Start with four — you'll actually fill them out.

Pasteable logger

var LOG_SHEET_NAME_ = 'Log';
var LOG_MAX_ROWS_ = 5000; // soft cap before trim

/**
 * Append a structured log row.
 * @param {string} level DEBUG|INFO|WARN|ERROR
 * @param {string} message Short human summary
 * @param {Object=} context Plain object (will be JSON.stringified)
 */
function logWrite_(level, message, context) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(LOG_SHEET_NAME_);
  if (!sheet) {
    sheet = ss.insertSheet(LOG_SHEET_NAME_);
    sheet.appendRow(['timestamp', 'level', 'message', 'context']);
    sheet.setFrozenRows(1);
  }

  var ctx = '';
  try {
    ctx = JSON.stringify(context || {});
  } catch (err) {
    ctx = JSON.stringify({ stringifyError: String(err), fallback: String(context) });
  }

  sheet.appendRow([
    new Date(),
    String(level || 'INFO').toUpperCase(),
    String(message || ''),
    ctx
  ]);

  // Also mirror to Logger for live runs
  Logger.log('[' + level + '] ' + message + ' ' + ctx);

  maybeTrimLog_(sheet);
}

function logInfo_(message, context) { logWrite_('INFO', message, context); }
function logWarn_(message, context) { logWrite_('WARN', message, context); }
function logError_(message, context) { logWrite_('ERROR', message, context); }
function logDebug_(message, context) { logWrite_('DEBUG', message, context); }

Trimming so the tab stays readable

function maybeTrimLog_(sheet) {
  var last = sheet.getLastRow();
  if (last <= LOG_MAX_ROWS_ + 1) return; // +1 header

  // Delete oldest data rows, keep header
  var extra = last - (LOG_MAX_ROWS_ + 1);
  sheet.deleteRows(2, extra);
}

Alternatives we like in bigger systems:

Archive to a Log_Archive tab monthly
Export to Drive as CSV, then clear
Watermark + batch delete (see the batch-writes guide if deletes get huge)

Don't let "we'll rotate later" become never.

Using it in real jobs

function runNightlySync() {
  var runId = Utilities.getUuid();
  logInfo_('sync.start', { runId: runId });

  try {
    var lock = LockService.getScriptLock();
    if (!lock.tryLock(15000)) {
      logWarn_('sync.lock_busy', { runId: runId });
      return;
    }
    try {
      var count = syncAllRows_();
      logInfo_('sync.done', { runId: runId, rows: count });
    } finally {
      lock.releaseLock();
    }
  } catch (err) {
    logError_('sync.failed', {
      runId: runId,
      message: String(err && err.message || err),
      stack: String(err && err.stack || '')
    });
    throw err;
  }
}

Notice the stable message keys (sync.start, sync.failed). Humans read them; later you can FILTER / QUERY without regex heroics.

Context JSON: what to put (and not)

Do put:

IDs (orderId, email, row number)
Counts, durations, watermark values
Vendor response codes (not full bodies if huge/sensitive)
runId so interleaved runs can be stitched

Don't put:

API tokens, webhook secrets, OAuth refresh tokens
Raw PII you wouldn't paste into Slack
Entire 50-page HTML error documents — truncate

function safeTruncate_(text, maxLen) {
  text = String(text || '');
  maxLen = maxLen || 2000;
  return text.length > maxLen ? text.substring(0, maxLen) + '…' : text;
}

Reading it like a grown-up

Filter views beat scrolling:

Level = ERROR for fire drills
Timestamp = last 24 hours for "what just happened?"
Message contains sync. for one job family

Or a QUERY tab:

=QUERY(Log!A:D, "select A,B,C,D where B='ERROR' order by A desc limit 50", 1)

Share that view with whoever triages — they shouldn't need the script editor.

Levels without religion

Keep four. If you invent TRACE, FATAL, OMG, nobody will agree what to filter.

Level	Use
DEBUG	Noisy detail; disable or trim in production if volume hurts
INFO	Start/finish milestones
WARN	Recoverable weirdness (lock busy, retry succeeded)
ERROR	Failed hard; needs a human

Pairing with webhooks & menus

Webhook doPost failures → logError_('webhook.failed', …) before returning JSON
Menu commands → logInfo_ on start/finish so stakeholders see a trail in-sheet
Time-driven triggers → always log start even if the job no-ops (proves the trigger fired)

Consistency matters more than cleverness. One helper, every entry point.

Soft Co-Pilot note

If you're cloning this into yet another client workbook, NitroGAS keeps the helper as a snippet — Co-Pilot helps rename levels or add a runId column when you need it. Free extension; Co-Pilot optional. The logger above is copy-paste complete either way.

Closing checklist

Log tab with timestamp / level / message / context headers
Single logWrite_ helper (plus thin level wrappers)
Jobs log start + success/failure with a runId
Soft trim or archive so the sheet stays openable
No secrets in context JSON
A filter or QUERY view for ERROR rows

Log like you'll get paged in six months. Because you will.

Happy Coding!