Automated alerts with MailApp / GmailApp

Concept

1. MailApp vs GmailApp

	MailApp	GmailApp
Can	send email only	send, read, search, label, create drafts
Permission asked	"send email as you"	full access to your Gmail
Use when	alerts and reports	you also need to read or organise mail

For sending, prefer MailApp — smaller permission, same result.

2. The simplest email

function testMail() {
  MailApp.sendEmail('you@example.com', 'Test from Sheets', 'Hello! This came from Apps Script.');
}

The email goes from the account that owns the script (or set up the trigger).

3. Options object — HTML, CC, attachments

MailApp.sendEmail({
  to: 'owner@example.com',
  cc: 'manager@example.com',
  subject: 'Stock alert',
  htmlBody: '<p><b>3 items</b> are below reorder level.</p>',
  name: 'ExcelWalaa Stock Bot'        // sender display name
});

4. Complete low-stock alert

function sendStockAlert() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const data = ss.getSheetByName('Data').getDataRange().getValues();
  const header = data.shift();
  const low = data.filter(r => r[0] !== '' && Number(r[1]) < 5);

  if (low.length === 0) return;                        // nothing to report

  const rows = low.map(r =>
    `<tr><td>${r[0]}</td><td style="text-align:right">${r[1]}</td></tr>`).join('');
  const html = `
    <p>Good morning,</p>
    <p>These <b>${low.length}</b> items are below the reorder level:</p>
    <table border="1" cellpadding="6" style="border-collapse:collapse">
      <tr style="background:#1F4E78;color:#fff"><th>${header[0]}</th><th>${header[1]}</th></tr>
      ${rows}
    </table>
    <p><a href="${ss.getUrl()}">Open the stock sheet</a></p>`;

  MailApp.sendEmail({
    to: 'owner@example.com',
    subject: `Stock alert: ${low.length} items to reorder (${Utilities.formatDate(new Date(), 'Asia/Kolkata', 'dd-MMM-yyyy')})`,
    htmlBody: html
  });
}

Connect it to the daily 9 AM trigger from Lesson 4 and the owner gets the list every morning — only on days when something is low.

5. Attach the sheet as a PDF

const pdf = DriveApp.getFileById(ss.getId()).getAs('application/pdf').setName('Stock.pdf');
MailApp.sendEmail({ to: 'owner@example.com', subject: 'Stock report',
                    htmlBody: 'Report attached.', attachments: [pdf] });

This exports the whole spreadsheet (all visible sheets). For one specific tab with custom page settings, you need the export URL with UrlFetchApp — a more advanced recipe.

6. Personalised emails from a list

Sheet "Customers" with Name (A), Email (B), Due (C), Status (D):

function sendReminders() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
  const data = sh.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    const [name, email, due, status] = data[i];
    if (!email || status === 'Sent' || Number(due) <= 0) continue;
    MailApp.sendEmail(email, 'Payment reminder',
      `Dear ${name},\n\nYour pending amount is Rs ${Number(due).toLocaleString('en-IN')}.\n\nThank you.`);
    sh.getRange(i + 1, 4).setValue('Sent');           // log it
  }
}

7. Daily limits

Free Gmail accounts can send to about 100 recipients per day through scripts; Google Workspace accounts get much more (around 1,500). Check what's left:

Logger.log(MailApp.getRemainingDailyQuota());

Common mistakes

Sending to the full customer list while testing (test with your own address first). Hitting the daily quota with a loop. Using GmailApp when MailApp is enough (bigger permission prompt). Sending an alert even when there's nothing to report.

What you'll learn