Google Sheets onEdit Trigger Recipes for Apps Script

The onEdit trigger is one of the most useful tools in Apps Script — it fires every time a cell is edited, letting you react instantly to changes. Here are practical recipes you can drop straight into your projects.

The event object

Every onEdit function receives an event object e with useful properties:

function onEdit(e) {
  const range     = e.range;            // The edited range
  const value     = e.value;            // New value (single cell edits only)
  const oldValue  = e.oldValue;         // Previous value
  const sheet     = range.getSheet();   // Sheet where edit occurred
  const row       = range.getRow();
  const col       = range.getColumn();
  const a1        = range.getA1Notation();

  Logger.log(`Edited ${a1} on "${sheet.getName()}": "${oldValue}" → "${value}"`);
}

Recipe 1: Auto-timestamp when a row is edited

function onEdit(e) {
  const sheet = e.range.getSheet();
  const editedCol = e.range.getColumn();
  const editedRow = e.range.getRow();
  const timestampCol = 10; // Column J

  // Only timestamp edits in columns A–I, skip row 1 (header)
  if (editedRow === 1 || editedCol >= timestampCol) return;

  sheet.getRange(editedRow, timestampCol).setValue(new Date());
}

Recipe 2: React to a specific dropdown change

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Tasks') return;

  const statusCol = 4; // Column D
  if (e.range.getColumn() !== statusCol) return;

  const row = e.range.getRow();
  if (row === 1) return; // Skip header

  if (e.value === 'Done') {
    // Highlight the row green
    sheet.getRange(row, 1, 1, sheet.getLastColumn())
         .setBackground('#d9ead3');

    // Log completion date in column E
    sheet.getRange(row, 5).setValue(new Date());
  } else if (e.value === 'In Progress') {
    sheet.getRange(row, 1, 1, sheet.getLastColumn())
         .setBackground('#fff2cc');
  } else {
    sheet.getRange(row, 1, 1, sheet.getLastColumn())
         .setBackground(null);
  }
}

Recipe 3: Validate input and revert if invalid

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Budget') return;

  const amountCol = 3; // Column C
  if (e.range.getColumn() !== amountCol || e.range.getRow() === 1) return;

  const value = parseFloat(e.value);

  if (isNaN(value) || value < 0) {
    e.range.setValue(e.oldValue || '');
    SpreadsheetApp.getUi().alert('Please enter a positive number in the Amount column.');
  }
}

Recipe 4: Auto-capitalise text input

function onEdit(e) {
  const nameColumns = [1, 2]; // Columns A and B
  if (!nameColumns.includes(e.range.getColumn())) return;
  if (typeof e.value !== 'string' || !e.value) return;

  // Capitalise first letter of each word
  const capitalised = e.value.replace(/\b\w/g, c => c.toUpperCase());
  if (capitalised !== e.value) {
    e.range.setValue(capitalised);
  }
}

Recipe 5: Mirror edits to another sheet

function onEdit(e) {
  const sourceSheet = 'Live Data';
  const targetSheet = 'Mirror';

  if (e.range.getSheet().getName() !== sourceSheet) return;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mirror = ss.getSheetByName(targetSheet);
  if (!mirror) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();
  mirror.getRange(row, col).setValue(e.value);
}

Recipe 6: Send an alert for high-value entries (installable trigger)

Simple triggers can't send email — use an installable trigger for this:

function onEditInstallable(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Sales') return;
  if (e.range.getColumn() !== 4) return; // Column D = Amount

  const amount = parseFloat(e.value);
  if (amount > 10000) {
    GmailApp.sendEmail(
      '[email protected]',
      '🚨 Large Sale Entered',
      `A sale of $${amount} was entered in row ${e.range.getRow()} by ${Session.getActiveUser().getEmail()}.`
    );
  }
}

function createInstallableOnEdit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('onEditInstallable')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

Tips

Always check e.range.getSheet().getName() at the top of your function — otherwise your trigger runs on every sheet edit.
Simple onEdit runs as the editing user and cannot send emails or access external services. Use an installable trigger for those actions.
e.value is only set for single-cell edits. For range pastes, it will be undefined — use e.range.getValues() instead.
Avoid heavy operations (API calls, large reads) in onEdit — it should respond near-instantly to feel snappy.