Auto-Timestamp Rows in Google Sheets with Apps Script (When Status Changes)

https://github.com/lsvekis/Apps-Script-Code-Snippets

If you use Google Sheets for workflows—submissions, approvals, task tracking, content pipelines—one thing always comes up:

“When did this actually happen?”

You can add timestamps manually, but:

• People forget
• People overwrite them
• They aren’t consistent

In this tutorial, you’ll create an Apps Script that automatically:

✅ Adds a timestamp when a row becomes Submitted / Complete / Approved
✅ Only sets the timestamp once (optional)
✅ Can also clear the timestamp if you revert status (optional)
✅ Works instantly on edit

---

Use Cases

This pattern is perfect for:

• Assignment submissions (“Submitted” → timestamp)
• Approval workflows (“Approved” → timestamp)
• Support ticket trackers (“Closed” → timestamp)
• Content publishing (“Ready” → timestamp)
• Form response processing (“Processed” → timestamp)

---

Example Sheet Layout

A	B	C	D	E
ID	Name	Status	Timestamp	Notes
1001	Sam	In Progress
1002	Alex	Submitted	2026-01-05 09:15

When Status changes to Submitted, the script writes the current date/time into Timestamp.

---

Step 1: Open Apps Script

1. Open your Google Sheet
2. Go to Extensions → Apps Script
3. Paste in the code below
4. Save

---

Step 2: Full Apps Script Code

/**
 * Auto-Timestamp for Google Sheets
 * - When a "Status" cell changes to a target value, write a timestamp in another column.
 * - Optionally set timestamp only once, and/or clear timestamp if status changes away.
 */

// ============ CONFIG ============
const CONFIG = {
  sheetNames: [],          // [] = all sheets, or ["Tracker", "Submissions"]
  headerRows: 1,

  statusColumn: 3,         // e.g. Column C = 3
  timestampColumn: 4,      // e.g. Column D = 4

  // Trigger values that should create a timestamp (case-insensitive)
  triggerStatuses: ["submitted", "complete", "approved"],

  // If true: only set timestamp if timestamp cell is empty
  setOnce: true,

  // If true: clearing/reverting status away from trigger will clear timestamp
  clearOnRevert: false,

  // Timestamp format (uses spreadsheet timezone)
  // Examples:
  // "yyyy-MM-dd HH:mm"
  // "MMM d, yyyy h:mm a"
  timestampFormat: "yyyy-MM-dd HH:mm"
};

// ============ TRIGGER ============
function onEdit(e) {
  try {
    if (!e || !e.range) return;

    const range = e.range;
    const sheet = range.getSheet();
    const row = range.getRow();
    const col = range.getColumn();

    // single-cell edits only
    if (range.getNumRows() !== 1 || range.getNumColumns() !== 1) return;

    // optional sheet filter
    if (CONFIG.sheetNames.length > 0 && !CONFIG.sheetNames.includes(sheet.getName())) return;

    // ignore header rows
    if (row <= CONFIG.headerRows) return;

    // only watch status column
    if (col !== CONFIG.statusColumn) return;

    const statusRaw = range.getValue();
    const status = String(statusRaw ?? "").trim().toLowerCase();

    const tsCell = sheet.getRange(row, CONFIG.timestampColumn);
    const currentTs = tsCell.getValue();

    const isTrigger = CONFIG.triggerStatuses.includes(status);

    if (isTrigger) {
      if (CONFIG.setOnce && currentTs) return; // don't overwrite existing timestamp

      const now = new Date();
      const tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
      const formatted = Utilities.formatDate(now, tz, CONFIG.timestampFormat);

      // write as a display string (stable + readable)
      tsCell.setValue(formatted);

    } else {
      // not a trigger status
      if (CONFIG.clearOnRevert && currentTs) {
        tsCell.clearContent();
      }
    }
  } catch (err) {
    // Logger.log(err); // optional
  }
}

---

Customization Ideas

1) Only Trigger on “Submitted”

triggerStatuses: ["submitted"],

2) Allow Timestamp to Update Every Time

setOnce: false,

3) Clear Timestamp if Status Is Reverted

clearOnRevert: true,

4) Use a Friendlier Timestamp Format

timestampFormat: "MMM d, yyyy h:mm a",

---

Installable Trigger (Recommended for Team Sheets)

Simple onEdit works well, but for shared sheets and reliability:

1. Apps Script → Triggers (clock icon)
2. Add Trigger
3. Function: onEdit
4. Event source: From spreadsheet
5. Event type: On edit
6. Save + authorize

---

Common Enhancements

If you want to level this up, you can also:

• Add a second timestamp column (“Created at” and “Completed at”)
• Log status changes to an “Audit Log” sheet (who changed what + when)
• Send an email/Slack notification when status is Approved
• Lock the row automatically after timestamp is written (combine with the earlier script)