onEdit in Apps Script: How It Works, Use Cases

onEdit is a function that Google Sheets runs by itself every time someone changes a cell. You write it once, with the name onEdit, and from then on it reacts to edits: stamping the date when a task is marked done, cleaning what people type, filling in a related cell, or keeping a log of who changed what. It is the most useful trigger in Apps Script, and it needs no set-up at all.

This post explains how onEdit works, what the event object e tells you, and five practical use cases. It also covers the traps: pasting many cells, what does not trigger it, and when you need an installable trigger instead. If you are new to Apps Script, start with Google Apps Script for Beginners: A Simple Intro.

How onEdit works

onEdit(e) is a simple trigger: a function with a reserved name that Google runs automatically when a specific event happens. For onEdit the event is “a user changes a cell value in the spreadsheet”. You just write the function, save, and it works. Because it needs no authorization, it can only do things that stay inside the file:

• It can change the spreadsheet it is bound to, but it cannot open other files.
• It cannot use services that ask for authorization, such as sending mail.
• It must finish within 30 seconds.
• It does not run if the file is opened in read-only (view or comment) mode.
• Edits made by a script (for example setValue()) or by an API request do not trigger it.

The last point is useful: your onEdit can safely write into cells without setting off itself in a loop.

The event object e

Google passes one argument to onEdit, the event object, conventionally called e. The fields you will use most are:

A quick way to see it in action is to log the fields. Here is a simple sheet where people track tasks. We edit one cell, then another, then several cells at once:

function onEdit(e) {
  Logger.log("value: %s | oldValue: %s | cells edited: %s", e.value, e.oldValue, e.range.getNumRows() * e.range.getNumColumns());
}

Execution log (simulated)

value: Done | oldValue: In progress | cells edited: 1
value: Blocked | oldValue: In progress | cells edited: 1
value: undefined | oldValue: undefined | cells edited: 3

Notice the last line. When several cells change together (for example by pasting), e.value and e.oldValue are undefined. Keep that in mind, we come back to it below.

Guard clauses come first

onEdit runs on every edit, in every sheet of the file. If you do not check where the edit happened, your code will run in the wrong place. Almost every real onEdit starts with a few lines that return early when the edit is not the one you care about:

1. Which sheet? e.range.getSheet().getName() === "Tasks"
2. Which column? e.range.getColumn() === 2
3. Not the header row? e.range.getRow() > 1
4. Which value? e.value === "Done"

The guards also keep the trigger fast, so it stays far from the 30-second limit. All the examples below use them.

Use case 1: timestamp a status change

When the Status column (B) of the Tasks sheet becomes “Done”, write the date into column C. Any other value clears it. The edit of column D in the demo is ignored by the guards:

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Tasks") return;
  if (e.range.getColumn() !== 2) return;
  if (e.range.getRow() === 1) return;
  const doneOn = sheet.getRange(e.range.getRow(), 3);
  if (e.value === "Done") {
    doneOn.setValue(new Date());
  } else {
    doneOn.clearContent();
  }
}

Because setValue() is done by the script, it does not trigger onEdit again. In real Sheets the date is shown in the cell’s date format. Here it is shown as year-month-day.

Use case 2: clean what people type

Data entry is messy: extra spaces, wrong capitals. onEdit can fix the value the moment it is typed. This example trims and upper-cases the product code in column A. It reads the cell with getValue(), which is dependable for both single cells and ranges:

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Products") return;
  if (e.range.getColumn() !== 1 || e.range.getRow() === 1) return;   // only the Code column
  const text = e.range.getValue();
  if (typeof text !== "string") return;
  e.range.setValue(text.trim().toUpperCase());                       // "  ab-12 " becomes "AB-12"
}

Use case 3: fill in a related cell

When someone picks a country, fill in the currency next to it. A JavaScript object works as a small lookup table (see Hash Tables in Python: Dict and Set for DSA for the same idea in Python). An unknown country leaves the currency blank:

const CURRENCIES = { India: "INR", Japan: "JPY", Germany: "EUR" };

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Orders") return;
  if (e.range.getColumn() !== 2 || e.range.getRow() === 1) return;   // only the Country column
  const currency = CURRENCIES[e.value];
  sheet.getRange(e.range.getRow(), 3).setValue(currency || "");      // blank if the country is unknown
}

