Notify the team when a Sheet row is marked done

Northwind tracks its client work in a single Projects sheet — one row per project, with a status column that moves from in progress to done. The problem is the gap between a project finishing and anyone telling the client. Awadesh marks the row done, then means to send the wrap-up email, then gets pulled into the next thing, and the client hears nothing for two days.

This script removes the gap entirely. The moment the status cell in any row flips to done, an onEdit trigger fires, reads the rest of that row, and sends a same-second email to the client and the internal team. No extra step, no forgetting — marking the row is the notification.

What you’ll need

• A Google Sheet with a tab named exactly Projects. Row 1 must be a header, and the script expects columns named status, client, project, and clientEmail.
• The script bound to that spreadsheet — write it from Extensions → Apps Script inside the sheet, not as a standalone project.
• A Gmail account to send from. By default that is whoever edits the cell; see “Watch out for” if you need it to always send from one address.

The script

// The tab this automation watches. Edits to any other tab are ignored.
const WATCHED_SHEET = 'Projects';

// The value in the status column that triggers a notification.
const DONE_VALUE = 'done';

// Internal address copied on every completion email.
const TEAM_EMAIL = '[email protected]';

/**
 * Simple trigger: runs on every edit to the bound spreadsheet. When the
 * status column of a Projects row is set to "done", it emails the client
 * and the team. The event object `e` carries the details of the edit.
 */
function onEdit(e) {
  // 1. Guard: a manual run has no event object, so bail out safely.
  if (!e || !e.range) return;

  // 2. Only react to edits on the Projects tab.
  const sheet = e.range.getSheet();
  if (sheet.getName() !== WATCHED_SHEET) return;

  // 3. Map header names to 1-based column numbers so the code does not
  //    depend on the column order.
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const col = Object.fromEntries(headers.map((h, i) => [h, i + 1]));

  // 4. Only act when the *status* column was edited to exactly "done".
  if (e.range.getColumn() !== col.status || e.value !== DONE_VALUE) return;

  // 5. Read the whole edited row so we can build the message.
  const row = sheet
    .getRange(e.range.getRow(), 1, 1, sheet.getLastColumn())
    .getValues()[0];
  const client = row[col.client - 1];
  const project = row[col.project - 1];
  const clientEmail = row[col.clientEmail - 1];

  // 6. Guard: without a client address there is nobody to notify.
  if (!clientEmail) {
    Logger.log('Row marked done but clientEmail is blank — no email sent.');
    return;
  }

  // 7. Send the completion email to the client, copying the team.
  GmailApp.sendEmail(
    `${clientEmail},${TEAM_EMAIL}`,
    `${project} — shipped`,
    `Hi ${client},\n\n` +
      `${project} is complete. Anything else we should look at?\n\n` +
      `— Northwind`
  );
}

How it works

1. onEdit is a simple trigger: Apps Script runs it automatically after every edit to the bound spreadsheet, passing an event object e that describes what changed. A manual run has no e, so the first line bails out.
2. It checks the sheet name. Edits to any tab other than Projects are ignored, so the automation is scoped to exactly one sheet.
3. It reads the header row and builds a col lookup mapping each header name to its 1-based column number — so status can move columns without breaking the script.
4. The core test: it only continues if the edited cell was in the status column and its new value is exactly done. Any other edit does nothing.
5. It reads the full row of the edit and pulls out the client name, the project name, and the client’s email address.
6. If clientEmail is blank it logs a note and stops, rather than sending a broken email.
7. It sends one email to the client and the team, with the project name in the subject and a short, friendly body.

Example run

The Projects sheet holds a row mid-flight:

project	client	clientEmail	status
Brand refresh	Acme Ltd	[email protected]	in progress

Awadesh changes the status cell to done. Within a second, this email goes out to [email protected] and [email protected]:

Subject: Brand refresh — shipped

Hi Acme Ltd,

Brand refresh is complete. Anything else we should look at?

— Northwind

Trigger it

A simple onEdit(e) function runs automatically with no setup — just save the script and edit a status cell. If you instead need an installable trigger (see the note below), wire it up like this:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose function onEdit, event source From spreadsheet, event type On edit.

Watch out for

• Simple triggers send mail as the editor. The completion email goes out from whoever flipped the cell. To always send from one address, create an installable trigger under an account that owns the script, and rename the function so it is not the reserved onEdit name.
• Simple triggers cannot prompt for authorisation. GmailApp is an authorised-only service, so the first edit may fail silently until the script owner runs any function once manually and approves the scopes. An installable trigger avoids this.
• It fires on every matching edit. Re-typing done into an already-done cell sends the email again. If that is a problem, add a notified column and check it before sending.
• Exact-match only. Done, DONE, or done with a trailing space will not trigger it. Normalise with String(e.value).trim().toLowerCase() if your team is inconsistent.
• Pasting a block of cells may not give a usable e.value. For multi-cell edits, e.value is undefined — this script only handles single-cell status changes, which is the normal case here.