onEdit Trigger – Apps Script

The onEdit(e) trigger fires automatically whenever a user edits a cell in a spreadsheet. It receives an event object e with details about what changed, which sheet it was on, and the old and new values.

Basic onEdit

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var cell = e.range;

  Logger.log("Sheet: " + sheet.getName());
  Logger.log("Cell: " + cell.getA1Notation());
  Logger.log("New value: " + e.value);
  Logger.log("Old value: " + e.oldValue);
}

Restricting to a Specific Sheet

Always check the sheet name to avoid unintended behavior on other sheets:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Sales Tracker") return;

  // Only process edits on the Sales Tracker sheet
  Logger.log("Edit on Sales Tracker: " + e.range.getA1Notation());
}

Validating the Status Column

This example watches column G (Status) and highlights the row based on the value entered — green for Closed, yellow for In Progress, red for anything else:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Sales Tracker") return;

  var editedCol = e.range.getColumn();
  var editedRow = e.range.getRow();

  if (editedCol !== 7 || editedRow < 2) return; // Column G = Status

  var status = e.value;
  var rowRange = sheet.getRange(editedRow, 1, 1, 8);

  if (status === "Closed") {
    rowRange.setBackground("#c6efce"); // Green
  } else if (status === "In Progress") {
    rowRange.setBackground("#ffeb9c"); // Yellow
  } else if (status === "Pending") {
    rowRange.setBackground("#ffc7ce"); // Red
  } else {
    rowRange.setBackground(null); // Clear
  }
}

With the sample data, editing Sarah Johnson's status to Closed turns her entire row green immediately.

Logging Changes to an Audit Sheet

Track every edit on the Sales Tracker into a separate Edit Log sheet:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Sales Tracker") return;

  var logSheet = e.source.getSheetByName("Edit Log");
  if (!logSheet) logSheet = e.source.insertSheet("Edit Log");

  var timestamp = new Date();
  var user = Session.getActiveUser().getEmail();
  var cell = e.range.getA1Notation();

  logSheet.appendRow([
    timestamp,
    user,
    cell,
    e.oldValue || "",
    e.value || ""
  ]);
}

Auto-Stamping the Date When Status is Set to Closed

When a sales rep marks a deal as Closed, automatically write today's date into column H (Date):

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Sales Tracker") return;

  var editedCol = e.range.getColumn();
  var editedRow = e.range.getRow();

  // Column G = Status
  if (editedCol === 7 && e.value === "Closed") {
    var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
    sheet.getRange(editedRow, 8).setValue(today); // Column H = Date
    Logger.log("Date stamped for row " + editedRow + ": " + today);
  }
}

Simple vs Installable onEdit

The simple onEdit function cannot send emails or access external services. If you need that, create an installable trigger:

// Run this once to create an installable onEdit trigger
function createEditTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger("onEditInstallable")
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

// This installable version CAN send emails
function onEditInstallable(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Sales Tracker") return;
  if (e.range.getColumn() !== 7 || e.value !== "Closed") return;

  var row = e.range.getRow();
  var data = sheet.getRange(row, 1, 1, 6).getValues()[0];
  var customerName = data[0];
  var customerEmail = data[1];
  var product = data[2];

  GmailApp.sendEmail(
    customerEmail,
    "Welcome to Acme Corp – Your " + product + " is Active",
    "Hi " + customerName + ",\n\nYour account is now active. Welcome aboard!\n\nBest,\nAcme Corp Team"
  );

  Logger.log("Welcome email sent to " + customerEmail);
}