Install time-driven triggers safely

Install one hourly trigger that changes blank request statuses to Ready, then remove it. The handler runs the same logic whether you click Run in the editor or let the clock schedule invoke it.

Choose what starts the function

A handler is the function Apps Script calls when an event occurs:

The next chapter uses the simple onOpen trigger to build a custom menu. For a recurring background scan, use an installable time-driven trigger.

Sheets edit triggers fire only on user edits in the spreadsheet UI, not on programmatic setValues() writes or API calls, and simple onEdit queues at most two concurrent events. See simple triggers and installable-trigger restrictions.

Start with a separate disposable spreadsheet

Create a new spreadsheet for safe-request-status-trigger. Because all .gs files in a project share one global scope and installed triggers remain attached to their project until removed, keep each chapter's project in its own spreadsheet.

1. Create a new spreadsheet and name its data tab Requests.

2. 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 four rows by four columns (A1:D4).

3. Open Extensions > Apps Script from that spreadsheet and add Code.gs and TriggerHelpers.gs. A trailing underscore on a function name (getRequestsSheet_) hides the helper from the editor's Run menu and trigger picker while keeping it callable across files in the project.

4. In Project Settings, enable Show "appsscript.json" manifest file in editor, replace appsscript.json with the manifest below, and save the project.

Request ID,Requester,Summary,Status
R-200,Alex,Review the meeting notes,
R-201,Sam,Update the project brief,Hold
R-202,Jo,Prepare a draft agenda,

const REQUEST_SHEET_NAME_ = 'Requests';
const REQUEST_HEADER_ = ['Request ID', 'Requester', 'Summary', 'Status'];
const REQUEST_MAX_ROWS_ = 100;

/**
 * Runs the request update from a time-driven trigger or manually.
 *
 * @returns {void}
 */
