Split long jobs with continuation triggers
Split long Apps Script sheet jobs across bounded slices with a ScriptProperties cursor, a script lock, and one-off ClockTriggerBuilder.after triggers.
Audit a sheet queue in bounded row and time slices, checkpoint progress in ScriptProperties under a script lock, and replace prior one-off clock triggers before scheduling the next slice.
Process a six-row AuditQueue table in bounded slices, save a validated progress cursor in ScriptProperties under a script lock, and schedule one-off clock triggers until every Pending row is Audited. You need a new disposable spreadsheet, the batch read and write pattern from Read and write Sheets in batches, the versioned state pattern from State across runs with PropertiesService, and the trigger ownership rules from Install and remove time-driven triggers.
Turn one long loop into a bounded state machine
Batching getValues() and setValues() removes per-row spreadsheet round-trips, so start there when a sheet loop takes minutes. Even with batched sheet access, a script that audits thousands of records, generates Drive documents, or calls slow external APIs can exceed the 6-minute single-execution limit documented in Quotas for Google services. Past 6 minutes, Apps Script terminates the execution with Exceeded maximum execution time (quota exception messages). Writes that already completed remain on the sheet, leaving the job partway finished with no automatic retry.
A continuation trigger breaks that unbounded loop into short executions coordinated by a saved cursor. Instead of trying to finish the whole queue in one run, each execution transitions the job through an explicit state machine:
idle:AUDIT_CURSOR_V1is absent fromScriptPropertiesand no continuation trigger is scheduled.running_chunk: one execution holds the script lock, readsAuditQueue, and processes a bounded slice ofPendingrows in memory.scheduled_continuation: unfinished rows remain, so the execution flushes its batch write, saves the next row index inAUDIT_CURSOR_V1, deletes prior continuation triggers, and creates one new clock trigger.completedorcancelled: noPendingrows remain (or the owner cancels the job), so the script deletes its continuation triggers and removesAUDIT_CURSOR_V1, returning toidle.
Four platform rules in the Apps Script quotas guide and ClockTriggerBuilder.after reference shape every part of this design:
| Platform limit or behavior | Documented value | Design rule in Code.gs |
|---|---|---|
| Single execution runtime | 6 minutes per execution | Stop starting new items once elapsed time reaches SOFT_TIME_BUDGET_MS (270000 ms, or 4.5 minutes), leaving 90 seconds for setValues(), flush(), property writes, and trigger replacement. |
| Project trigger count | 20 triggers per user per script | Delete prior resumeChunkedAudit clock triggers before calling create() so disabled one-off triggers never fill the 20-trigger cap. |
| Daily trigger runtime | 90 minutes per day (consumer) or 6 hours per day (Google Workspace) across all triggers owned by that user | Enforce MAX_CONTINUATION_SLICES (25) so a stalled job stops rescheduling instead of burning the owner's daily runtime quota. |
after(durationMilliseconds) window | Minimum delay in milliseconds before the one-off clock trigger runs | Pass 60 * 1000 to request at least a 1-minute gap, and store all state in ScriptProperties rather than relying on exact firing times. |
PropertiesService stores string values and caps each key at 9 KB (Properties service guide, Quotas for Google services). Keep row data in the spreadsheet and store only small cursor metadata (version, state, nextRowIndex, slicesCompleted, auditedTotal, and updatedAtIso) under AUDIT_CURSOR_V1.
Set up the bound spreadsheet project
Create a new disposable spreadsheet so this project's ScriptProperties keys and installable triggers stay isolated from earlier chapters:
Create a new Google Sheet and rename its first tab
AuditQueue.Copy
sample.csvbelow into cellA1, select column A, and choose Data > Split text to columns with Comma as the separator.Confirm that the table occupies
A1:E7(one header row plus six queue rows, withAUD-103alreadyAuditedat2026-10-01T08:30:00Z).Open Extensions > Apps Script and replace
Code.gswith the complete file below.Open Project Settings, check Show "appsscript.json" manifest file in editor, replace
appsscript.jsonwith the manifest below, and save both files.
Item ID,Department,Owner,Audit Status,Checked At
AUD-101,Finance,morgan.lee@example.com,Pending,
AUD-102,Facilities,avery.quinn@example.com,Pending,
AUD-103,Legal,sam.chen@example.com,Audited,2026-10-01T08:30:00Z
AUD-104,Engineering,jordan.diaz@example.com,Pending,
AUD-105,Operations,riley.patel@example.com,Pending,
AUD-106,Security,casey.kim@example.com,Pending,
const MAX_ITEMS_PER_SLICE = 2; // Maximum Pending rows to audit in one execution.
const SOFT_TIME_BUDGET_MS = 270000; // Stop starting new rows at 4.5 minutes to leave headroom before the 6-minute limit.
const CONTINUATION_DELAY_MS = 60 * 1000; // Minimum delay in milliseconds requested for ClockTriggerBuilder.after().
const MAX_CONTINUATION_SLICES = 25; // Maximum slices allowed for one job before stopping automatic rescheduling.
const AUDIT_SHEET_NAME_ = 'AuditQueue';
const AUDIT_CURSOR_KEY_ = 'AUDIT_CURSOR_V1';
const CONTINUATION_HANDLER_ = 'resumeChunkedAudit';
const AUDIT_MAX_ROWS_ = 2000;
const LOCK_WAIT_MS_ = 5000;
const MAX_CURSOR_CHARS_ = 2048;
const AUDIT_HEADERS_ = ['Item ID', 'Department', 'Owner', 'Audit Status', 'Checked At'];
const AUDIT_STATUSES_ = ['Pending', 'Audited', 'Hold'];
/**
* @typedef {'running_chunk' | 'scheduled_continuation' | 'paused_slice_cap'} AuditCursorState_
*/
/**
* @typedef {{
* version: number,
* state: AuditCursorState_,
* nextRowIndex: number,
* slicesCompleted: number,
* auditedTotal: number,
* updatedAtIso: string
* }} AuditCursor_
*/
/**
* @typedef {{
* sheet: SpreadsheetApp.Sheet,
* rows: unknown[][]
* }} AuditTable_
*/
/**
* Starts a new chunked audit of AuditQueue or resumes an in-progress cursor.
*
* @returns {void}
*/
function startChunkedAudit() {
runAuditSlice_(true);
}
/**
* Continues an active chunked audit from a one-off clock trigger or a manual test run.
*
* @returns {void}
*/
function resumeChunkedAudit() {
runAuditSlice_(false);
}
/**
* Logs the saved AUDIT_CURSOR_V1 record and the current user's continuation trigger count.
* Makes no changes to spreadsheet cells, ScriptProperties, or triggers.
*
* @returns {void}
*/
function inspectChunkedAuditState() {
const rawCursor = PropertiesService.getScriptProperties().getProperty(AUDIT_CURSOR_KEY_);
const triggers = listContinuationTriggers_();
if (rawCursor === null) {
Logger.log('Audit state: idle (AUDIT_CURSOR_V1 is not set).');
Logger.log('Active continuation triggers for current user: ' + triggers.length + '.');
return;
}
const cursor = decodeAuditCursor_(rawCursor);
Logger.log(
'Audit state: ' +
cursor.state +
' (next spreadsheet row ' +
(cursor.nextRowIndex + 2) +
', slicesCompleted=' +
cursor.slicesCompleted +
', auditedTotal=' +
cursor.auditedTotal +
').'
);
Logger.log('Cursor JSON: ' + JSON.stringify(cursor));
Logger.log('Active continuation triggers for current user: ' + triggers.length + '.');
}
/**
* Deletes the current user's continuation clock triggers and clears AUDIT_CURSOR_V1.
* Leaves already-audited rows in AuditQueue intact.
*
* @returns {void}
*/
function cancelChunkedAudit() {
const lock = LockService.getScriptLock();
if (!lock.tryLock(LOCK_WAIT_MS_)) {
throw new Error(
'Could not acquire the script lock to cancel the audit. Wait for the active slice to finish and try again.'
);
}
try {
const store = PropertiesService.getScriptProperties();
const hadCursor = store.getProperty(AUDIT_CURSOR_KEY_) !== null;
const removedTriggers = deleteContinuationTriggers_();
store.deleteProperty(AUDIT_CURSOR_KEY_);
Logger.log(
'Audit state: cancelled. Removed ' +
removedTriggers +
' continuation trigger(s); cleared AUDIT_CURSOR_V1=' +
String(hadCursor) +
'.'
);
} finally {
lock.releaseLock();
}
}
/**
* Runs one bounded slice under the script lock, writes updates in one batch,
* and replaces or clears continuation triggers.
*
* @param {boolean} allowInitialize
* @returns {void}
*/
function runAuditSlice_(allowInitialize) {
validateSliceConfig_();
const startedAtMs = Date.now();
const lock = LockService.getScriptLock();
if (!lock.tryLock(LOCK_WAIT_MS_)) {
Logger.log('Skipped audit slice because another execution holds the script lock.');
return;
}
try {
const store = PropertiesService.getScriptProperties();
const rawCursor = store.getProperty(AUDIT_CURSOR_KEY_);
if (rawCursor === null && !allowInitialize) {
const removedTriggers = deleteContinuationTriggers_();
Logger.log(
'No active audit cursor in ScriptProperties (state=idle). Removed ' +
removedTriggers +
' continuation trigger(s).'
);
return;
}
const sliceTimestampIso = formatUtcIsoSeconds_(startedAtMs);
/** @type {AuditCursor_} */
const cursor =
rawCursor === null
? {
version: 1,
state: 'running_chunk',
nextRowIndex: 0,
slicesCompleted: 0,
auditedTotal: 0,
updatedAtIso: sliceTimestampIso
}
: decodeAuditCursor_(rawCursor);
if (cursor.slicesCompleted >= MAX_CONTINUATION_SLICES) {
deleteContinuationTriggers_();
throw new Error(
'Audit cursor already reached MAX_CONTINUATION_SLICES (' +
MAX_CONTINUATION_SLICES +
'). Inspect AuditQueue with inspectChunkedAuditState, raise MAX_CONTINUATION_SLICES, or run cancelChunkedAudit.'
);
}
const table = readAuditTable_(getAuditSheet_());
if (table.rows.length === 0) {
const removedTriggers = deleteContinuationTriggers_();
store.deleteProperty(AUDIT_CURSOR_KEY_);
Logger.log(
'AuditQueue has no data rows. Cleared cursor and removed ' +
removedTriggers +
' continuation trigger(s).'
);
return;
}
if (cursor.nextRowIndex > table.rows.length) {
throw new Error(
'Saved cursor points past the end of AuditQueue (nextRowIndex=' +
cursor.nextRowIndex +
', rows=' +
table.rows.length +
'). Inspect the sheet or run cancelChunkedAudit before restarting.'
);
}
const outputRows = table.rows.map(function (row) {
return [String(row[3]), row[4]];
});
let rowIndex = cursor.nextRowIndex;
let auditedThisSlice = 0;
let stopReason = 'completed';
while (rowIndex < outputRows.length) {
if (outputRows[rowIndex][0] !== 'Pending') {
rowIndex += 1;
continue;
}
if (auditedThisSlice >= MAX_ITEMS_PER_SLICE) {
stopReason = 'max_items_per_slice';
break;
}
if (Date.now() - startedAtMs >= SOFT_TIME_BUDGET_MS) {
stopReason = 'soft_time_budget';
break;
}
outputRows[rowIndex] = ['Audited', sliceTimestampIso];
auditedThisSlice += 1;
rowIndex += 1;
}
if (auditedThisSlice > 0) {
table.sheet.getRange(2, 4, outputRows.length, 2).setValues(outputRows);
SpreadsheetApp.flush();
}
const nextSlicesCompleted = cursor.slicesCompleted + 1;
const nextAuditedTotal = cursor.auditedTotal + auditedThisSlice;
if (rowIndex >= outputRows.length) {
const removedTriggers = deleteContinuationTriggers_();
store.deleteProperty(AUDIT_CURSOR_KEY_);
Logger.log(
'Audit state: completed in ' +
nextSlicesCompleted +
' slice(s). Audited ' +
auditedThisSlice +
' item(s) in final slice (' +
nextAuditedTotal +
' total). Removed ' +
removedTriggers +
' continuation trigger(s) and cleared AUDIT_CURSOR_V1.'
);
return;
}
if (nextSlicesCompleted >= MAX_CONTINUATION_SLICES) {
const removedTriggers = deleteContinuationTriggers_();
/** @type {AuditCursor_} */
const cappedCursor = {
version: 1,
state: 'paused_slice_cap',
nextRowIndex: rowIndex,
slicesCompleted: nextSlicesCompleted,
auditedTotal: nextAuditedTotal,
updatedAtIso: sliceTimestampIso
};
store.setProperty(AUDIT_CURSOR_KEY_, encodeAuditCursor_(cappedCursor));
throw new Error(
'Stopped after ' +
nextSlicesCompleted +
' slice(s) at MAX_CONTINUATION_SLICES=' +
MAX_CONTINUATION_SLICES +
' with Pending rows remaining at spreadsheet row ' +
(rowIndex + 2) +
' (removed ' +
removedTriggers +
' trigger(s)). Inspect AuditQueue before resuming or canceling.'
);
}
/** @type {AuditCursor_} */
const nextCursor = {
version: 1,
state: 'scheduled_continuation',
nextRowIndex: rowIndex,
slicesCompleted: nextSlicesCompleted,
auditedTotal: nextAuditedTotal,
updatedAtIso: sliceTimestampIso
};
store.setProperty(AUDIT_CURSOR_KEY_, encodeAuditCursor_(nextCursor));
const removedTriggers = deleteContinuationTriggers_();
const nextTrigger = ScriptApp.newTrigger(CONTINUATION_HANDLER_)
.timeBased()
.after(CONTINUATION_DELAY_MS)
.create();
Logger.log(
'Audit state: scheduled_continuation after slice ' +
nextSlicesCompleted +
' (' +
stopReason +
'). Audited ' +
auditedThisSlice +
' item(s) (' +
nextAuditedTotal +
' total), next spreadsheet row ' +
(rowIndex + 2) +
', removed ' +
removedTriggers +
' prior trigger(s), scheduled trigger ' +
nextTrigger.getUniqueId() +
'.'
);
} finally {
lock.releaseLock();
}
}
/**
* @returns {SpreadsheetApp.Sheet}
*/
function getAuditSheet_() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error('Open this project from its bound spreadsheet, not a standalone project.');
}
const sheet = spreadsheet.getSheetByName(AUDIT_SHEET_NAME_);
if (!sheet) {
throw new Error('AuditQueue sheet is missing. Import sample.csv into a tab named AuditQueue.');
}
return sheet;
}
/**
* Reads AuditQueue in one getValues() call and validates headers, cells, merges, and formulas.
*
* @param {SpreadsheetApp.Sheet} sheet
* @returns {AuditTable_}
*/
function readAuditTable_(sheet) {
const range = sheet.getDataRange();
if (
range.getNumRows() > AUDIT_MAX_ROWS_ + 1 ||
range.getNumColumns() > AUDIT_HEADERS_.length
) {
throw new Error(
'AuditQueue must fit within A1:E2001 (up to 2000 audit rows). Move extra content to another tab.'
);
}
const values = range.getValues();
const header = values[0];
if (
header.length !== AUDIT_HEADERS_.length ||
header.some(function (cell, index) {
return cell !== AUDIT_HEADERS_[index];
})
) {
throw new Error(
'AuditQueue must use this exact header row: Item ID, Department, Owner, Audit Status, Checked At.'
);
}
const rows = values.slice(1);
rows.forEach(function (row, index) {
const rowNumber = index + 2;
if (row.length !== AUDIT_HEADERS_.length || !row[0] || !row[1] || !row[2]) {
throw new Error('AuditQueue row ' + rowNumber + ' is missing Item ID, Department, or Owner.');
}
if (typeof row[3] !== 'string' || !AUDIT_STATUSES_.includes(row[3])) {
throw new Error('AuditQueue row ' + rowNumber + ' has an unsupported Audit Status: ' + String(row[3]) + '.');
}
if (typeof row[4] === 'string' && (row[4].startsWith('=') || row[4].startsWith('+'))) {
throw new Error('AuditQueue row ' + rowNumber + ' has a Checked At value starting with = or +.');
}
});
if (rows.length > 0) {
const outputRange = sheet.getRange(2, 4, rows.length, 2);
if (outputRange.isPartOfMerge()) {
throw new Error('Audit Status and Checked At cells must not be merged.');
}
if (
outputRange.getFormulas().some(function (formulaRow) {
return formulaRow[0] !== '' || formulaRow[1] !== '';
})
) {
throw new Error('Audit Status and Checked At cells must hold plain values, not formulas.');
}
}
return { sheet: sheet, rows: rows };
}
/**
* Lists clock triggers owned by the current user for resumeChunkedAudit.
* Throws if a non-clock trigger points at resumeChunkedAudit.
*
* @returns {ScriptApp.Trigger[]}
*/
function listContinuationTriggers_() {
const matching = ScriptApp.getProjectTriggers().filter(function (trigger) {
return trigger.getHandlerFunction() === CONTINUATION_HANDLER_;
});
if (
matching.some(function (trigger) {
return (
trigger.getEventType() !== ScriptApp.EventType.CLOCK ||
trigger.getTriggerSource() !== ScriptApp.TriggerSource.CLOCK
);
})
) {
throw new Error(
'A non-clock trigger uses resumeChunkedAudit. Review and remove it in Triggers before running.'
);
}
return matching;
}
/**
* Deletes every visible clock trigger for resumeChunkedAudit and returns the count removed.
*
* @returns {number}
*/
function deleteContinuationTriggers_() {
const triggers = listContinuationTriggers_();
triggers.forEach(function (trigger) {
ScriptApp.deleteTrigger(trigger);
});
return triggers.length;
}
/**
* @param {string} rawText
* @returns {AuditCursor_}
*/
function decodeAuditCursor_(rawText) {
if (rawText.length === 0 || rawText.length > MAX_CURSOR_CHARS_) {
throw new Error('AUDIT_CURSOR_V1 must be a JSON string of 1 to 2048 characters.');
}
/** @type {unknown} */
let parsed;
try {
parsed = JSON.parse(rawText);
} catch (error) {
throw new Error('AUDIT_CURSOR_V1 is not valid JSON. Inspect Script Properties or run cancelChunkedAudit.');
}
if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) {
throw new Error('AUDIT_CURSOR_V1 must be a JSON object.');
}
const record = /** @type {Record<string, unknown>} */ (parsed);
const expectedKeys = [
'version',
'state',
'nextRowIndex',
'slicesCompleted',
'auditedTotal',
'updatedAtIso'
];
const actualKeys = Object.keys(record);
if (
actualKeys.length !== expectedKeys.length ||
actualKeys.some(function (key) {
return !expectedKeys.includes(key);
})
) {
throw new Error('AUDIT_CURSOR_V1 has missing or unexpected fields.');
}
if (record.version !== 1) {
throw new Error('AUDIT_CURSOR_V1 has unsupported schema version: ' + String(record.version) + '.');
}
const stateValue = record.state;
if (
typeof stateValue !== 'string' ||
(stateValue !== 'running_chunk' &&
stateValue !== 'scheduled_continuation' &&
stateValue !== 'paused_slice_cap')
) {
throw new Error('AUDIT_CURSOR_V1 has an invalid state field.');
}
if (
!isBoundedInteger_(record.nextRowIndex, 0, AUDIT_MAX_ROWS_) ||
!isBoundedInteger_(record.slicesCompleted, 0, 500) ||
!isBoundedInteger_(record.auditedTotal, 0, AUDIT_MAX_ROWS_ * 500) ||
typeof record.updatedAtIso !== 'string' ||
!/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z$/.test(record.updatedAtIso)
) {
throw new Error('AUDIT_CURSOR_V1 has out-of-range numeric fields or an invalid UTC timestamp.');
}
return {
version: 1,
state: stateValue,
nextRowIndex: record.nextRowIndex,
slicesCompleted: record.slicesCompleted,
auditedTotal: record.auditedTotal,
updatedAtIso: record.updatedAtIso
};
}
/**
* @param {AuditCursor_} cursor
* @returns {string}
*/
function encodeAuditCursor_(cursor) {
const serialized = JSON.stringify(cursor);
if (serialized.length > MAX_CURSOR_CHARS_) {
throw new Error('Encoded AUDIT_CURSOR_V1 exceeds the 2048-character limit.');
}
return serialized;
}
/**
* @returns {void}
*/
function validateSliceConfig_() {
if (!isBoundedInteger_(MAX_ITEMS_PER_SLICE, 1, AUDIT_MAX_ROWS_)) {
throw new Error('MAX_ITEMS_PER_SLICE must be an integer from 1 through ' + AUDIT_MAX_ROWS_ + '.');
}
if (!isBoundedInteger_(SOFT_TIME_BUDGET_MS, 1000, 300000)) {
throw new Error('SOFT_TIME_BUDGET_MS must be an integer from 1000 through 300000 milliseconds.');
}
if (!isBoundedInteger_(CONTINUATION_DELAY_MS, 1000, 3600000)) {
throw new Error('CONTINUATION_DELAY_MS must be an integer from 1000 through 3600000 milliseconds.');
}
if (!isBoundedInteger_(MAX_CONTINUATION_SLICES, 1, 500)) {
throw new Error('MAX_CONTINUATION_SLICES must be an integer from 1 through 500.');
}
}
/**
* @param {unknown} value
* @param {number} minimum
* @param {number} maximum
* @returns {value is number}
*/
function isBoundedInteger_(value, minimum, maximum) {
return (
typeof value === 'number' &&
Number.isInteger(value) &&
value >= minimum &&
value <= maximum
);
}
/**
* @param {number} epochMs
* @returns {string}
*/
function formatUtcIsoSeconds_(epochMs) {
return new Date(epochMs).toISOString().replace(/\.\d{3}Z$/, 'Z');
}
{
"timeZone": "Etc/UTC",
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/script.scriptapp"
]
}
The manifest sets the V8 runtime and Etc/UTC time zone, requests spreadsheets.currentonly to read and write the bound spreadsheet, and requests script.scriptapp to list, create, and delete the current user's triggers in this project. PropertiesService and LockService need no additional OAuth scopes (Authorization for Google services).
In getAuditSheet_(), the script checks both SpreadsheetApp.getActiveSpreadsheet() and spreadsheet.getSheetByName('AuditQueue') before reading any range. If the AuditQueue tab is missing or misnamed, getSheetByName returns null and the helper throws an actionable setup message instead of failing later with Cannot read properties of null (reading 'getRange').
Step through slices manually before waiting on triggers
Code.gs sets MAX_ITEMS_PER_SLICE = 2 alongside SOFT_TIME_BUDGET_MS = 270000. On the six-row sample table, five rows start as Pending (AUD-101, AUD-102, AUD-104, AUD-105, and AUD-106) and one row starts as Audited (AUD-103). Capping each slice at two audited items lets you observe three distinct continuation slices on six rows without waiting 4.5 minutes per run.
Because resumeChunkedAudit reads its cursor from ScriptProperties and ignores the clock event object, you can run startChunkedAudit once and then click Run on resumeChunkedAudit in the editor to step through every slice immediately. Testing manual slices first verifies your row math, cursor transitions, and completion cleanup before you wait on live clock triggers.
Walk through the full lifecycle from the Apps Script editor under one Google account:
Select
inspectChunkedAuditStateand click Run. Authorize the project when prompted. ExpectAudit state: idle (AUDIT_CURSOR_V1 is not set).andActive continuation triggers for current user: 0.Run
startChunkedAudit. Slice 1 auditsAUD-101andAUD-102, skipsAUD-103in memory, writesD2:E7, savesnextRowIndex: 3(row 5) inAUDIT_CURSOR_V1, and schedulesresumeChunkedAudit.Run
inspectChunkedAuditState. ExpectAudit state: scheduled_continuation (next spreadsheet row 5, slicesCompleted=1, auditedTotal=2).andActive continuation triggers for current user: 1.Before the 1-minute trigger fires, run
resumeChunkedAudit. Slice 2 auditsAUD-104andAUD-105, savesnextRowIndex: 5(row 7), deletes the prior trigger, and schedules a replacement.Run
resumeChunkedAuditonce more. Slice 3 auditsAUD-106, writesD2:E7, deletes the replacement trigger, clearsAUDIT_CURSOR_V1, and logsAudit state: completed in 3 slice(s).Run
inspectChunkedAuditStateagain to confirm the state is back toidlewith0continuation triggers.
Inspect the AuditQueue sheet after Slice 3 completes:
| Item ID and row | Initial status | Updated during | Final Audit Status | Final Checked At |
|---|---|---|---|---|
AUD-101, row 2 | Pending | Slice 1 (startChunkedAudit) | Audited | T1 (Slice 1 UTC timestamp) |
AUD-102, row 3 | Pending | Slice 1 (startChunkedAudit) | Audited | T1 (same Slice 1 UTC timestamp) |
AUD-103, row 4 | Audited | Skipped in memory | Audited | 2026-10-01T08:30:00Z (unchanged) |
AUD-104, row 5 | Pending | Slice 2 (resumeChunkedAudit) | Audited | T2 (Slice 2 UTC timestamp) |
AUD-105, row 6 | Pending | Slice 2 (resumeChunkedAudit) | Audited | T2 (same Slice 2 UTC timestamp) |
AUD-106, row 7 | Pending | Slice 3 (resumeChunkedAudit) | Audited | T3 (Slice 3 UTC timestamp) |
If the Slice 1 or Slice 2 clock trigger was already queued by Google's scheduler right before deleteTrigger ran, that late firing calls resumeChunkedAudit with AUDIT_CURSOR_V1 already deleted. Because runAuditSlice_(false) exits immediately when rawCursor === null, a late firing cleans up 0 triggers and leaves AuditQueue untouched.
Checkpoint each slice in batch under one lock
runAuditSlice_ wraps the entire read, slice loop, batch write, property checkpoint, and trigger update inside LockService.getScriptLock():
lock.tryLock(5000)waits up to 5 seconds for exclusive access. An overlapping run logsSkipped audit slice because another execution holds the script lock.and exits immediately.readAuditTable_checks the dimensions ofsheet.getDataRange()before callinggetValues(), verifies the five column headers, and checksD2:Efor merged cells and formulas in onegetFormulas()read.The
whileloop runs in V8 memory without service calls.AuditedandHoldrows advancerowIndexwithout counting towardMAX_ITEMS_PER_SLICE, preventing empty trailing slices.Before each
Pendingrow, the loop checksauditedThisSlice >= MAX_ITEMS_PER_SLICEandDate.now() - startedAtMs >= SOFT_TIME_BUDGET_MS, stopping at 4.5 minutes with 90 seconds before the 6-minute limit.When rows change,
Range.setValueswritesD2:E7andSpreadsheetApp.flush()applies pending changes beforePropertiesService.getScriptProperties()updatesAUDIT_CURSOR_V1andfinallycallslock.releaseLock().
Writing the sheet and calling flush() before advancing AUDIT_CURSOR_V1 makes recovery predictable if an execution fails mid-transition. Suppose flush() succeeds on Slice 1, and an error interrupts the script before setProperty saves nextRowIndex: 3. On the next run, AUD-101 and AUD-102 already contain Audited in column D, so re-scanning from nextRowIndex: 0 skips both rows in memory without overwriting their T1 timestamps.
Note two boundaries of this sheet-only checkpoint model:
Because
cursor.nextRowIndexmoves forward from0tooutputRows.length, changing row 2 back toPendingwhile the cursor is paused at row 5 will not revisit row 2 until the current job finishes and a newstartChunkedAuditrun starts at index0. Deleting rows abovenextRowIndexwhile a job is paused shrinks the table and can shift row positions, so edit queue structure only wheninspectChunkedAuditStatereportsidle.Batching all writes at the end of a slice fits transformations that update only the sheet. If each row also sends an email or creates a Drive file, record a per-row completion marker or external resource ID right after each external call, as State across runs with PropertiesService explained, so an interrupted slice can detect which external actions already happened.
Delete prior one-off triggers and cap total slices
A one-off trigger created with ClockTriggerBuilder.after(durationMilliseconds) fires once and then remains in Triggers in a disabled state. Disabled triggers still count toward the 20-trigger per-user, per-script limit (installable triggers, Quotas for Google services) until ScriptApp.deleteTrigger(trigger) deletes them. Always delete expired continuation triggers before calling create(), and cap total slices with MAX_CONTINUATION_SLICES so a broken loop cannot reschedule itself all day and exhaust your 90-minute (consumer) or 6-hour (Google Workspace) daily trigger runtime quota.
deleteContinuationTriggers_() calls ScriptApp.getProjectTriggers(), keeps only Trigger objects whose getHandlerFunction() equals 'resumeChunkedAudit', verifies that each match is a clock trigger, and deletes each one before runAuditSlice_ creates the next trigger. Deleting first matters when a project already holds other triggers. If 19 unrelated triggers and 1 expired continuation trigger exist, calling create() before deleteTrigger() hits the 20-trigger ceiling and throws This script has too many triggers.
When you build the continuation trigger with ScriptApp.newTrigger(CONTINUATION_HANDLER_).timeBased().after(CONTINUATION_DELAY_MS).create(), CONTINUATION_HANDLER_ must match the exact name of a top-level function without parentheses or a trailing _ character. A typo or private suffix causes the trigger firing to fail with Script function not found.
ClockTriggerBuilder.after(60 * 1000) requests a minimum delay of 60,000 milliseconds, not an exact 60-second timer (ClockTriggerBuilder.after reference). Apps Script queues the trigger to run after at least that duration has passed, and actual execution start times can vary by a minute or more depending on platform load.
To stop a paused or scheduled job before it finishes, run cancelChunkedAudit under the Google account that started startChunkedAudit. cancelChunkedAudit waits up to 5 seconds for the script lock (throwing an error if a slice is actively running so you know to retry), deletes all resumeChunkedAudit clock triggers visible to that account, and deletes AUDIT_CURSOR_V1. Rows already marked Audited keep their status and Checked At timestamps. Remember from Install and remove time-driven triggers that ScriptApp.getProjectTriggers() returns only the current account's triggers. If a teammate started a continuation chain under their own account, they must run cancelChunkedAudit under that account.
Exercise
Practice mid-job cancellation and test the MAX_CONTINUATION_SLICES circuit breaker on your disposable AuditQueue sheet:
Reset
D2:D3andD5:D7toPendingand clearE2:E3andE5:E7, keepingAUD-103in row 4 asAuditedwith2026-10-01T08:30:00Z.Run
startChunkedAuditonce so Slice 1 auditsAUD-101andAUD-102and schedulesresumeChunkedAudit. Open Triggers in the left sidebar (or runinspectChunkedAuditState) to confirm one clock trigger is queued.Run
cancelChunkedAuditbefore waiting for the remaining slices. Confirm thatinspectChunkedAuditStatelogsAudit state: idleandActive continuation triggers for current user: 0., whileAUD-101andAUD-102stayAuditedandAUD-104throughAUD-106stayPending.In
Code.gs, changeMAX_ITEMS_PER_SLICEto1andMAX_CONTINUATION_SLICESto2, then save. ThreePendingrows remain (AUD-104,AUD-105, andAUD-106), which now need three slices.Run
startChunkedAudit(auditsAUD-104), then runresumeChunkedAudit(auditsAUD-105). AtMAX_CONTINUATION_SLICES(2) withAUD-106stillPending,resumeChunkedAuditflushesAUD-105, deletes the trigger, savesstate: 'paused_slice_cap', and throws.Run
inspectChunkedAuditStateto confirmpaused_slice_capand0triggers.Restore
MAX_ITEMS_PER_SLICE = 2andMAX_CONTINUATION_SLICES = 25, saveCode.gs, and runstartChunkedAuditonce to finishAUD-106and return the project toidle.
When finished, confirm Triggers lists zero triggers for this disposable spreadsheet. Next, read Build a Sheets request tracker.
Project files
Complete source files for this chapter's examples.
Audit a sheet queue across slices with continuation triggers
Code.gs
Download fileconst MAX_ITEMS_PER_SLICE = 2; // Maximum Pending rows to audit in one execution.
const SOFT_TIME_BUDGET_MS = 270000; // Stop starting new rows at 4.5 minutes to leave headroom before the 6-minute limit.
const CONTINUATION_DELAY_MS = 60 * 1000; // Minimum delay in milliseconds requested for ClockTriggerBuilder.after().
const MAX_CONTINUATION_SLICES = 25; // Maximum slices allowed for one job before stopping automatic rescheduling.
const AUDIT_SHEET_NAME_ = 'AuditQueue';
const AUDIT_CURSOR_KEY_ = 'AUDIT_CURSOR_V1';
const CONTINUATION_HANDLER_ = 'resumeChunkedAudit';
const AUDIT_MAX_ROWS_ = 2000;
const LOCK_WAIT_MS_ = 5000;
const MAX_CURSOR_CHARS_ = 2048;
const AUDIT_HEADERS_ = ['Item ID', 'Department', 'Owner', 'Audit Status', 'Checked At'];
const AUDIT_STATUSES_ = ['Pending', 'Audited', 'Hold'];
/**
* @typedef {'running_chunk' | 'scheduled_continuation' | 'paused_slice_cap'} AuditCursorState_
*/
/**
* @typedef {{
* version: number,
* state: AuditCursorState_,
* nextRowIndex: number,
* slicesCompleted: number,
* auditedTotal: number,
* updatedAtIso: string
* }} AuditCursor_
*/
/**
* @typedef {{
* sheet: SpreadsheetApp.Sheet,
* rows: unknown[][]
* }} AuditTable_
*/
/**
* Starts a new chunked audit of AuditQueue or resumes an in-progress cursor.
*
* @returns {void}
*/
function startChunkedAudit() {
runAuditSlice_(true);
}
/**
* Continues an active chunked audit from a one-off clock trigger or a manual test run.
*
* @returns {void}
*/
function resumeChunkedAudit() {
runAuditSlice_(false);
}
/**
* Logs the saved AUDIT_CURSOR_V1 record and the current user's continuation trigger count.
* Makes no changes to spreadsheet cells, ScriptProperties, or triggers.
*
* @returns {void}
*/
function inspectChunkedAuditState() {
const rawCursor = PropertiesService.getScriptProperties().getProperty(AUDIT_CURSOR_KEY_);
const triggers = listContinuationTriggers_();
if (rawCursor === null) {
Logger.log('Audit state: idle (AUDIT_CURSOR_V1 is not set).');
Logger.log('Active continuation triggers for current user: ' + triggers.length + '.');
return;
}
const cursor = decodeAuditCursor_(rawCursor);
Logger.log(
'Audit state: ' +
cursor.state +
' (next spreadsheet row ' +
(cursor.nextRowIndex + 2) +
', slicesCompleted=' +
cursor.slicesCompleted +
', auditedTotal=' +
cursor.auditedTotal +
').'
);
Logger.log('Cursor JSON: ' + JSON.stringify(cursor));
Logger.log('Active continuation triggers for current user: ' + triggers.length + '.');
}
/**
* Deletes the current user's continuation clock triggers and clears AUDIT_CURSOR_V1.
* Leaves already-audited rows in AuditQueue intact.
*
* @returns {void}
*/
function cancelChunkedAudit() {
const lock = LockService.getScriptLock();
if (!lock.tryLock(LOCK_WAIT_MS_)) {
throw new Error(
'Could not acquire the script lock to cancel the audit. Wait for the active slice to finish and try again.'
);
}
try {
const store = PropertiesService.getScriptProperties();
const hadCursor = store.getProperty(AUDIT_CURSOR_KEY_) !== null;
const removedTriggers = deleteContinuationTriggers_();
store.deleteProperty(AUDIT_CURSOR_KEY_);
Logger.log(
'Audit state: cancelled. Removed ' +
removedTriggers +
' continuation trigger(s); cleared AUDIT_CURSOR_V1=' +
String(hadCursor) +
'.'
);
} finally {
lock.releaseLock();
}
}
/**
* Runs one bounded slice under the script lock, writes updates in one batch,
* and replaces or clears continuation triggers.
*
* @param {boolean} allowInitialize
* @returns {void}
*/
function runAuditSlice_(allowInitialize) {
validateSliceConfig_();
const startedAtMs = Date.now();
const lock = LockService.getScriptLock();
if (!lock.tryLock(LOCK_WAIT_MS_)) {
Logger.log('Skipped audit slice because another execution holds the script lock.');
return;
}
try {
const store = PropertiesService.getScriptProperties();
const rawCursor = store.getProperty(AUDIT_CURSOR_KEY_);
if (rawCursor === null && !allowInitialize) {
const removedTriggers = deleteContinuationTriggers_();
Logger.log(
'No active audit cursor in ScriptProperties (state=idle). Removed ' +
removedTriggers +
' continuation trigger(s).'
);
return;
}
const sliceTimestampIso = formatUtcIsoSeconds_(startedAtMs);
/** @type {AuditCursor_} */
const cursor =
rawCursor === null
? {
version: 1,
state: 'running_chunk',
nextRowIndex: 0,
slicesCompleted: 0,
auditedTotal: 0,
updatedAtIso: sliceTimestampIso
}
: decodeAuditCursor_(rawCursor);
if (cursor.slicesCompleted >= MAX_CONTINUATION_SLICES) {
deleteContinuationTriggers_();
throw new Error(
'Audit cursor already reached MAX_CONTINUATION_SLICES (' +
MAX_CONTINUATION_SLICES +
'). Inspect AuditQueue with inspectChunkedAuditState, raise MAX_CONTINUATION_SLICES, or run cancelChunkedAudit.'
);
}
const table = readAuditTable_(getAuditSheet_());
if (table.rows.length === 0) {
const removedTriggers = deleteContinuationTriggers_();
store.deleteProperty(AUDIT_CURSOR_KEY_);
Logger.log(
'AuditQueue has no data rows. Cleared cursor and removed ' +
removedTriggers +
' continuation trigger(s).'
);
return;
}
if (cursor.nextRowIndex > table.rows.length) {
throw new Error(
'Saved cursor points past the end of AuditQueue (nextRowIndex=' +
cursor.nextRowIndex +
', rows=' +
table.rows.length +
'). Inspect the sheet or run cancelChunkedAudit before restarting.'
);
}
const outputRows = table.rows.map(function (row) {
return [String(row[3]), row[4]];
});
let rowIndex = cursor.nextRowIndex;
let auditedThisSlice = 0;
let stopReason = 'completed';
while (rowIndex < outputRows.length) {
if (outputRows[rowIndex][0] !== 'Pending') {
rowIndex += 1;
continue;
}
if (auditedThisSlice >= MAX_ITEMS_PER_SLICE) {
stopReason = 'max_items_per_slice';
break;
}
if (Date.now() - startedAtMs >= SOFT_TIME_BUDGET_MS) {
stopReason = 'soft_time_budget';
break;
}
outputRows[rowIndex] = ['Audited', sliceTimestampIso];
auditedThisSlice += 1;
rowIndex += 1;
}
if (auditedThisSlice > 0) {
table.sheet.getRange(2, 4, outputRows.length, 2).setValues(outputRows);
SpreadsheetApp.flush();
}
const nextSlicesCompleted = cursor.slicesCompleted + 1;
const nextAuditedTotal = cursor.auditedTotal + auditedThisSlice;
if (rowIndex >= outputRows.length) {
const removedTriggers = deleteContinuationTriggers_();
store.deleteProperty(AUDIT_CURSOR_KEY_);
Logger.log(
'Audit state: completed in ' +
nextSlicesCompleted +
' slice(s). Audited ' +
auditedThisSlice +
' item(s) in final slice (' +
nextAuditedTotal +
' total). Removed ' +
removedTriggers +
' continuation trigger(s) and cleared AUDIT_CURSOR_V1.'
);
return;
}
if (nextSlicesCompleted >= MAX_CONTINUATION_SLICES) {
const removedTriggers = deleteContinuationTriggers_();
/** @type {AuditCursor_} */
const cappedCursor = {
version: 1,
state: 'paused_slice_cap',
nextRowIndex: rowIndex,
slicesCompleted: nextSlicesCompleted,
auditedTotal: nextAuditedTotal,
updatedAtIso: sliceTimestampIso
};
store.setProperty(AUDIT_CURSOR_KEY_, encodeAuditCursor_(cappedCursor));
throw new Error(
'Stopped after ' +
nextSlicesCompleted +
' slice(s) at MAX_CONTINUATION_SLICES=' +
MAX_CONTINUATION_SLICES +
' with Pending rows remaining at spreadsheet row ' +
(rowIndex + 2) +
' (removed ' +
removedTriggers +
' trigger(s)). Inspect AuditQueue before resuming or canceling.'
);
}
/** @type {AuditCursor_} */
const nextCursor = {
version: 1,
state: 'scheduled_continuation',
nextRowIndex: rowIndex,
slicesCompleted: nextSlicesCompleted,
auditedTotal: nextAuditedTotal,
updatedAtIso: sliceTimestampIso
};
store.setProperty(AUDIT_CURSOR_KEY_, encodeAuditCursor_(nextCursor));
const removedTriggers = deleteContinuationTriggers_();
const nextTrigger = ScriptApp.newTrigger(CONTINUATION_HANDLER_)
.timeBased()
.after(CONTINUATION_DELAY_MS)
.create();
Logger.log(
'Audit state: scheduled_continuation after slice ' +
nextSlicesCompleted +
' (' +
stopReason +
'). Audited ' +
auditedThisSlice +
' item(s) (' +
nextAuditedTotal +
' total), next spreadsheet row ' +
(rowIndex + 2) +
', removed ' +
removedTriggers +
' prior trigger(s), scheduled trigger ' +
nextTrigger.getUniqueId() +
'.'
);
} finally {
lock.releaseLock();
}
}
/**
* @returns {SpreadsheetApp.Sheet}
*/
function getAuditSheet_() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error('Open this project from its bound spreadsheet, not a standalone project.');
}
const sheet = spreadsheet.getSheetByName(AUDIT_SHEET_NAME_);
if (!sheet) {
throw new Error('AuditQueue sheet is missing. Import sample.csv into a tab named AuditQueue.');
}
return sheet;
}
/**
* Reads AuditQueue in one getValues() call and validates headers, cells, merges, and formulas.
*
* @param {SpreadsheetApp.Sheet} sheet
* @returns {AuditTable_}
*/
function readAuditTable_(sheet) {
const range = sheet.getDataRange();
if (
range.getNumRows() > AUDIT_MAX_ROWS_ + 1 ||
range.getNumColumns() > AUDIT_HEADERS_.length
) {
throw new Error(
'AuditQueue must fit within A1:E2001 (up to 2000 audit rows). Move extra content to another tab.'
);
}
const values = range.getValues();
const header = values[0];
if (
header.length !== AUDIT_HEADERS_.length ||
header.some(function (cell, index) {
return cell !== AUDIT_HEADERS_[index];
})
) {
throw new Error(
'AuditQueue must use this exact header row: Item ID, Department, Owner, Audit Status, Checked At.'
);
}
const rows = values.slice(1);
rows.forEach(function (row, index) {
const rowNumber = index + 2;
if (row.length !== AUDIT_HEADERS_.length || !row[0] || !row[1] || !row[2]) {
throw new Error('AuditQueue row ' + rowNumber + ' is missing Item ID, Department, or Owner.');
}
if (typeof row[3] !== 'string' || !AUDIT_STATUSES_.includes(row[3])) {
throw new Error('AuditQueue row ' + rowNumber + ' has an unsupported Audit Status: ' + String(row[3]) + '.');
}
if (typeof row[4] === 'string' && (row[4].startsWith('=') || row[4].startsWith('+'))) {
throw new Error('AuditQueue row ' + rowNumber + ' has a Checked At value starting with = or +.');
}
});
if (rows.length > 0) {
const outputRange = sheet.getRange(2, 4, rows.length, 2);
if (outputRange.isPartOfMerge()) {
throw new Error('Audit Status and Checked At cells must not be merged.');
}
if (
outputRange.getFormulas().some(function (formulaRow) {
return formulaRow[0] !== '' || formulaRow[1] !== '';
})
) {
throw new Error('Audit Status and Checked At cells must hold plain values, not formulas.');
}
}
return { sheet: sheet, rows: rows };
}
/**
* Lists clock triggers owned by the current user for resumeChunkedAudit.
* Throws if a non-clock trigger points at resumeChunkedAudit.
*
* @returns {ScriptApp.Trigger[]}
*/
function listContinuationTriggers_() {
const matching = ScriptApp.getProjectTriggers().filter(function (trigger) {
return trigger.getHandlerFunction() === CONTINUATION_HANDLER_;
});
if (
matching.some(function (trigger) {
return (
trigger.getEventType() !== ScriptApp.EventType.CLOCK ||
trigger.getTriggerSource() !== ScriptApp.TriggerSource.CLOCK
);
})
) {
throw new Error(
'A non-clock trigger uses resumeChunkedAudit. Review and remove it in Triggers before running.'
);
}
return matching;
}
/**
* Deletes every visible clock trigger for resumeChunkedAudit and returns the count removed.
*
* @returns {number}
*/
function deleteContinuationTriggers_() {
const triggers = listContinuationTriggers_();
triggers.forEach(function (trigger) {
ScriptApp.deleteTrigger(trigger);
});
return triggers.length;
}
/**
* @param {string} rawText
* @returns {AuditCursor_}
*/
function decodeAuditCursor_(rawText) {
if (rawText.length === 0 || rawText.length > MAX_CURSOR_CHARS_) {
throw new Error('AUDIT_CURSOR_V1 must be a JSON string of 1 to 2048 characters.');
}
/** @type {unknown} */
let parsed;
try {
parsed = JSON.parse(rawText);
} catch (error) {
throw new Error('AUDIT_CURSOR_V1 is not valid JSON. Inspect Script Properties or run cancelChunkedAudit.');
}
if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) {
throw new Error('AUDIT_CURSOR_V1 must be a JSON object.');
}
const record = /** @type {Record<string, unknown>} */ (parsed);
const expectedKeys = [
'version',
'state',
'nextRowIndex',
'slicesCompleted',
'auditedTotal',
'updatedAtIso'
];
const actualKeys = Object.keys(record);
if (
actualKeys.length !== expectedKeys.length ||
actualKeys.some(function (key) {
return !expectedKeys.includes(key);
})
) {
throw new Error('AUDIT_CURSOR_V1 has missing or unexpected fields.');
}
if (record.version !== 1) {
throw new Error('AUDIT_CURSOR_V1 has unsupported schema version: ' + String(record.version) + '.');
}
const stateValue = record.state;
if (
typeof stateValue !== 'string' ||
(stateValue !== 'running_chunk' &&
stateValue !== 'scheduled_continuation' &&
stateValue !== 'paused_slice_cap')
) {
throw new Error('AUDIT_CURSOR_V1 has an invalid state field.');
}
if (
!isBoundedInteger_(record.nextRowIndex, 0, AUDIT_MAX_ROWS_) ||
!isBoundedInteger_(record.slicesCompleted, 0, 500) ||
!isBoundedInteger_(record.auditedTotal, 0, AUDIT_MAX_ROWS_ * 500) ||
typeof record.updatedAtIso !== 'string' ||
!/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z$/.test(record.updatedAtIso)
) {
throw new Error('AUDIT_CURSOR_V1 has out-of-range numeric fields or an invalid UTC timestamp.');
}
return {
version: 1,
state: stateValue,
nextRowIndex: record.nextRowIndex,
slicesCompleted: record.slicesCompleted,
auditedTotal: record.auditedTotal,
updatedAtIso: record.updatedAtIso
};
}
/**
* @param {AuditCursor_} cursor
* @returns {string}
*/
function encodeAuditCursor_(cursor) {
const serialized = JSON.stringify(cursor);
if (serialized.length > MAX_CURSOR_CHARS_) {
throw new Error('Encoded AUDIT_CURSOR_V1 exceeds the 2048-character limit.');
}
return serialized;
}
/**
* @returns {void}
*/
function validateSliceConfig_() {
if (!isBoundedInteger_(MAX_ITEMS_PER_SLICE, 1, AUDIT_MAX_ROWS_)) {
throw new Error('MAX_ITEMS_PER_SLICE must be an integer from 1 through ' + AUDIT_MAX_ROWS_ + '.');
}
if (!isBoundedInteger_(SOFT_TIME_BUDGET_MS, 1000, 300000)) {
throw new Error('SOFT_TIME_BUDGET_MS must be an integer from 1000 through 300000 milliseconds.');
}
if (!isBoundedInteger_(CONTINUATION_DELAY_MS, 1000, 3600000)) {
throw new Error('CONTINUATION_DELAY_MS must be an integer from 1000 through 3600000 milliseconds.');
}
if (!isBoundedInteger_(MAX_CONTINUATION_SLICES, 1, 500)) {
throw new Error('MAX_CONTINUATION_SLICES must be an integer from 1 through 500.');
}
}
/**
* @param {unknown} value
* @param {number} minimum
* @param {number} maximum
* @returns {value is number}
*/
function isBoundedInteger_(value, minimum, maximum) {
return (
typeof value === 'number' &&
Number.isInteger(value) &&
value >= minimum &&
value <= maximum
);
}
/**
* @param {number} epochMs
* @returns {string}
*/
function formatUtcIsoSeconds_(epochMs) {
return new Date(epochMs).toISOString().replace(/\.\d{3}Z$/, 'Z');
}
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/script.scriptapp"
]
}
sample.csv
Download fileItem ID,Department,Owner,Audit Status,Checked At
AUD-101,Finance,morgan.lee@example.com,Pending,
AUD-102,Facilities,avery.quinn@example.com,Pending,
AUD-103,Legal,sam.chen@example.com,Audited,2026-10-01T08:30:00Z
AUD-104,Engineering,jordan.diaz@example.com,Pending,
AUD-105,Operations,riley.patel@example.com,Pending,
AUD-106,Security,casey.kim@example.com,Pending,