Send an email when a status changes

1 · Prepare

Put these pieces in place

• Create a Google Sheet with a task name in column A, a status in column B, and an email address in column C.
• Add a dropdown to column B with statuses such as In progress and Complete. Select the column and choose Insert, then Dropdown.
• Create a tab named Template if you duplicate tabs for new projects, or keep all tasks on one tab.
• Keep your headers in row 1, because the script skips that row.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

const TEMPLATE = 'Template'; // Edits on this tab are skipped so preparing a new template does not send an email.
const STATUS_COLUMN = 2; // Column B is column 2. The script sends an email only when a cell in this column changes.
const EMAIL_COLUMN = 3; // Column C is column 3. The notification email is sent to the address in this cell.
const TASK_NAME_COLUMN = 1; // Column A is column 1. The script includes this item name in the email subject and body.

/** @param {Events.SheetsOnEdit} e */
function sendEmailOnStatusChange(e) {
  if (!e || !e.range) return;

  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() === TEMPLATE) return;
  if (range.getNumRows() > 1 || range.getNumColumns() > 1) return;
  if (range.getColumn() !== STATUS_COLUMN) return;

  const editedRow = range.getRow();
  if (editedRow < 2) return;

  const newStatus = e.value;
  const oldStatus = e.oldValue;
  if (!newStatus || newStatus === oldStatus) return;

  const recipient = String(sheet.getRange(editedRow, EMAIL_COLUMN).getValue()).trim();
  if (!recipient || !recipient.includes('@')) return;

  const taskName = sheet.getRange(editedRow, TASK_NAME_COLUMN).getValue() || ('Row ' + editedRow);
  const sheetName = sheet.getName();
  const spreadsheet = e.source;

  const subject = 'Status updated to ' + newStatus + ' on ' + sheetName;
  const body = 'The status for "' + taskName + '" was updated to ' + newStatus + '.\n\n' +
    'Sheet: ' + sheetName + '\n' +
    'Row: ' + editedRow + '\n' +
    'Previous status: ' + (oldStatus || '(none)') + '\n\n' +
    'Open spreadsheet: ' + spreadsheet.getUrl();

  GmailApp.sendEmail(recipient, subject, body);
}

4 · Run

Follow these steps in order

1. Set up one test row

   On your sheet tab, put headers in row 1. Put one task in row 2, choose a status in column B, and put your own email address in column C.

2. Paste the function and save

   Choose Extensions, then Apps Script. Replace the starter code with this function and save.

3. Install the On edit trigger

   Click Triggers, the clock icon in the left sidebar, then click Add Trigger. Choose sendEmailOnStatusChange as the function, From spreadsheet as the event source, and On edit as the event type. Click Save and approve access when Google asks.

4. Test by changing the status

   Return to the spreadsheet and pick a different status in cell B2. Within a few seconds, an email with a subject such as Status updated to Complete on Sheet1 arrives in your inbox. When you duplicate the tab for a new project, edits on the new tab send emails too, with no new trigger to install.

5 · Confirm

How to tell it worked

Changing a status dropdown cell sends an email notification to the recipient on that row within a few seconds, with a link back to the spreadsheet.

If the result is different

Troubleshooting

The Execution log shows "You do not have permission to call GmailApp.sendEmail".

Keep the function name sendEmailOnStatusChange and install it as an On edit trigger from Triggers in the left sidebar. A function named onEdit runs as a simple trigger, and simple triggers cannot send email.

No email arrives after I change a status.

Open Triggers and check that an On edit trigger runs sendEmailOnStatusChange. Then check that the edit is in column B on row 2 or below, that column C in that row holds an email address, and that the tab is not named Template.

Clicking Run in the editor sends nothing.

Test the function by changing a status cell in the spreadsheet instead. The Run button calls it with no edit event, so the first line stops the function.

Pasting several cells at once sends no email.

Paste the values first, then change each status cell on its own. The script skips edits to more than one cell, so a large paste cannot send many emails at once.