Triggers and menus: a Monday email that sends itself

A trigger runs a function without anyone clicking. Two kinds cover most needs: something changed, and it's time.

onEdit: react to a change. Name a function onEdit and it runs every time someone edits the sheet. This one stamps the date when a lead's status becomes Closed:

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  // Only watch the Status column (D) on the Leads tab, below the header.
  if (sheet.getName() !== 'Leads' || range.getColumn() !== 4 || range.getRow() < 2) return;
  if (e.value === 'Closed') {
    sheet.getRange(range.getRow(), 5).setValue(new Date());  // column E: Closed on
  }
}

The e object describes the edit: e.range is the cell, e.value the new value, e.oldValue the old one. Check the tab and column first and leave early, because onEdit runs on every edit in the file. When someone pastes several cells at once, e.value is empty, so loop over e.range if pastes matter.

A simple onEdit can't do anything that needs your permission, like sending email, and it has 30 seconds. For those, create an installable trigger (the clock icon in the editor, event type On edit), which runs as you.

Time-driven: run on a schedule. This sends the weekly report built in the capstone every Monday morning:

function sendWeeklyReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const report = ss.getSheetByName('Report');
  const headline = report.getRange('B12').getDisplayValue();
  const rows = report.getRange('A3:C8').getDisplayValues();
  const table = rows.map(function (r) {
    return '<tr><td>' + r.join('</td><td>') + '</td></tr>';
  }).join('');
  MailApp.sendEmail({
    to: '[email protected]',
    subject: headline,
    htmlBody: '<p>' + headline + '</p><table>' + table + '</table>' +
      '<p><a href="' + ss.getUrl() + '">Open the sheet</a></p>'
  });
}

function createMondayTrigger() {
  ScriptApp.newTrigger('sendWeeklyReport')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(7)
    .create();
}

Run createMondayTrigger once from the editor. From then on, sendWeeklyReport runs every Monday between 7 and 8am in the script's time zone (set it under Project Settings). Run it twice and you get two emails, so check the Triggers page in the editor's left bar; you can also add and delete triggers there without code. getDisplayValues reads what the cells show, so dates and dollars arrive formatted.

A menu to run it now. Scheduled jobs still need a manual button for the week someone asks for the numbers on Thursday:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Reports')
    .addItem('Send weekly report now', 'confirmAndSend')
    .addSeparator()
    .addItem('Turn on the Monday email', 'createMondayTrigger')
    .addToUi();
}

function confirmAndSend() {
  const ui = SpreadsheetApp.getUi();
  const answer = ui.alert('Send the weekly report to the team now?', ui.ButtonSet.YES_NO);
  if (answer === ui.Button.YES) {
    sendWeeklyReport();
    ui.alert('Sent.');
  }
}

The confirmation matters: a menu item that emails 40 people should never fire on a stray click. When a triggered run fails, Google emails the script's owner, so an error won't go unnoticed. Scripts belong to a person, so before you leave a team, hand ownership of the file and its triggers to someone who's staying.