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} */ (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'); }