Answers / Question 3

Why your onEdit script won't send email in Google Sheets (and the fix)

A function named onEdit can't send email because simple triggers can't use authorized services. Rename it, add an installable trigger, and it works.

The problem

Your script sends an email perfectly when you press Run in the editor, but nothing arrives when someone actually edits the sheet. There's no visible error, so it looks like the trigger isn't firing at all.

Inspired by a public question on Stack Overflow. The problem above is described in our own words and the solution below is ours.

Why it happens

A function called onEdit runs as a simple trigger. Simple triggers run without your authorization, so they aren't allowed to use services like MailApp or GmailApp. The call fails silently. When you press Run, you are the one authorizing it, which is why it works there.

The fix

Give the function any other name (below it's notifyOnCheck). If you keep the name onEdit and add an installable trigger, it runs twice.
Paste the code, set the tab name, columns and address in NOTIFY.
Run installNotifyTrigger once and approve the permissions. That creates an installable “On edit” trigger, which runs as you and can send mail. (You can also add it by hand: clock icon → Add trigger → From spreadsheet → On edit.)

Code.gsApps Script

// Email someone when a checkbox is ticked. Runs from an INSTALLABLE trigger,
// because a plain onEdit() function is not allowed to send email.
const NOTIFY = {
  sheetName: 'Projects',
  checkboxCol: 14,          // column N holds the checkbox
  labelCol: 2,              // column B: what to call the row in the email
  to: '[email protected]'
};

function notifyOnCheck(e) {
  const r = e.range, sh = r.getSheet();
  if (sh.getName() !== NOTIFY.sheetName || r.getColumn() !== NOTIFY.checkboxCol || r.getRow() < 2) return;
  if (r.getNumRows() !== 1 || r.getNumColumns() !== 1 || e.value !== 'TRUE') return;   // single tick only
  const label = sh.getRange(r.getRow(), NOTIFY.labelCol).getValue();
  MailApp.sendEmail(NOTIFY.to, label + ' is ready', 'Row ' + r.getRow() + ' was ticked on the ' + sh.getName() + ' tab.');
}

// Run this once from the editor (and approve the permissions). It replaces any older copy of the trigger.
function installNotifyTrigger() {
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return t.getHandlerFunction() === 'notifyOnCheck'; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
  ScriptApp.newTrigger('notifyOnCheck').forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

Good to know

A ticked checkbox arrives in the event as the text 'TRUE', not the boolean true.
Remove any SpreadsheetApp.getUi().alert() calls from trigger code. Pop-ups can't show inside a trigger and will stop the script.
Check Executions in the Apps Script editor to see errors from triggered runs.

The script's logic is unit-tested against a simulated spreadsheet before publishing. Always try new automations on a copy of your sheet first.