Build a Sheets request tracker

Build a bound Sheets request tracker that changes New rows to Ready, records when each row changed in a Last updated column, and adds a custom spreadsheet menu. Installing an hourly trigger is optional. You need a new spreadsheet, the batch write from Read and write Sheets in batches, and the trigger installer pattern from Install and remove time-driven triggers.

Create a disposable tracker

Create a new spreadsheet and bound script project for sheets-request-tracker. This table differs from the previous chapter's in three ways. It has five columns (Request ID, Requester, Summary, Status, Last updated). The script updates only rows with status New, and a blank status is an error. Request IDs start at R-300. If the previous chapter's hourly trigger is still installed, remove it in that 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 tracker and note its timestamps

Select runRequestTracker in the Apps Script editor and click Run. Authorize the project when prompted. With the sample data, the log prints Marked 2 request(s) Ready. and the table changes as follows:

Both updated rows get the same T1, because new Date() runs once per run. Columns A:C are 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 call those functions, read cells, or install a trigger.

The simple onOpen trigger runs when a user with edit access opens the spreadsheet. When a user clicks a menu item, Apps Script runs the named function as that user. An installed time-driven trigger runs as the account that created the trigger. runRequestTracker takes no event parameter and reads the Requests table directly, so the same function works from Run in the editor, from the Request tracker menu, and from an hourly time-driven 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, and verifies that the output range (D2:E) has no merged cells or formulas. It then 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 changed rows. 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. The finally block does not catch the error, so the error still ends the run.

JSDoc comments (@param, @returns, @typedef) help the editor check types while you write code. They do nothing at run time. At run time, readTrackerTable_() does the checking.

What the table must contain

The script checks the table before writing:

• The used range must fit within A1:E101: one header row and up to 100 data rows.

• The headers must be Request ID, Requester, Summary, Status, and Last updated, in that order.

• Every data row needs non-empty Request ID, Requester, and Summary values. The !value check rejects '', 0, and false.

• Status must be 'New', 'Ready', 'Hold', or 'Done'.

A blank status, lowercase 'new', or 'Waiting' causes an error naming the spreadsheet row. The row number is index + 2 because the array starts at zero and omits the header.

The output range D2:E must also have no merged cells and no formulas. getValues() returns a formula's result, not its text, so readTrackerTable_() makes one getFormulas() read over D2:E to find formulas. It also rejects any Last updated string that starts with =, because Range.setValues() would store that text 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. It writes new values for New rows and preserves the existing [status, lastUpdated] pairs for Ready, Hold, and Done rows, including hidden or filtered rows. Because readTrackerTable_() rejects formulas in D2:E first, this write never replaces a formula with its static result.

If the sheet has more than 101 rows, readTrackerTable_() throws before reading cell values. It does not process the first 100 and skip the rest. See Apps Script quotas for execution time and trigger runtime limits.

Change a request, 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.

To process a row again and give it a new timestamp, set its status back to New. Editing only the Summary cell of a Ready row changes nothing.

Remove the test row by clearing A6:E6 completely. A partly blank row at the bottom of the table would fail validation on the next run.

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 changes to Ready with a new timestamp while row 4 retains its previous timestamp.

Decide who owns the trigger

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 and remove time-driven triggers, the installer, runner, and remover share LockService.getScriptLock(). installRequestTrackerTrigger reads ScriptApp.getProjectTriggers() for the current account. If it finds exactly one time-driven trigger for runRequestTracker, it keeps that trigger. It throws if it finds several time-driven triggers or a trigger of another kind for that handler.

Trigger does not expose a time-driven trigger's interval, and getProjectTriggers() lists only the current user's triggers. Open Triggers to check that the trigger runs every hour, or to see whether a trigger was created after create() failed with an unclear error.

What repeated runs do and do not protect

After a run completes on valid input, running again on the unchanged sheet changes nothing. The filter call finds zero New rows, so the runner skips setValues() and the existing Last updated timestamps stay as they are. The lock and this check protect you in some cases and not others:

• Overlapping runs of this script. A run that gets 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. It does not retry later.

• A person editing at the same time. Edits in the spreadsheet UI do not wait for LockService. If a person edits D2:E5 after getValues() runs, setValues() overwrites that edit with the values read at the start of the run.

• A write or flush that fails. finally releases the lock. It does not undo changes already written to the sheet.

• Actions outside the sheet. A Ready status in Sheets, or a value saved with PropertiesService, is stored separately from Gmail, Calendar, or an external API. It cannot undo an action there, and the two can disagree after a failure. See Mark each row after an external action.

Stop and recover

Run removeRequestTrackerTriggers (or choose Request tracker > Remove hourly schedule) under the account that installed the trigger. The remover does not validate the table, so it works 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 sample data 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 sample projects.