Validate emails and phone numbers on entry

Northwind’s Clients sheet is filled in by whoever takes the call, often in a hurry. A missing @, a phone number with letters in it, an address pasted into the email column — none of it shows up until a mail-merge bounces or someone tries to ring a client and gets nowhere. By then the bad data has been sitting there for weeks.

This script checks email and phone cells the moment they are entered. As soon as someone types into the email or phone column, it tests the value against a sensible pattern and tints the cell red if it does not pass. The typist sees the problem straight away, while the right value is still in their head — no nightly clean-up, no bounced mail.

What you’ll need

• A Google Sheet with a tab named exactly Clients.
• A header row on that tab including columns titled exactly email and phone. Other columns can sit anywhere — the script finds these two by name.
• Edit access to the sheet, so you can add the trigger.

The script

// The tab this validation applies to.
const CLIENTS_SHEET_NAME = 'Clients';

// The headers of the columns to validate.
const EMAIL_HEADER = 'email';
const PHONE_HEADER = 'phone';

// The fill used to flag a cell that fails validation.
const INVALID_BACKGROUND = '#fde2e1';

// A pragmatic email shape: something, an @, something, a dot, something.
const EMAIL_PATTERN = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

// Phone: starts with + or a digit, then at least 6 digits, spaces,
// brackets, or hyphens.
const PHONE_PATTERN = /^[+\d][\d\s()-]{6,}$/;

/**
 * Runs on every edit to the spreadsheet. If the edited cell is in the
 * Clients email or phone column, it validates the new value and tints the
 * cell red when the value is malformed.
 *
 * @param {Object} e The edit event Sheets passes in.
 */
function onEdit(e) {
  // 1. Guard: no event object means it was not run by the trigger.
  if (!e || !e.range) return;

  // 2. Only act on the Clients tab.
  const sheet = e.range.getSheet();
  if (sheet.getName() !== CLIENTS_SHEET_NAME) return;

  // 3. Ignore edits to the header row itself.
  if (e.range.getRow() === 1) return;

  // 4. Find which column was edited by reading its header.
  const headers = sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];
  const columnName = headers[e.range.getColumn() - 1];

  // 5. Only the email and phone columns are validated.
  if (columnName !== EMAIL_HEADER && columnName !== PHONE_HEADER) return;

  // 6. A cleared cell is valid — drop any red tint and stop.
  const value = String(e.value || '');
  if (!value) {
    e.range.setBackground(null);
    return;
  }

  // 7. Test the value against the right pattern for its column.
  const valid =
    columnName === EMAIL_HEADER
      ? EMAIL_PATTERN.test(value)
      : PHONE_PATTERN.test(value);

  // 8. Tint the cell red on failure, clear the tint on success.
  e.range.setBackground(valid ? null : INVALID_BACKGROUND);
}

How it works

1. onEdit first checks it actually received an event object, so calling it by hand from the editor does nothing harmful.
2. It reads the edited cell’s sheet and stops unless it is the Clients tab.
3. It ignores edits to row 1, so editing a header never gets flagged.
4. It reads the header row and looks up the title of the edited column. Finding the column by name means rearranging columns never breaks the check.
5. If the edit was not in the email or phone column, it stops.
6. A cleared cell counts as valid: the script removes any existing red tint and returns, so deleting a bad value also clears the warning.
7. It tests the value against EMAIL_PATTERN or PHONE_PATTERN depending on which column was edited.
8. It sets the cell background red on failure and back to no fill on success.

Example run

Typing into the Clients sheet:

name	email	phone
Acme Ltd	[email protected]	+44 20 7946 0000
Globex	globex.com (red)	12345 (red)
Initech	[email protected]	(020) 7946 1234

globex.com has no @, so its cell turns red. 12345 is too short to be a phone number, so it turns red too. The valid rows stay un-tinted. Fixing either value and pressing Enter clears the red immediately.

Trigger it

onEdit from a function in the editor is a simple trigger, but a simple trigger cannot always change other cells reliably. Add an installable onEdit trigger so the background colour is set every time:

1. In the Apps Script editor open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose onEdit, event source From spreadsheet, event type On edit.
4. Save and approve the authorisation prompt.

Watch out for

• This validates format, not existence. [email protected] is a perfectly valid shape — the script cannot tell you the inbox exists.
• The phone pattern is deliberately loose so it accepts international numbers, spaces, and brackets. It will not enforce a single house style; tighten PHONE_PATTERN if you need one exact format.
• The check fires per cell edit. Pasting a whole block of rows at once triggers one event for the range, and only the top-left value is in e.value — paste validation is better handled by a separate full-column sweep.
• The red tint is a warning, not a block. Sheets cannot stop a value being saved from a script; the cell still holds the bad data until someone fixes it.
• An old value the script never saw stays un-tinted. Run a one-off pass over the existing email and phone columns when you first add this.