Build a safe Sheets request tracker

Build a bound Sheets request tracker that transitions New rows to Ready, records the transition timestamp in a Last updated column, and exposes a custom spreadsheet menu alongside an optional hourly trigger.

Create a disposable tracker

Create a new spreadsheet and bound script project for sheets-request-tracker. Whereas the previous chapter's fixture had four columns and updated blank statuses, this table has five columns (Request ID, Requester, Summary, Status, Last updated), requires an explicit New status, and uses request IDs starting at R-300. If you installed the hourly trigger in the previous chapter's project, remove that trigger in its own project first.

1. In a new spreadsheet, name the data tab Requests. Copy sample.csv below into A1, select column A, and choose Data > Split text to columns with Comma as the separator. Confirm that the table occupies five rows by five columns (A1:E5).

2. Keep A1:E1 as the header row and leave E2 and E4 blank for the two New rows.

3. Under File > Settings, set the spreadsheet time zone to UTC so displayed dates match the Etc/UTC script time zone.

4. Open Extensions > Apps Script from this spreadsheet and add Code.gs, TrackerHelpers.gs, and TriggerHelpers.gs. Enable Show "appsscript.json" manifest file in editor in Project Settings, replace appsscript.json with the manifest below, and save.

Request ID,Requester,Summary,Status,Last updated
R-300,Alex,Review the meeting notes,New,
R-301,Sam,Update the project brief,Hold,2026-09-01T09:00:00Z
R-302,Jo,Prepare a draft agenda,New,
R-303,Taylor,Archive approved notes,Done,2026-09-02T14:30:00Z

const TRACKER_SHEET_NAME_ = 'Requests';
const TRACKER_HANDLER_ = 'runRequestTracker';

/**
 * Adds explicit manual actions to the bound spreadsheet's menu.
 *
 * @returns {void}
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Request tracker')
    .addItem('Run update', 'runRequestTracker')
    .addItem('Install hourly schedule', 'installRequestTrackerTrigger')
    .addItem('Remove hourly schedule', 'removeRequestTrackerTriggers')
    .addToUi();
}

/**
 * Marks New requests Ready and timestamps them. Safe to run manually or by trigger.
 *
 * @returns {void}
 */
function runRequestTracker() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) {
    Logger.log('Skipped request tracker run because another execution is running.');
    return;
  }

  try {
    const table = readTrackerTable_(getTrackerSheet_());
    const nextValues = createStatusAndTimestampRows_(table.rows, new Date());
    const changed = nextValues.filter(function (row, index) {
      return row[0] !== table.rows[index][3];
    }).length;

    if (changed === 0) {
      Logger.log('No new requests found.');
      return;
    }

    table.sheet.getRange(2, 4, nextValues.length, 2).setValues(nextValues);
    SpreadsheetApp.flush();
    Logger.log('Marked ' + changed + ' request(s) Ready.');
  } finally {
    lock.releaseLock();
  }
}

/**
 * @returns {SpreadsheetApp.Sheet}
 */
function getTrackerSheet_() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  if (!spreadsheet) {
    throw new Error('Open this project from its bound spreadsheet, not a standalone project.');
  }
  const sheet = spreadsheet.getSheetByName(TRACKER_SHEET_NAME_);
  if (!sheet) {
    throw new Error('Requests sheet is missing. Import sample.csv into a sheet named Requests.');
  }
  return sheet;
}

const TRACKER_HEADER_ = ['Request ID', 'Requester', 'Summary', 'Status', 'Last updated'];
const TRACKER_STATUSES_ = ['New', 'Ready', 'Hold', 'Done'];
const TRACKER_MAX_ROWS_ = 100;

/**
 * @typedef {{sheet: SpreadsheetApp.Sheet, rows: Array<Array<*>>}} TrackerTable
 */

/**
 * Reads bounded table values once and checks the output range before a caller can write.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {TrackerTable}
 */
