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.
• Set up a dropdown list in column B with statuses such as In progress and Complete.
• 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 safely

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

   Open Extensions, choose Apps Script, replace the starter code with this function, and save the project.

3. Install the On edit trigger

   In the left sidebar, select Triggers (the clock icon) and click Add Trigger. Choose sendEmailOnStatusChange, select From spreadsheet as the event source, and choose On edit as the event type. Save and approve the requested permissions.

4. Test by changing the status

   Return to the spreadsheet and change the dropdown in cell B2. Check your inbox for the notification email. When you duplicate the tab for a new project, edits on the new tab also trigger notifications without installing new triggers.

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".

The function was run as a simple trigger or named onEdit. Simple triggers cannot send email. Keep the custom function name and create an installable trigger from the Triggers panel in the left sidebar.

Nothing happens when I change a cell.

Verify that you installed an On edit trigger for sendEmailOnStatusChange, that the edit is in column 2 on row 2 or below, and that the sheet tab is not named Template.

Running the function from the Apps Script editor fails.

That is expected because the editor does not provide an edit event. Test the function by changing a status cell in the spreadsheet.

Pasting multiple cells sends no email.

That is on purpose. The script skips multi-cell edits to prevent accidental email floods. Paste values first, then update the status cell individually.