In our previous session (Episode 21), we explored onOpen(e) to automatically add a custom menu as soon as a spreadsheet is opened.
For Episode 22, we are diving deep into onEdit(e), a built-in trigger that automatically executes your Google Apps Script the moment a cell is edited in Google Sheets!
“I want a timestamp automatically placed in the adjacent cell whenever someone types a value and presses Enter.”
“I want to trigger an automated validation check whenever specific columns are modified to prevent input mistakes.”
onEdit(e) is the exact tool to create these workflows where your sheet actively assists your team without requiring anyone to click a manual button.
To keep things crystal clear even for absolute beginners, this tutorial covers the mystery behind the event object e, a hands-on project tracking Sapporo snowfall records, and solutions to the common pitfalls beginners almost always run into!
1. What Is onEdit(e) in Google Apps Script?
onEdit(e) is a built-in feature in GAS known as a Simple Trigger.
A trigger does exactly what the name implies: when a specific event happens, it automatically pulls the trigger to execute your code.
Eliminate Even the Need to Click Buttons!
Throughout earlier lessons in this series, running a script meant clicking “Run” in the Apps Script editor or clicking a custom drawing button placed on the sheet.
With onEdit(e), Google continuously monitors your spreadsheet in the background. The split second a user manually alters a cell and presses Enter, GAS wakes up and executes immediately behind the scenes.
2. Demystifying the Event Object e
When drafting an onEdit(e) function, the single parameter inside the parentheses—(e)—is usually where learners first pause.
function onEdit(e) {
// What in the world is this "e" inside the parentheses...?
}
In programming terminology, this e stands for the Event Object. If that sounds intimidating, think of it simply as a care package packed with details sent straight from Google!
What Is Inside the Care Package e?
Whenever an edit occurs, Google compiles a detailed report containing who changed which cell on which sheet, including the old value and the updated value. All of this info is packed inside e and delivered directly to your function.
By inspecting this object, our script can accurately identify: “Row 2 in Column A was just modified!”
Key Properties of the Event Object (e)
| Property Name | What Data Does It Contain? | Example Value |
e.range |
The edited cell itself (Range object) | Cell A2, Cell B5, etc. |
e.value |
The new value after editing (string) | "15" (new value) |
e.oldValue |
The previous value before editing (string) | "10" (previous value) |
e.source |
The entire spreadsheet edited (Spreadsheet) | Spreadsheet instance |
e.user |
The email of the editor (*subject to permissions) | user@example.com |
The most crucial properties are e.range and e.value. Working with these gives you precise control over finding out which cell was edited and what it was updated to.
3. Hands-On: Automatically Record Timestamps When Snowfall Data Updates
Let’s put this into practice with real code!
Living through a Sapporo winter means tracking snowfall depth is an everyday ritual! We will build a “Sapporo Snowfall Log” where updating Column B (Snowfall Depth) automatically inserts the current timestamp into Column C (Last Updated).
STEP 1: Prepare the Google Sheet
- Create a new Google Sheet and name the spreadsheet file “Sapporo Snowfall Check”.
- Rename the active tab at the bottom to “Snowfall Log”.
- Enter the header labels in row 1:
- A1: Observation Area (e.g., Chuo Ward, Kita Ward, Teine Ward)
- B1: Current Snowfall (cm)
- C1: Last Updated
- Fill in sample data in row 2:
- A2: Chuo Ward
- B2: 15
- C2: (Leave blank for now)
STEP 2: Add Code to the Apps Script Editor
- In your spreadsheet menu, navigate to Extensions > Apps Script.
- Erase the boilerplate
function myFunction() { ... }so the editor is completely empty. - Paste the following code into the editor:
/**
* Executes automatically whenever a cell is manually edited.
* @param {Object} e - Event object containing edit details from Google.
*/
function onEdit(e) {
// ---------------------------------------------------
// 1. Guard Clause: Immediately ignore irrelevant edits
// ---------------------------------------------------
// Get the sheet where the edit took place
var sheet = e.range.getSheet();
// Exit early if the edited sheet is not "Snowfall Log"
if (sheet.getName() !== "Snowfall Log") {
return;
}
// Get the row and column numbers of the edited cell
var editedRow = e.range.getRow(); // e.g., 2 if row 2 was edited
var editedColumn = e.range.getColumn(); // e.g., 2 if Column B was edited
// ---------------------------------------------------
// 2. Validate target cell and run the main update
// ---------------------------------------------------
// Trigger only if Column B (Col 2) is edited, excluding the header (Row >= 2)
if (editedColumn === 2 && editedRow >= 2) {
// Target Column C (Column 3) on the same row
var targetRange = sheet.getRange(editedRow, 3);
// Automatically write current timestamp (JavaScript Date object) into Column C!
targetRange.setValue(new Date());
}
}
- Click the Save icon (floppy disk) or press Ctrl + S / Cmd + S to save your script.
STEP 3: Detailed Breakdown of the Code
Let’s review the core mechanics step-by-step so you understand every line.
① What Is a Guard Clause?
if (sheet.getName() !== "Snowfall Log") {
return;
}
By default, onEdit(e) triggers whenever any cell anywhere in the workbook is modified. Allowing complete execution on unrelated sheets or columns slows down your workbook performance. A guard clause acts like a security guard: if the active sheet is not the intended one, return; instantly halts execution.
② Determining Cell Coordinates Numerically
var editedRow = e.range.getRow();
var editedColumn = e.range.getColumn();
e.range.getRow() yields the vertical row index, while e.range.getColumn() returns the column as an integer (A=1, B=2, C=3, etc.). These methods tell the script: “Row 2 of Column B (Column 2) was just updated!”
③ Referencing the Adjacent Cell on the Same Row
var targetRange = sheet.getRange(editedRow, 3);
This uses the sheet.getRange(row, column) coordinate technique we covered back in Episode 4!
By reusing editedRow, edits on row 2 select cell C2 (row 2, column 3), edits on row 5 select cell C5, and so on, keeping your target dynamically aligned with the modified row.
STEP 4: Test Your Script in Action!
Return to your spreadsheet tab to test out the trigger.
- Change the snowfall value in cell B2 from “15” to “20”, then hit Enter.
- Right away, after a brief moment, cell C2 automatically populates with the current timestamp like
2026/09/22 10:41:55!
Without clicking any script execution buttons, your sheet reacted purely to your cell edit. That is the power of automated event triggers!
4. Practical Real-World Techniques with onEdit(e)
Once you feel comfortable with the basics, check out these practical adaptations commonly applied in production spreadsheets.
Technique 1: Comparing Previous and Updated Values
The event object allows you to compare values before and after an edit.
function onEdit(e) {
var oldValue = e.oldValue; // Value prior to edit
var newValue = e.value; // Value after edit
// Example: log or flag sudden jumps from "10" to "50"
if (oldValue && newValue) {
Logger.log("Value changed from " + oldValue + " to " + newValue);
}
}
Technique 2: Dynamically Change Background Color on Specific Inputs
You can also highlight cells dynamically, such as coloring a cell red when heavy snowfall (over 50 cm) is recorded.
function onEdit(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== "Snowfall Log") return;
var editedRow = e.range.getRow();
var editedColumn = e.range.getColumn();
// Target Column B starting from row 2
if (editedColumn === 2 && editedRow >= 2) {
var amount = Number(e.value); // Cast string input to number
if (amount >= 50) {
// Highlight cell in soft red if 50 cm or above
e.range.setBackground("#fce8e6");
} else {
// Clear background color if below 50 cm
e.range.setBackground(null);
}
}
}
5. 5 Common onEdit(e) Pitfalls and How to Fix Them
While onEdit(e) is super handy, it operates under distinct rules that catch almost every beginner off guard. Keep these traps in mind!
Pitfall 1: Clicking “Run” in the Script Editor Throws an Error!
Selecting onEdit in the Apps Script toolbar and clicking “Run” leads directly to an error:
TypeError: Cannot read properties of undefined (reading 'range')
[Why does this happen?]
Clicking “Run” inside the code editor did not originate from editing a cell. Because no cell was changed, Google passes no event object; e remains undefined, causing e.range to crash.
[Solution]
You cannot test onEdit(e) using the editor’s Run button! Always test by manually editing a cell directly in your spreadsheet.
Pitfall 2: Formula Calculations (e.g., =SUM()) Do Not Trigger onEdit
If cell A1 contains =SUM(B1:B10), modifying values in column B updates cell A1, but onEdit will not register cell A1 as edited.
[Why does this happen?]
onEdit responds solely to manual human keystrokes, direct cell replacements, or deletions. Automatic recalculations via spreadsheet formulas and updates via programmatic scripts (like setValue()) do not trigger it.
Pitfall 3: Sending Emails or Calling External APIs Fails
Calling services like GmailApp.sendEmail() or external webhook requests inside onEdit will fail or be silently ignored.
[Why does this happen?]
onEdit operates under Simple Trigger constraints with limited privileges. To prevent malicious scripts from sending spam emails without consent, operations requiring user authorization (such as sending emails, external HTTP fetches, and Drive access) are blocked.
[Solution]
When email notifications or webhook integrations are needed, configure an Installable Trigger (ScriptApp.newTrigger()), which we will cover in Episode 23!
Pitfall 4: e.value Returns Undefined During Bulk Edits or Pastes
Updating a single individual cell populates e.value. However, dragging across multiple cells to delete them or pasting data across a cell range leaves e.value as undefined.
[Solution]
For production-grade scripts designed to handle bulk inputs, avoid relying exclusively on e.value. Instead, fetch the entire updated area using e.range.getValues() as a 2D array.
Pitfall 5: Omitting Guard Clauses Drastically Slows Down Your Sheet
Writing heavy logic without conditional if statements means GAS will run full computational routines even when users touch completely unrelated cells like A1 or D10, creating significant input lag across your workbook.
[Solution]
Make it an ingrained practice to inspect the sheet name and column index right at the top of your function, immediately executing return; whenever the edit falls outside your target area!
6. Summary & What’s Next
In this guide, we covered how to leverage onEdit(e) to run automated logic on spreadsheet edits.
onEdit(e)is a Simple Trigger executing immediately upon manual cell edits- The parameter
e(Event Object) carries critical details about the modification - Placing Guard Clauses at the beginning protects workbook responsiveness
- Scripts using
onEdit(e)must be tested from the spreadsheet, not the editor’s Run button - Privileged actions like sending emails require Installable Triggers instead
Setting up automatic cell-level reactions is one of the most satisfying milestones when learning Apps Script!
As highlighted in Pitfall 3, workflows requiring scheduled executions (like running daily at 7:00 AM) or sending automated notifications to Gmail or Slack hit the security boundaries of Simple Triggers.
In our next tutorial (Episode 23), we will overcome these barriers using ScriptApp.newTrigger() (Installable Triggers). That is where Google Apps Script automation truly unlocks its full capabilities. Stay tuned!
