A Contacts sheet is only as useful as the accuracy of its Email column, and onEditValidateEmail enforces that accuracy in real time by checking every new entry against a regular expression the instant it's typed.
Bad email addresses tend to surface much later, when an outreach campaign bounces or a confirmation email never arrives, by which point tracing the typo back to who entered it and when is nearly impossible without a paper trail.
This example clears any value that fails a standard email pattern check, replaces it with an explanatory note describing exactly what was rejected and when, and pops a toast so the person typing sees immediate feedback rather than silence.
You'll end up with a Contacts sheet that self-polices its Email column, catching typos the moment they happen instead of during a mail merge weeks later.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Field | Purpose |
|---|---|---|
| A | Name | Unrelated to validation, included for context only |
| B | Company | Unrelated to validation, included for context only |
| C | Watched column - every entry here is checked against the email pattern |
What it does
Any edit to column C on the Contacts sheet runs through a regular expression test. A value that doesn't look like a valid email gets cleared immediately, replaced with a note explaining the rejection, and the person editing sees a toast confirming what happened and why.
- Only column C, Email, is checked - other columns are untouched
- Rejected values are cleared, not just flagged, to keep bad data out of downstream exports
- A toast gives instant feedback so the rejection doesn't look like the entry silently vanished
Prerequisites
The regular expression used here covers the vast majority of real-world addresses but isn't a full RFC 5322 implementation, so extremely unusual but technically valid addresses could occasionally be rejected.
- A sheet named 'Contacts' with Email in column C
- An installed onEdit trigger with edit permission on the sheet
- Awareness that clearing content is destructive - there's no undo path built into the script itself
Walkthrough
e.value holds the newly typed string directly from the edit event, which avoids a second call to getRange().getValue() just to read back what was already typed - this keeps the check fast for high-frequency data entry.
When the pattern fails, clearContent() empties the cell before setNote() attaches an explanation; doing it in that order means the note is the only thing left behind, making it obvious at a glance that something was rejected there.
Edge cases
A user pasting a list of emails into column C will have each one checked independently as its own onEdit event, so a mix of valid and invalid addresses in one paste results in only the invalid ones being cleared.
- Leading or trailing whitespace around an otherwise valid email will fail the strict pattern match and get rejected
- International domain names with non-ASCII characters will fail this particular pattern and need a more permissive regex
- Clearing a cell that was already empty simply does nothing, since the guard returns early for falsy values
Testing
Type a clearly invalid string like 'not-an-email' into an Email cell and confirm it's cleared with a note attached, then type a valid address into the same cell and confirm it's accepted with the note removed.
- Test a borderline address with a plus sign, like name+test@example.com, to confirm the pattern accepts it
- Paste three emails at once, two valid and one invalid, and confirm only the invalid one is cleared
- Check that the toast text correctly references the row number that was rejected
Hardening
Right now a rejected entry is gone the moment it's cleared, with only the note as a record - anyone who wants to know what was actually typed has to read the note text rather than see the original value anywhere else.
- Log rejected values to a separate audit sheet before clearing them, preserving a full history
- Trim whitespace with value.trim() before testing, so accidental leading or trailing spaces don't cause false rejections
- Swap the regex for a more permissive pattern if your Contacts sheet needs to support international domains
Variations
The same clear-and-note pattern works for validating any structured field, not just email - phone numbers, postal codes, or product SKUs can all use a different regex plugged into the same guard-and-reject flow.
- Apply the same validation pattern to a Phone column using a digits-only regex
- Instead of clearing the value, highlight the cell red and leave it in place for manual review
- Combine with a dropdown-based validation approach for fields that should only ever contain a fixed set of values
Full code: onEditValidateEmail()
The function relies entirely on the edit event's own e.value rather than re-reading the cell, which keeps the validation fast enough to feel instant even on a busy Contacts sheet.
function onEditValidateEmail(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== 'Contacts') return;
var emailColumn = 3;
if (e.range.getColumn() !== emailColumn) return;
var row = e.range.getRow();
if (row === 1) return;
var value = e.value;
if (!value) return;
var emailPattern = /^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$/;
var cell = sheet.getRange(row, emailColumn);
if (!emailPattern.test(value)) {
cell.clearContent();
cell.setNote('Rejected "' + value + '" on ' + new Date().toLocaleString() + ' - not a valid email address.');
SpreadsheetApp.getActiveSpreadsheet().toast('Invalid email removed in row ' + row, 'Validation', 5);
return;
}
cell.clearNote();
}- Line 3: Restricts validation to the Contacts sheet so unrelated tabs aren't affected by this check.
- Line 6: Only inspects edits to column C, the Email field, ignoring Name and Company.
- Line 11: Reads the freshly typed value straight from the edit event rather than the sheet.
- Line 14: Defines the regex pattern used to judge whether a typed value looks like a real email address.
- Line 17: Runs the actual validation test against the typed value.
- Line 19: Attaches a note explaining exactly what was rejected and when, right after clearing the bad value.
- Line 20: Shows a toast so the rejection is visible immediately, not just recorded silently in a note.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before enabling email validation
- 1Email column position confirmed as column C
- 2Regex pattern tested against real examples from your own contact list
- 3Considered whether international domains need a more permissive pattern
- 4Toast message reviewed for clarity
- 5Note text confirmed to include both the rejected value and a timestamp
- 6Decided whether rejected values should be logged elsewhere before clearing
Frequently asked questions
Clearing prevents a malformed address from ever being exported or emailed to accidentally, which matters more for a Contacts sheet feeding an outreach tool than simply flagging it visually and hoping someone notices before the next export.
No - the pattern used allows the common special characters in the local part, including plus signs and dots, and handles subdomains fine since it only requires at least one dot in the domain portion.
Each cell in the pasted range fires its own onEdit event for this kind of edit, so every address is checked independently, and only the ones that fail validation get cleared - valid ones in the same paste are left untouched.
Yes - in addition to or instead of setNote, you could write the rejection reason to a dedicated 'Validation Errors' sheet, or use SpreadsheetApp.getUi().alert() for a much more disruptive but harder-to-miss warning.
Yes, the fifth argument to toast() controls how many seconds it stays visible before fading automatically - the example uses 5 seconds, which is enough time to read a short rejection message without lingering indefinitely.
It's stricter in some edge cases and more permissive in others - it's meant to catch obvious typos and malformed entries quickly, not to serve as an authoritative deliverability check, since only actually sending a message can confirm an address truly works.