Use case 4: a change log

Who changed the rent, and what was it before? onEdit can add a line to a “Log” sheet for every edit, using e.oldValue for the previous value and e.user for the person. The user is not always available to a simple trigger, so the code has a fallback:

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() === "Log") return;                    // never log the log itself
  e.source.getSheetByName("Log").appendRow([
    Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm"),
    sheet.getName() + "!" + e.range.getA1Notation(),
    e.oldValue === undefined ? "" : e.oldValue,
    e.value === undefined ? "" : e.value,
    e.user ? e.user.getEmail() : "unknown"                  // a simple trigger cannot always tell who edited
  ]);
}

The check sheet.getName() === "Log" at the top is important. Without it, the log would try to log its own edits.

In a real Sheet the Time column looks a little different from the simulated table above. Google Sheets turns the text 2026-03-16 09:30 into a real date and time and shows it in the format of your sheet’s locale, for example 3/16/2026 9:30:00.

Use case 5: color a row

Turn a row red when a task is blocked and green when it is done, so problems stand out. setBackground() takes a color in CSS notation, and null resets the color:

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Tasks") return;
  if (e.range.getColumn() !== 2 || e.range.getRow() === 1) return;
  const row = sheet.getRange(e.range.getRow(), 1, 1, 4);     // the whole task row
  if (e.value === "Blocked") {
    row.setBackground("#f4cccc");                            // light red
  } else if (e.value === "Done") {
    row.setBackground("#d9ead3");                            // light green
  } else {
    row.setBackground(null);                                 // back to no color
  }
}

Conditional formatting in the Sheets menu does the same job with no code, so use that when a simple rule is enough. Use a script when the rule is more complicated than a formatting rule can express.

The paste trap

If someone pastes “Done” into three cells at once, e.value is undefined, as we saw. A handler that checks e.value === "Done" silently does nothing:

function onEdit(e) {
  if (e.range.getSheet().getName() !== "Tasks" || e.range.getColumn() !== 2) return;
  if (e.value === "Done") {                                  // e.value is undefined when many cells change
    e.range.getSheet().getRange(e.range.getRow(), 3).setValue(new Date());
  }
}

The fix is to read the values from e.range, which always describes the cells that changed, and loop over them:

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Tasks" || e.range.getColumn() !== 2) return;
  const firstRow = e.range.getRow();
  const values = e.range.getValues();                        // works for one cell or many
  values.forEach((row, i) => {
    const r = firstRow + i;
    if (r === 1) return;                                     // skip the header
    if (row[0] === "Done") sheet.getRange(r, 3).setValue(new Date());
  });
}

Make this the habit: use e.range.getValues() when several cells might change (paste, fill-down, delete), and e.value only when you know a single cell was edited.

When you need an installable trigger

A simple trigger cannot send email or use other services that need authorization. For that you need an installable edit trigger. It is like onEdit, but you create it yourself. It can use services that require authorization, and it runs as the person who created it. You can add one in the editor (the Triggers page, then Add Trigger), or with one line of code, run once:

function handleEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Requests" || e.range.getColumn() !== 3 || e.value !== "Approved") return;
  const row = sheet.getRange(e.range.getRow(), 1, 1, 3).getValues()[0];   // Request, Email, Status
  MailApp.sendEmail(row[1], "Your request was approved", "Hi, your request \"" + row[0] + "\" has been approved.");
}

// Run this once. It installs the trigger for the current spreadsheet.
function createEditTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger("handleEdit").forSpreadsheet(ss).onEdit().create();
}

Execution log (simulated)

Installed: handleEdit  <-  forSpreadsheet(ss).onEdit()
Email to asha@example.com: Your request was approved
Emails sent: 1

Approving C2 sent one email. C3 was not approved, so the guard stopped it. Three things to remember:

• Use a different function name. Call it handleEdit, not onEdit. A function named onEdit also runs as a simple trigger, so it might run twice.
• It runs under your account. The emails come from the account that created the trigger, and only that account can see the trigger.
• Mail has daily limits. Google’s quota is 100 recipients a day for a personal account and 1,500 for Google Workspace, at the time of writing. MailApp only sends mail and cannot read your inbox. Check the quotas page for current numbers.