function readTrackerTable_(sheet) {
  const range = sheet.getDataRange();
  if (range.getNumRows() > TRACKER_MAX_ROWS_ + 1 || range.getNumColumns() > TRACKER_HEADER_.length) {
    throw new Error('Requests must fit within A1:E101 (100 request rows). Move other content to another tab.');
  }
  const values = range.getValues();
  const header = values[0];
  if (
    header.length !== TRACKER_HEADER_.length ||
    header.some(function (value, index) {
      return value !== TRACKER_HEADER_[index];
    })
  ) {
    throw new Error(
      'Requests must use this header row: Request ID, Requester, Summary, Status, Last updated.'
    );
  }

  const rows = values.slice(1);
  rows.forEach(function (row, index) {
    const rowNumber = index + 2;
    if (row.length !== TRACKER_HEADER_.length || !row[0] || !row[1] || !row[2]) {
      throw new Error('Request row ' + rowNumber + ' is incomplete.');
    }
    if (typeof row[3] !== 'string' || TRACKER_STATUSES_.indexOf(row[3]) === -1) {
      throw new Error('Request row ' + rowNumber + ' has an unsupported status: ' + row[3] + '.');
    }
    if (typeof row[4] === 'string' && row[4].startsWith('=')) {
      throw new Error('Request row ' + rowNumber + ' has a Last updated value beginning with =.');
    }
  });

  if (rows.length > 0) {
    const output = sheet.getRange(2, 4, rows.length, 2);
    if (output.isPartOfMerge()) {
      throw new Error('Status and Last updated cells must not be merged.');
    }
    if (output.getFormulas().some(function (row) { return row[0] !== '' || row[1] !== ''; })) {
      throw new Error('Status and Last updated cells must contain values, not formulas.');
    }
  }

  return { sheet: sheet, rows: rows };
}

/**
 * Produces a rectangular Status/Last updated output array without calling Apps Script services.
 *
 * @param {Array<Array<*>>} rows
 * @param {Date} now
 * @returns {Array<Array<*>>}
 */
function createStatusAndTimestampRows_(rows, now) {
  return rows.map(function (row) {
    if (row[3] === 'New') {
      return ['Ready', now];
    }
    return [row[3], row[4]];
  });
}

/**
 * Installs one hourly trigger for runRequestTracker.
 * A different account's installable triggers are not visible to this account.
 * Stops on visible conflicts; preserves one existing clock trigger without changing its cadence.
 *
 * @returns {void}
 */
function installRequestTrackerTrigger() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) {
    throw new Error('Could not acquire the trigger installation lock. Try again shortly.');
  }

  try {
    readTrackerTable_(getTrackerSheet_());
    const existing = ScriptApp.getProjectTriggers().filter(function (trigger) {
      return trigger.getHandlerFunction() === TRACKER_HANDLER_;
    });
    if (existing.some(function (trigger) {
      return trigger.getEventType() !== ScriptApp.EventType.CLOCK ||
        trigger.getTriggerSource() !== ScriptApp.TriggerSource.CLOCK;
    })) {
      throw new Error('A non-clock trigger uses runRequestTracker. Review it in Triggers before installing.');
    }
    if (existing.length > 1) {
      throw new Error('Multiple request tracker clock triggers exist. Review them, then run removeRequestTrackerTriggers before installing again.');
    }
    if (existing.length === 1) {
      Logger.log('Request tracker trigger already installed: ' + existing[0].getUniqueId());
      Logger.log('Confirm its hourly interval in Triggers. This installer does not change an existing schedule.');
      return;
    }

    const trigger = ScriptApp.newTrigger(TRACKER_HANDLER_).timeBased().everyHours(1).create();
    Logger.log('Installed request tracker trigger: ' + trigger.getUniqueId());
  } finally {
    lock.releaseLock();
  }
}

/**
 * Removes this account's clock triggers for runRequestTracker without changing sheet data.
 *
 * @returns {void}
 */
function removeRequestTrackerTriggers() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) {
    throw new Error('Could not acquire the trigger removal lock. Try again shortly.');
  }

  try {
    const triggers = ScriptApp.getProjectTriggers().filter(function (trigger) {
      return trigger.getHandlerFunction() === TRACKER_HANDLER_ &&
        trigger.getEventType() === ScriptApp.EventType.CLOCK &&
        trigger.getTriggerSource() === ScriptApp.TriggerSource.CLOCK;
    });
    triggers.forEach(function (trigger) {
      ScriptApp.deleteTrigger(trigger);
    });
    Logger.log('Removed ' + triggers.length + ' request tracker trigger(s).');
  } finally {
    lock.releaseLock();
  }
}

