Automating Static Timestamps in Google Sheets via Apps Script

The Advanced Use-Case: The Volatility Trap

In high-performance project management and CRM systems built on Google Sheets, tracking when a status changes is critical for calculating lead times and accountability.

Most users attempt to use the =IF(A2="Done", NOW(), "") formula. However, Google Sheets recalculates volatile functions like NOW() and TODAY() every time any cell is edited or the file is reopened. This causes your “historical” timestamps to update to the current time, rendering your data useless for auditing. To solve this, we must move beyond formulas into the realm of event-driven automation.

The “Aha!” Solution: The onEdit Trigger

The solution lies in the Google Apps Script onEdit(e) simple trigger. Unlike formulas, a script can perform a “write-once” operation.

When the script detects a specific change (e.g., a “Status” column changing to “Complete”), it fetches the current system time and hard-codes that value into a target cell as a static string. Because it is a hard-coded value and not a formula, it will never change unless manually overwritten or triggered again.

Step-by-Step Implementation

1. Prepare Your Spreadsheet

Ensure your data is structured with a clear “Trigger Column” and a “Timestamp Column.”

Column A (Trigger)	Column B (Static Timestamp)
Status	Completion Date
In Progress	[Script will write here]

2. Access the Script Editor

1. Open your Google Sheet.
2. Navigate to Extensions > Apps Script.
3. Delete any code in the editor and replace it with the script below.

3. The Script Logic

Copy and paste this optimized script:

/**
 * Advanced Static Timestamp Automation
 * Author: Workflow Automation Expert
 */

function onEdit(e) {
  const sheetName = 'Tasks'; // CHANGE TO YOUR SHEET NAME
  const triggerColumn = 1;   // Column A
  const stampColumn = 2;     // Column B

  const range = e.range;
  const sheet = range.getSheet();

  // 1. Guard Clauses: Only run if correct sheet and column are edited
  if (sheet.getName() !== sheetName || range.getColumn() !== triggerColumn) {
    return;
  }

  // 2. Logic: If cell is NOT empty, add timestamp. If cleared, clear timestamp.
  const timestampCell = sheet.getRange(range.getRow(), stampColumn);

  if (range.getValue() !== "") {
    // Only add timestamp if the target cell is currently empty (prevents overwriting)
    if (timestampCell.getValue() === "") {
      timestampCell.setValue(new Date()).setNumberFormat("yyyy-mm-dd HH:mm");
    }
  } else {
    // Optional: Clear timestamp if the trigger status is deleted
    timestampCell.clearContent();
  }
}

4. Configuring the Event Object (e)

The script uses the e parameter, which represents the Event Object. This is a JSON-like structure passed by Google Sheets automatically when an edit occurs.

Property	Description
e.range	The Range object representing the cell(s) edited.
e.value	The new value of the cell after the edit.
e.oldValue	The value of the cell before the edit (useful for audit logs).
e.source	The Spreadsheet object the edit occurred in.

Edge Cases & Limitations

• Mobile App Support: Simple onEdit triggers run on mobile, but they can occasionally experience latency depending on the device’s connection.
• Bulk Edits: If you copy-paste values into 100 cells at once, the onEdit trigger only recognizes the “top-left” cell of the range in its simple form. For bulk updates, a for loop must be implemented to iterate through e.range.
• Permissions: Simple triggers cannot access services that require authentication (like sending an email). If you need to send an email when a timestamp is created, you must delete the onEdit function and create an Installable Trigger via the Triggers (Clock Icon) menu in the Apps Script sidebar.
• Formulas as Triggers: Scripts do not “see” formula changes. If Column A changes because of a formula like =VLOOKUP(), the onEdit trigger will not fire. It only responds to manual user edits.