Installable triggers also include an on change trigger for structural changes such as inserting a row or column, which onEdit does not see.

What onEdit does not catch

If you need to react to a Google Form response, use an installable form-submit trigger instead.

Testing onEdit

You cannot test onEdit by pressing Run in the editor. There is no edit, so Google passes no event, and the first line that uses e fails with a TypeError. Instead, write a small test function that builds a fake event and calls your handler:

function testOnEdit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const fakeEvent = {
    range: ss.getSheetByName("Tasks").getRange("B2"),   // the cell "edited"
    value: "Done",
    source: ss,
  };
  onEdit(fakeEvent);                                     // call your handler with a made-up event
}

Execution log (simulated)

onEdit() with no event: TypeError
testOnEdit() wrote: 2026-03-16

Or edit a cell in a copy of the sheet and look at the result and at Executions in the editor’s sidebar, which lists each run of the trigger and any error.

Common mistakes

• No guard clauses. The code runs on every edit in every sheet. Check the sheet, column and row first.
• Relying on e.value for pastes. It is undefined for multi-cell edits. Use e.range.getValues().
• Trying to send email from onEdit. Simple triggers cannot use services that need authorization. Use an installable trigger.
• Expecting script edits to fire it. setValue() from another function never triggers onEdit.
• Running onEdit from the Run button. There is no event. Use a test function with a fake event.
• Slow work inside the trigger. Do not loop over thousands of cells on every keystroke, and stay far below 30 seconds.
• An installable trigger with the same function name. Name it something else, or the function can run twice.

Try it yourself

Use the Tasks sheet. Work out each answer first, then open the solution.

1. When someone types an owner’s name in column D, make it start with capital letters and remove extra spaces around it.

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Tasks" || e.range.getColumn() !== 4 || e.range.getRow() === 1) return;
  const name = e.range.getValue();
  if (typeof name === "string") {
    e.range.setValue(name.trim().replace(/\b\w/g, c => c.toUpperCase()));   // "  asha rao" becomes "Asha Rao"
  }
}

Asha Rao

2. Keep a live count in cell F1, such as “2 of 3 done”, whenever a Status changes.

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== "Tasks" || e.range.getColumn() !== 2 || e.range.getRow() === 1) return;
  const done = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getValues().filter(r => r[0] === "Done").length;
  sheet.getRange("F1").setValue(done + " of " + (sheet.getLastRow() - 1) + " done");
}

2 of 3 done

3. Why does a handler that tests e.value === "Done" miss some rows when people paste?

4. You want an email when a request is approved. Why will it not work in a function called onEdit?

Frequently asked questions

What is onEdit in Google Apps Script?

It is a simple trigger: a function named onEdit(e) that Google Sheets runs automatically when a user changes a cell value. It gets an event object e that describes the edit.

How do I use onEdit in Google Sheets?

Open Extensions, then Apps Script, and add a function named onEdit(e) with your code. Save it, and it runs on every edit. Start with checks so that it only acts on the sheet and column you care about.

Why is onEdit not firing?

Common reasons: the change was made by a script or an API request (these never trigger it), the file was opened in view-only mode, the function name is misspelled, or a guard clause returns early. Also note that a simple trigger stops after 30 seconds.

Can onEdit send an email?

Not as a simple trigger, since sending mail needs authorization. Install an edit trigger for a differently named function, such as handleEdit, and call MailApp.sendEmail() from it.

What is the difference between a simple and an installable onEdit trigger?

A simple trigger is just a function named onEdit and works without set-up, but cannot use services that need authorization. An installable trigger is created by you, can use those services, and runs under the account that created it.

How do I know which cell was edited?

Use e.range. Its getRow(), getColumn(), getSheet() and getA1Notation() tell you where the edit happened.

Related reading

• Google Apps Script for Beginners: A Simple Intro – the basics behind this post.
• onOpen in Apps Script: How It Works, Use Cases – the other simple trigger.
• Apps Script Time Triggers: Cron for Google Sheets – run code on a schedule.
• Apps Script Deployment Types Explained – publishing your script.