Your nightly job rewrites 400 rows by row number. Meanwhile someone edits a cell, your installable onEdit auto-sorts the sheet, and the job finishes writing to the wrong rows. beginScriptWrite / endScriptWrite / isScriptWriting set a short-lived “the script is writing” flag in CacheService. Your onEdit checks it and stands down until the job is done. If the job crashes before clearing the flag, it expires on its own (default 120 seconds), so onEdit never stays switched off.
Quick myth check
Apps Script’s own writes (setValue, setValues) don’t fire onEdit. Google skips triggers for script and API changes. The chase happens when people edit while your script is mid-way through a batch. Their edits fire onEdit, and its auto-sort, auto-timestamp or cascade logic collides with what your script is doing. This flag gives those handlers a reason to wait.
When to use
- Batch jobs that write by row number while an
onEditsorts, moves or deletes rows onEditcascades (fill this column when that one changes) that shouldn’t run against half-written data- Menu actions that restructure a tab (insert columns, rewrite blocks) while teammates are in the file
How to use this snippet
var SCRIPT_WRITE_KEY_ = 'scriptWriting';
/** Bound script: per-spreadsheet cache. Standalone: script-wide cache. */
function writeGuardCache_() {
return CacheService.getDocumentCache() || CacheService.getScriptCache();
}
/**
* Raise the "script is writing" flag.
* @param {number=} ttlSeconds how long before the flag expires on its own
* (default 120, max 21600). Set it a bit longer than the job usually takes.
*/
function beginScriptWrite(ttlSeconds) {
var ttl = Math.max(1, Math.min(ttlSeconds || 120, 21600));
writeGuardCache_().put(SCRIPT_WRITE_KEY_, '1', ttl);
}
/** Lower the flag. Always call it from a finally block. */
function endScriptWrite() {
writeGuardCache_().remove(SCRIPT_WRITE_KEY_);
}
/** True while a script job has the flag raised (and it hasn't expired). */
function isScriptWriting() {
return writeGuardCache_().get(SCRIPT_WRITE_KEY_) === '1';
}
Example
// The job: raise the flag, write, ALWAYS lower it
function rebuildStatusColumn() {
beginScriptWrite(300); // job usually takes ~3 minutes
try {
var sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
var rows = computeStatuses_(sheet); // your logic
sheet.getRange(2, 6, rows.length, 1).setValues(rows);
SpreadsheetApp.flush();
} finally {
endScriptWrite();
}
}
// Installable onEdit: stand down while the job runs
function onEditInstalled(e) {
if (isScriptWriting()) {
// Optional: tell the person why nothing happened
e.source.toast('A background update is running. Auto-sort will resume shortly.', 'Heads up', 5);
return;
}
autoSortOrders_(e); // the handler that would otherwise fight the job
}
Tips:
- Always
try / finally. The TTL is a safety net, not the plan. - Pick a TTL a bit longer than the job’s usual run. Too short and the flag expires mid-job; too long and a crashed job keeps
onEditquiet for that long. - The flag covers everyone editing that spreadsheet, not just one user. That’s the point, but tell your team why auto-sort sometimes pauses.
- CacheService is best-effort: Google can evict entries early. For jobs where
onEditmust never interfere, also take a document lock in both places. - This is a cooperation flag, not a permission. It only works if your
onEditchecksisScriptWriting().
Tip: NitroGAS Co-Pilot can add the begin / try / finally / end wrapper to an existing batch job and the early return to your onEdit, if you paste both.
Happy Coding!