function runScheduledRequestUpdate() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) {
    Logger.log('Skipped request update because another execution is running.');
    return;
  }

  try {
    const sheet = getRequestSheet_();
    const values = readAndValidateRequests_(sheet);
    if (values.length === 1) {
      Logger.log('No request rows to update.');
      return;
    }

    const statuses = values.slice(1).map(function (row) {
      return [row[3] === '' ? 'Ready' : row[3]];
    });
    const changed = statuses.filter(function (status, index) {
      return status[0] !== values[index + 1][3];
    }).length;

    if (changed === 0) {
      Logger.log('No blank request statuses found.');
      return;
    }

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

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

/**
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {Array<Array<*>>}
 */
function readAndValidateRequests_(sheet) {
  const range = sheet.getDataRange();
  if (range.getNumRows() > REQUEST_MAX_ROWS_ + 1 || range.getNumColumns() > REQUEST_HEADER_.length) {
    throw new Error('Requests must fit within A1:D101 (100 request rows). Move other content to another tab.');
  }
  const values = range.getValues();
  const header = values[0];
  if (
    header.length !== REQUEST_HEADER_.length ||
    header.some(function (value, index) {
      return value !== REQUEST_HEADER_[index];
    })
  ) {
    throw new Error('Requests must use this header row: Request ID, Requester, Summary, Status.');
  }

  values.slice(1).forEach(function (row, index) {
    if (row.length !== REQUEST_HEADER_.length || !row[0] || !row[1] || !row[2]) {
      throw new Error('Request row ' + (index + 2) + ' is incomplete.');
    }
    if (typeof row[3] === 'string' && row[3].startsWith('=')) {
      throw new Error('Request row ' + (index + 2) + ' has a Status value beginning with =.');
    }
  });

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

const SCHEDULE_HANDLER_ = 'runScheduledRequestUpdate';

/**
 * Installs one hourly trigger for the request-status runner.
 * Stops on visible conflicts; preserves one existing clock trigger without changing its cadence.
 *
 * @returns {void}
 */
function installRequestStatusTrigger() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) {
    throw new Error('Could not acquire the trigger installation lock. Try again shortly.');
  }

  try {
    readAndValidateRequests_(getRequestSheet_());
    const existing = ScriptApp.getProjectTriggers().filter(function (trigger) {
      return trigger.getHandlerFunction() === SCHEDULE_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 runScheduledRequestUpdate. Review it in Triggers before installing.');
    }
    if (existing.length > 1) {
      throw new Error('Multiple request-status clock triggers exist. Review them, then run removeRequestStatusTriggers before installing again.');
    }
    if (existing.length === 1) {
      Logger.log('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(SCHEDULE_HANDLER_).timeBased().everyHours(1).create();
    Logger.log('Installed trigger: ' + trigger.getUniqueId());
  } finally {
    lock.releaseLock();
  }
}

/**
 * Removes all visible time-driven triggers for the request-status runner.
 * A different account's installable triggers are not visible or removable here.
 *
 * @returns {void}
 */
function removeRequestStatusTriggers() {
  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() === SCHEDULE_HANDLER_ &&
        trigger.getEventType() === ScriptApp.EventType.CLOCK &&
        trigger.getTriggerSource() === ScriptApp.TriggerSource.CLOCK;
    });
    triggers.forEach(function (trigger) {
      ScriptApp.deleteTrigger(trigger);
    });
    Logger.log('Removed ' + triggers.length + ' request-status 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 list, create, and delete project triggers.

Run the update before installing anything

Select runScheduledRequestUpdate in the editor and click Run, authorizing the project when prompted. With the initial fixture, expect these changes:

The execution log prints Marked 2 request(s) Ready. Running runScheduledRequestUpdate a second time logs No blank request statuses found. without writing to the sheet, while columns A:C remain unchanged. A header-only table logs No request rows to update.

After reading the used range once with getValues(), runScheduledRequestUpdate skips the header row with slice(1), substitutes 'Ready' for any blank status (row[3] === ''), counts rows whose status changed, and writes the updated status column with a single setValues() call.

Before computing those updates, the helper validates the sheet:

• The script must be bound to a spreadsheet containing a Requests tab. In a bound trigger execution, getActiveSpreadsheet() returns that container spreadsheet regardless of which browser tab is open.

• The used range must fit within A1:D101 (one header row plus up to 100 request rows). The helper checks row and column counts before calling getValues(), then verifies the exact header labels and checks that every row (including filtered or hidden rows) has non-empty Request ID, Requester, and Summary values.

• The status column (D2:D) must contain no merged cells, no formulas (checked with a single getFormulas() read), and no text strings starting with =, which setValues() would interpret as formulas.

The 100-row cap bounds the example's runtime; if the sheet exceeds 101 rows, the function throws before reading values. Required fields in columns A:C are checked with !value (which rejects empty strings, 0, and false), whereas row[3] === '' matches only an empty string and leaves non-empty statuses such as Hold or Waiting untouched.

Understand the event argument

When a trigger fires, Apps Script passes an event object as the first argument (conventionally named e) to the handler function:

e.authMode indicates the authorization level, and e.user may be empty depending on the trigger type and domain settings. Installable triggers include e.triggerUid, which identifies the saved trigger configuration rather than a single firing.

Because clicking Run in the editor passes undefined instead of an event object, runScheduledRequestUpdate takes no event parameter and reads the Requests table directly on both manual and clock-driven runs.

Install one visible clock schedule

An installable trigger runs under the account that created it, and transferring ownership of the spreadsheet in Drive does not transfer its installed triggers. Using the account that should own the schedule:

1. Run installRequestStatusTrigger.

2. Verify that the execution log prints Installed trigger: followed by the new trigger UID. The installer validates the Requests table before creating the trigger without modifying any cells.

3. Open Triggers in the left sidebar and confirm that one time-driven, hourly trigger is registered for runScheduledRequestUpdate.

4. Run installRequestStatusTrigger a second time under the same account. It logs Trigger already installed: with the existing UID and leaves the single clock trigger in place.

ScriptApp.newTrigger('runScheduledRequestUpdate').timeBased().everyHours(1).create() builds and registers the schedule. Clock triggers fire at approximate intervals rather than exact wall-clock seconds; calendar-time builders (atHour, nearMinute) use the script time zone unless overridden. See ClockTriggerBuilder.

Before inspecting or creating triggers, the installer acquires the script lock shared by the runner and remover. It then calls ScriptApp.getProjectTriggers() to list the current user's installable triggers in this project, filters by getHandlerFunction() === 'runScheduledRequestUpdate', and branches on the count:

The Trigger class exposes the handler name, event type, event source, and UID, but provides no method to read back a clock trigger's hourly interval, and returns null from getTriggerSourceId() on clock triggers. Because getProjectTriggers() lists only triggers owned by the current execution user, the script lock prevents duplicate schedules only within a single installer account; a collaborator running installRequestStatusTrigger under their own account would create a second schedule. If create() fails with an uncertain error, check Triggers before re-running the installer to see whether the trigger was already created.

Interpret overlaps and failures

The runner, installer, and remover all call LockService.getScriptLock().tryLock(5000). If a scheduled run cannot acquire the lock within 5,000 milliseconds, it logs Skipped request update because another execution is running. and returns without reading or writing the sheet, whereas the installer and remover throw an error so a failed management command is visible.

Inside the try block, runScheduledRequestUpdate calls SpreadsheetApp.flush() immediately after setValues() so pending spreadsheet writes are applied before finally releases the lock, as recommended by the Lock reference.

Because runScheduledRequestUpdate updates only cells where Status is '', repeated runs after a row reaches Ready make no further changes. If an unattended run fails, inspect Executions under the trigger owner's account. Current Apps Script quotas allow up to 20 triggers per user per script, 6 minutes per execution, and 90 minutes (consumer) or 6 hours (Google Workspace) of daily trigger runtime.

Remove the schedule and restore the exercise

Run removeRequestStatusTriggers under the account that installed the schedule. It deletes only the current user's clock triggers for runScheduledRequestUpdate in this project while leaving other handlers and non-clock triggers untouched. Open Triggers to confirm the entry is gone, then run removeRequestStatusTriggers a second time; the logged removal count should be 1 on the first run and 0 on the second.

With the schedule removed, verify how the handler treats non-blank statuses and validation errors:

1. Starting from the first-run result (D2 and D4 set to Ready, D3 set to Hold), change D3 from Hold to Waiting and run runScheduledRequestUpdate. It logs No blank request statuses found. and leaves Waiting intact.

2. Clear D3 and run runScheduledRequestUpdate again. It logs Marked 1 request(s) Ready. and sets D3 to Ready. Restore D3 to Hold.

3. Clear D2, then clear C4 (Prepare a draft agenda) and run runScheduledRequestUpdate. Validation throws Request row 4 is incomplete. before any write occurs, leaving D2 blank. Restore C4 to Prepare a draft agenda and run runScheduledRequestUpdate once to mark R-200 Ready.

4. Clear D2 and D4 while leaving D3 as Hold to return the table to its initial state.

Next, combine validation, state, and triggers into a complete Sheets request tracker.