Logger.log disappears after executions. A durable ErrorLog sheet helps ops see failures from triggers overnight.
withErrorLog runs a function, catches errors, appends a structured row, then rethrows so callers still fail loudly.
Context JSON stores small debugging fields like sheet names without dumping entire payloads.
riskyImport shows a typical import guard that logs when Raw is missing or empty.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| ErrorLog | A at | Timestamp |
| ErrorLog | B function | Wrapper name |
| ErrorLog | C message | err.message |
| ErrorLog | D stack | Stack string |
| ErrorLog | E contextJson | Truncated JSON |
| ErrorLog | F user | Active/effective email |
| Raw | (source) | Sample dependency |
What this script does
Errors become rows; execution still throws so triggers mark as failed.
Prerequisites
Spreadsheet edit access; willingness to create ErrorLog automatically.
- Do not log secrets in context
- Truncate large stacks if needed
- Share ErrorLog with on-call readers
Walkthrough
Run riskyImport without Raw; confirm ErrorLog row; recreate Raw and succeed.
Edge cases
insertSheet on every first error can surprise locked spreadsheet structures — pre-create ErrorLog.
- Rethrow preserves trigger failure emails
- context sliced to 2000 chars
How to test
Throw new Error('boom') inside the wrapper and verify columns.
Hardening for production
Rotate ErrorLog monthly; alert when message rates spike.
Variations
Also Logger.log; or post to Chat/Slack webhook.
Full code: withErrorLog()
Wrap entry points with withErrorLog('name', fn, context). Inspect ErrorLog after failures.
/**
* Log errors to an ErrorLog sheet with stack, function name, and context.
*/
const ERROR_SHEET = "ErrorLog";
function withErrorLog(fnName, fn, context) {
try {
return fn();
} catch (err) {
logErrorToSheet_(fnName, err, context || {});
throw err;
}
}
function logErrorToSheet_(fnName, err, context) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(ERROR_SHEET) || ss.insertSheet(ERROR_SHEET);
if (sheet.getLastRow() === 0) {
sheet.appendRow(["at", "function", "message", "stack", "contextJson", "user"]);
}
sheet.appendRow([
new Date(),
fnName,
String(err && err.message ? err.message : err),
String(err && err.stack ? err.stack : ""),
JSON.stringify(context).slice(0, 2000),
Session.getActiveUser().getEmail() || Session.getEffectiveUser().getEmail(),
]);
}
function riskyImport() {
return withErrorLog("riskyImport", function () {
const raw = SpreadsheetApp.getActive().getSheetByName("Raw");
if (!raw) throw new Error("Raw sheet missing");
const n = raw.getLastRow();
if (n < 2) throw new Error("Raw has no data");
// ... transform ...
return n - 1;
}, { sheet: "Raw" });
}- Line 6: Try/catch wrapper that logs then rethrows.
- Line 4: Destination tab for durable errors.
- Line 25: Captures stack when available.
- Line 26: Stores caller-provided debug fields.
- Line 27: Records who hit the error when available.
- Line 31: Example usage around an import path.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: error logging
- 1Decide whether ErrorLog is auto-created or pre-provisioned
- 2Never put tokens in context objects
- 3Wrap trigger entry functions
- 4Confirm rethrow behavior matches your alerting
- 5Limit who can edit ErrorLog
- 6Test a deliberate failure once
Frequently asked questions
So time-driven triggers still show as failed and any outer monitors fire.
Yes — remove throw err for soft-fail loops, but document that failures are swallowed.
Some thrown strings are not Error objects — wrap with new Error(msg).
Burst failures can spam rows — dedupe by message fingerprint in CacheService.
Yes. Prefer effective user when active user is blank.
Archive by copying values to ErrorLog_Archive then clear, or filter by date.
Executions page shows temporary logs; sheets survive longer for ops reviews.