{
  "timeZone": "Etc/UTC",
  "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 manage installable triggers.

Run the baseline and keep its timestamps

Select runRequestTracker in the editor and click Run, authorizing the project when prompted. With the initial fixture, the log prints Marked 2 request(s) Ready. and the table updates as follows:

In the table above, T1 denotes the Date instance created by new Date() during the run; both updated rows receive that same timestamp while columns A:C remain unchanged.

Note the two new timestamps in E2 and E4, then run runRequestTracker a second time without editing the sheet. Expect No new requests found., no spreadsheet write, and all four timestamps unchanged.

Reload the spreadsheet tab. A custom Request tracker menu appears with Run update, Install hourly schedule, and Remove hourly schedule. Click Request tracker > Run update and confirm the same No new requests found. log in Executions.

Why opening the sheet does not install anything

onOpen is the only simple trigger in this project. It calls SpreadsheetApp.getUi().createMenu('Request tracker'), registers three menu items by function-name string, and attaches the menu to the open spreadsheet with addToUi(). Constructing the menu does not invoke those functions, read cells, or install a trigger.

Simple onOpen runs for users who have edit access to the spreadsheet. When a user later clicks a menu item, Apps Script runs the named function under that user's account, whereas an installed clock trigger runs under the trigger creator's account. Because runRequestTracker takes no event parameter and reads the Requests table directly, the same entrypoint works from Run in the editor, from the Request tracker menu, and from an hourly clock trigger.

Trace one run through the three files

Each runRequestTracker execution follows six steps:

1. Acquire the script lock. runRequestTracker calls tryLock(5000) before reading the sheet. If the lock is busy after 5,000 milliseconds, it logs Skipped request tracker run because another execution is running. and returns.

2. Validate the table. getTrackerSheet_() locates the bound spreadsheet and its Requests tab. readTrackerTable_() checks range dimensions before calling getValues(), validates the headers and every data row, verifies that the output range (D2:E) has no merged cells or formulas, and returns { sheet, rows }.

3. Compute next values in memory. createStatusAndTimestampRows_(table.rows, new Date()) maps each data row to a two-element [status, lastUpdated] pair: a 'New' row becomes ['Ready', now], and any other row keeps [row[3], row[4]].

4. Count transitions. filter counts rows whose status changed. If the count is 0 (including on a header-only table), the runner exits without calling setValues().

5. Write the output rectangle and flush. Starting at row 2, column 4 (D2), setValues(nextValues) writes all Status and Last updated cells in a single call, and SpreadsheetApp.flush() applies pending changes before the lock is released.

6. Release the lock in finally. Whether the try block returns normally or throws during validation, writing, or flushing, finally releases the script lock without catching or masking the error.

Note that JSDoc annotations (@param, @returns, @typedef) aid editor type checking but perform no runtime validation; the checks in readTrackerTable_() enforce the schema at runtime.

What validation promises

The used range must fit within A1:E101 (one header row plus up to 100 data rows) and start with the exact headers Request ID, Requester, Summary, Status, and Last updated. Every data row must have non-empty Request ID, Requester, and Summary values (checked with !value, which rejects '', 0, and false), and Status must be 'New', 'Ready', 'Hold', or 'Done'. Blank statuses, lowercase 'new', or 'Waiting' throw an error that reports the 1-based spreadsheet row number (index + 2, accounting for the header row).

The output range D2:E must also be unmerged and free of formulas. Because getValues() returns a formula's evaluated result rather than its formula text, readTrackerTable_() performs one batch getFormulas() read over D2:E and also rejects any Last updated string starting with =, which Range.setValues() would parse as a formula.

What batching preserves and rewrites

Each run performs one getValues() read, one getFormulas() read on D2:E, and at most one setValues() write, following Google's batch operations guidance.

When at least one row is New, setValues() rewrites the entire D2:E data rectangle, writing new values for New rows and writing back the existing [status, lastUpdated] pairs for Ready, Hold, and Done rows (including hidden or filtered rows). Rejecting formulas in D2:E beforehand ensures that writing back evaluated values never overwrites a formula with a static value.

If the sheet exceeds 101 rows, readTrackerTable_() throws before reading cell values rather than truncating the table. See Apps Script quotas for execution time and trigger runtime limits.

Make a small change, then restore it

With no trigger installed, append a fifth request in row 6:

Run runRequestTracker. It logs No new requests found. and leaves row 6 as Done with its existing timestamp. Next, change D6 to New and run runRequestTracker again. It logs Marked 1 request(s) Ready. and updates D6:E6 to Ready with a fresh timestamp while rows 2 through 5 retain their existing timestamps.

Setting a row's status back to New is the explicit signal to reprocess and re-timestamp that row; editing a Ready row's Summary cell alone does not trigger an update.

Delete the test row by clearing A6:E6 completely so no partially blank row remains at the bottom of the table.

See an invalid row stop an earlier valid row

1. Set D2 to New and clear E2.

2. Change D4 from Ready to lowercase new, keeping E4 unchanged.

3. Run runRequestTracker. Validation throws Request row 4 has an unsupported status: new. before calling setValues(), so row 2 remains New with E2 blank.

4. Restore D4 to Ready and run runRequestTracker again. Row 2 transitions to Ready with a new timestamp while row 4 retains its previous timestamp.

Decide who owns the schedule

To test scheduled execution, use the account that should own the trigger:

1. Run installRequestTrackerTrigger (or choose Request tracker > Install hourly schedule). It validates the table without modifying cells and logs Installed request tracker trigger: with the new trigger UID.

2. Open Triggers and confirm that one hourly time-driven trigger is registered for runRequestTracker.

3. Run installRequestTrackerTrigger a second time. Expect Request tracker trigger already installed: with the same UID and no duplicate trigger.

As in Install time-driven triggers safely, the installer, runner, and remover share LockService.getScriptLock(). installRequestTrackerTrigger inspects ScriptApp.getProjectTriggers() for the current account, preserves a single existing clock trigger for runRequestTracker, and throws if it finds multiple clock triggers or a non-clock trigger for that handler. Because Trigger does not expose a clock's interval and getProjectTriggers() lists only the current user's triggers, inspect Triggers if the cadence needs verification or if create() fails with an uncertain error.

Distinguish repetition from a transaction

Once a run completes on valid input, subsequent runs against the unchanged sheet are idempotent: filter finds zero New rows and skips setValues(), preserving existing transition timestamps. Keep four boundaries in mind:

• Overlapping runs of this script. A run that acquires the script lock reads the sheet after the previous holder's SpreadsheetApp.flush() call completes. A run that fails tryLock(5000) logs Skipped request tracker run because another execution is running. and exits without queuing a retry.

• Concurrent human edits. Manual edits in the spreadsheet UI do not wait for LockService. If a person edits D2:E5 after getValues() runs, setValues() will overwrite that edit with the values read at the start of the run.

• Uncertain write or flush failures. finally releases the lock without rolling back changes already written to the sheet.

• External actions. A Ready status in Sheets or a property in PropertiesService is a local record, not a distributed transaction with Gmail, Calendar, or an external API.

Stop and recover

Run removeRequestTrackerTriggers (or choose Request tracker > Remove hourly schedule) under the account that installed the trigger. The remover skips table validation so you can remove a schedule even when the sheet has validation errors. Expect Removed 1 request tracker trigger(s). on the first run and 0 on a repeat run, and confirm the entry is gone in Triggers.

To reset the sheet to its initial fixture after removing the trigger, clear A6:E6 if present, set D2 and D4 to New, clear E2 and E4, and leave row 3 (Hold) and row 5 (Done) with their original timestamps.

Next, choose and copy Drive files. Later chapters, including menus and interfaces and debug executions and recover from limits, use their own standalone or bound fixtures.