Install and remove time-driven triggers

Mark blank request statuses as Ready on an hourly schedule, verify that running the installer twice keeps a single trigger, and remove the trigger when you finish. You need a new spreadsheet, the batch update pattern from Read and write Sheets in batches, and the four-row sample table below. You can test the handler with Run in the Apps Script editor before the hourly trigger starts 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 when a person edits the spreadsheet in the UI. A setValues() write from a script or an API call does not fire them. Simple onEdit queues at most two trigger events. See simple triggers and installable-trigger restrictions.

Start with a separate disposable spreadsheet

Create a new spreadsheet for safe-request-status-trigger. Keep each chapter's project in its own spreadsheet, because all .gs files in a project share one global scope and an installed trigger stays attached to its project until you remove it.

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 (getRequestSheet_) hides the helper from the editor's function menu and the trigger picker. Other files in the project can still call it.

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. Authorize the project when prompted. With the sample data, expect these changes:

The execution log prints Marked 2 request(s) Ready. and columns A:C are unchanged. Running runScheduledRequestUpdate a second time logs No blank request statuses found. and writes nothing. A table with only a header row logs No request rows to update.

runScheduledRequestUpdate reads the used range once with getValues(), skips the header row with slice(1), sets each blank status (row[3] === '') to Ready in memory, counts the changed rows, and writes the status column back with one setValues() call.

Before computing those updates, readAndValidateRequests_ checks the sheet and throws if any check fails:

• The script must be bound to a spreadsheet with a Requests tab. When a trigger runs a bound script, getActiveSpreadsheet() returns that spreadsheet no matter 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 the row and column counts before calling getValues(), so a larger sheet is rejected before any values are read. The cap keeps the example's run time short.

• The header row must match the four labels exactly, and every row, including filtered or hidden rows, must have non-empty Request ID, Requester, and Summary values. The helper checks these with !value, which rejects empty strings, 0, and false.

• The status column (D2:D) must have no merged cells, no formulas (checked with one getFormulas() read), and no text starting with = that setValues() would store as a formula.

The status test itself, row[3] === '', matches only an empty string, so runScheduledRequestUpdate leaves non-empty statuses such as Hold or Waiting unchanged.

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 gives the authorization level. e.user may be empty, depending on the trigger type and domain settings. Installable triggers include e.triggerUid, which identifies the saved trigger, not one firing of it.

Clicking Run in the editor passes undefined instead of an event object. Because runScheduledRequestUpdate reads the Requests table without using an event parameter, it works from both the editor and the time-driven trigger.

Install one hourly trigger

An installable trigger runs as the account that created it. Transferring ownership of the spreadsheet in Drive does not transfer its triggers. Sign in as the account that should own the trigger, then:

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 it creates the trigger. Validation does not modify 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 time-driven trigger in place.

ScriptApp.newTrigger('runScheduledRequestUpdate').timeBased().everyHours(1).create() builds and saves the schedule. The handler name must match a top-level function in the project (see Script function not found). Time-driven triggers fire at approximate intervals, not at exact wall-clock seconds. Builders that name a time of day (atHour, nearMinute) use the script time zone unless you set another with inTimezone. See ClockTriggerBuilder.

Before it inspects or creates triggers, the installer acquires the script lock that the runner and remover also use. It then calls ScriptApp.getProjectTriggers() to list the current user's installable triggers in this project. Finally, it keeps those with getHandlerFunction() === 'runScheduledRequestUpdate' and acts on what it finds:

The Trigger class exposes the handler name, event type, event source, and UID. It has no method that returns a time-driven trigger's interval, and getTriggerSourceId() returns null for those triggers. Check the interval in Triggers, because the installer cannot confirm it is hourly.

getProjectTriggers() lists only the triggers owned by the account running the code, so the installer prevents duplicates only within one account. A collaborator who runs installRequestStatusTrigger under their own account creates a second trigger.

If create() fails with an error that does not say whether the trigger was saved, check Triggers before running the installer again.

Interpret overlaps and failures

The runner, installer, and remover all call LockService.getScriptLock().tryLock(5000). If a run cannot get the lock within 5,000 milliseconds, the runner logs Skipped request update because another execution is running. and returns without reading or writing the sheet. The installer and remover throw instead, so you see the failure when you install or remove the trigger.

Inside the try block, runScheduledRequestUpdate calls SpreadsheetApp.flush() right after setValues(), so pending spreadsheet writes are applied before finally releases the lock. The Lock reference recommends this order.

runScheduledRequestUpdate changes only cells where Status is '', so once a row is Ready, later runs leave it alone. If an unattended run fails, open 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 trigger runtime per day.

Remove the trigger and restore the sample data

Run removeRequestStatusTriggers under the account that installed the trigger. It deletes only the current user's time-driven triggers for runScheduledRequestUpdate in this project and leaves other handlers and triggers of other kinds alone. The log reports 1 removed. Open Triggers to confirm the entry is gone, then run removeRequestStatusTriggers again. This time it reports 0.

With the trigger 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, so D2 stays 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.