Skip to content
Skip to chapter

Chapter 17

Reliable automations

10 min read

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.

By the end

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.

Chapter navigation

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:

  1. idle: AUDIT_CURSOR_V1 is absent from ScriptProperties and no continuation trigger is scheduled.

  2. running_chunk: one execution holds the script lock, reads AuditQueue, and processes a bounded slice of Pending rows in memory.

  3. scheduled_continuation: unfinished rows remain, so the execution flushes its batch write, saves the next row index in AUDIT_CURSOR_V1, deletes prior continuation triggers, and creates one new clock trigger.

  4. completed or cancelled: no Pending rows remain (or the owner cancels the job), so the script deletes its continuation triggers and removes AUDIT_CURSOR_V1, returning to idle.

Four platform rules in the Apps Script quotas guide and ClockTriggerBuilder.after reference shape every part of this design:

Platform limit or behaviorDocumented valueDesign rule in Code.gs
Single execution runtime6 minutes per executionStop 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 count20 triggers per user per scriptDelete prior resumeChunkedAudit clock triggers before calling create() so disabled one-off triggers never fill the 20-trigger cap.
Daily trigger runtime90 minutes per day (consumer) or 6 hours per day (Google Workspace) across all triggers owned by that userEnforce MAX_CONTINUATION_SLICES (25) so a stalled job stops rescheduling instead of burning the owner's daily runtime quota.
after(durationMilliseconds) windowMinimum delay in milliseconds before the one-off clock trigger runsPass 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:

  1. Create a new Google Sheet and rename its first tab AuditQueue.

  2. Copy sample.csv below into cell A1, select column A, and choose Data > Split text to columns with Comma as the separator.

  3. Confirm that the table occupies A1:E7 (one header row plus six queue rows, with AUD-103 already Audited at 2026-10-01T08:30:00Z).

  4. Open Extensions > Apps Script and replace Code.gs with the complete file below.

  5. Open Project Settings, check Show "appsscript.json" manifest file in editor, replace appsscript.json with the manifest below, and save both files.

sample.csv
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,

Download sample.csv
Code.gs
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');
}

Download Code.gs
appsscript.json
{
  "timeZone": "Etc/UTC",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.scriptapp"
  ]
}

Download appsscript.json

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.

tip

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:

  1. Select inspectChunkedAuditState and click Run. Authorize the project when prompted. Expect Audit state: idle (AUDIT_CURSOR_V1 is not set). and Active continuation triggers for current user: 0.

  2. Run startChunkedAudit. Slice 1 audits AUD-101 and AUD-102, skips AUD-103 in memory, writes D2:E7, saves nextRowIndex: 3 (row 5) in AUDIT_CURSOR_V1, and schedules resumeChunkedAudit.

  3. Run inspectChunkedAuditState. Expect Audit state: scheduled_continuation (next spreadsheet row 5, slicesCompleted=1, auditedTotal=2). and Active continuation triggers for current user: 1.

  4. Before the 1-minute trigger fires, run resumeChunkedAudit. Slice 2 audits AUD-104 and AUD-105, saves nextRowIndex: 5 (row 7), deletes the prior trigger, and schedules a replacement.

  5. Run resumeChunkedAudit once more. Slice 3 audits AUD-106, writes D2:E7, deletes the replacement trigger, clears AUDIT_CURSOR_V1, and logs Audit state: completed in 3 slice(s).

  6. Run inspectChunkedAuditState again to confirm the state is back to idle with 0 continuation triggers.

Inspect the AuditQueue sheet after Slice 3 completes:

Item ID and rowInitial statusUpdated duringFinal Audit StatusFinal Checked At
AUD-101, row 2PendingSlice 1 (startChunkedAudit)AuditedT1 (Slice 1 UTC timestamp)
AUD-102, row 3PendingSlice 1 (startChunkedAudit)AuditedT1 (same Slice 1 UTC timestamp)
AUD-103, row 4AuditedSkipped in memoryAudited2026-10-01T08:30:00Z (unchanged)
AUD-104, row 5PendingSlice 2 (resumeChunkedAudit)AuditedT2 (Slice 2 UTC timestamp)
AUD-105, row 6PendingSlice 2 (resumeChunkedAudit)AuditedT2 (same Slice 2 UTC timestamp)
AUD-106, row 7PendingSlice 3 (resumeChunkedAudit)AuditedT3 (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():

  1. lock.tryLock(5000) waits up to 5 seconds for exclusive access. An overlapping run logs Skipped audit slice because another execution holds the script lock. and exits immediately.

  2. readAuditTable_ checks the dimensions of sheet.getDataRange() before calling getValues(), verifies the five column headers, and checks D2:E for merged cells and formulas in one getFormulas() read.

  3. The while loop runs in V8 memory without service calls. Audited and Hold rows advance rowIndex without counting toward MAX_ITEMS_PER_SLICE, preventing empty trailing slices.

  4. Before each Pending row, the loop checks auditedThisSlice >= MAX_ITEMS_PER_SLICE and Date.now() - startedAtMs >= SOFT_TIME_BUDGET_MS, stopping at 4.5 minutes with 90 seconds before the 6-minute limit.

  5. When rows change, Range.setValues writes D2:E7 and SpreadsheetApp.flush() applies pending changes before PropertiesService.getScriptProperties() updates AUDIT_CURSOR_V1 and finally calls lock.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.nextRowIndex moves forward from 0 to outputRows.length, changing row 2 back to Pending while the cursor is paused at row 5 will not revisit row 2 until the current job finishes and a new startChunkedAudit run starts at index 0. Deleting rows above nextRowIndex while a job is paused shrinks the table and can shift row positions, so edit queue structure only when inspectChunkedAuditState reports idle.

  • 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

warning

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.

note

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:

  1. Reset D2:D3 and D5:D7 to Pending and clear E2:E3 and E5:E7, keeping AUD-103 in row 4 as Audited with 2026-10-01T08:30:00Z.

  2. Run startChunkedAudit once so Slice 1 audits AUD-101 and AUD-102 and schedules resumeChunkedAudit. Open Triggers in the left sidebar (or run inspectChunkedAuditState) to confirm one clock trigger is queued.

  3. Run cancelChunkedAudit before waiting for the remaining slices. Confirm that inspectChunkedAuditState logs Audit state: idle and Active continuation triggers for current user: 0., while AUD-101 and AUD-102 stay Audited and AUD-104 through AUD-106 stay Pending.

  4. In Code.gs, change MAX_ITEMS_PER_SLICE to 1 and MAX_CONTINUATION_SLICES to 2, then save. Three Pending rows remain (AUD-104, AUD-105, and AUD-106), which now need three slices.

  5. Run startChunkedAudit (audits AUD-104), then run resumeChunkedAudit (audits AUD-105). At MAX_CONTINUATION_SLICES (2) with AUD-106 still Pending, resumeChunkedAudit flushes AUD-105, deletes the trigger, saves state: 'paused_slice_cap', and throws.

  6. Run inspectChunkedAuditState to confirm paused_slice_cap and 0 triggers.

  7. Restore MAX_ITEMS_PER_SLICE = 2 and MAX_CONTINUATION_SLICES = 25, save Code.gs, and run startChunkedAudit once to finish AUD-106 and return the project to idle.

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

appsscript.json

Download file
appsscript.json
{
  "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 file
sample.csv
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,