Mail merge from a sheet with GmailApp

The sheet

Tab Customers:

Column E starts empty; the script writes Sent with a timestamp.

GmailApp vs MailApp

• MailApp.sendEmail(...) — simple sending only.
• GmailApp.sendEmail(...) — sending plus drafts, labels, searching your inbox, aliases.

Both share the same daily recipient quota. We'll use GmailApp so we can make drafts for testing.

The script

const TEST_MODE = true; // true = create drafts only; false = actually send

function sendReminders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  const rows = sheet.getRange(2, 1, lastRow - 1, 5).getValues();
  const statusOut = [];
  let count = 0;

  rows.forEach(([name, email, amount, dueDate, status]) => {
    if (!email || String(status).startsWith('Sent')) {
      statusOut.push([status]); // keep as is
      return;
    }

    const dateText = Utilities.formatDate(new Date(dueDate), Session.getScriptTimeZone(), 'dd MMM yyyy');
    const amountText = Number(amount).toLocaleString('en-IN');

    const subject = `Payment reminder: ₹${amountText} due on ${dateText}`;
    const plain = `Dear ${name},\n\nThis is a reminder that ₹${amountText} is due on ${dateText}.\n\nThank you.`;
    const html =
      `<p>Dear ${name},</p>` +
      `<p>This is a reminder that <b>₹${amountText}</b> is due on <b>${dateText}</b>.</p>` +
      `<p>Thank you.</p>`;

    const options = { htmlBody: html, name: 'Accounts Team' };

    if (TEST_MODE) {
      GmailApp.createDraft(email, subject, plain, options);
      statusOut.push(['Draft ' + new Date().toLocaleString('en-IN')]);
    } else {
      GmailApp.sendEmail(email, subject, plain, options);
      statusOut.push(['Sent ' + new Date().toLocaleString('en-IN')]);
    }
    count++;
  });

  sheet.getRange(2, 5, statusOut.length, 1).setValues(statusOut); // one batch write
  SpreadsheetApp.getActiveSpreadsheet().toast(`${count} emails processed`);
}

How it works:

1. Reads all rows at once.
2. Skips rows with no email or already Sent.
3. Builds subject, plain text and HTML for each person.
4. In test mode, creates drafts — open Gmail → Drafts to check them.
5. Writes the status column back in one go.

When the drafts look right, delete them, clear the Status column, set TEST_MODE = false and run again.

Useful options

{
  htmlBody: html,                 // HTML version
  name: 'Accounts Team',          // sender display name
  cc: 'manager@example.com',
  bcc: 'archive@example.com',
  replyTo: 'accounts@example.com',
  attachments: [pdfBlob]          // a file blob (see the PDF invoices lesson)
}

Good habits

• Always include the plain-text body as well as htmlBody.
• Test with your own address or drafts before sending to customers.
• Check MailApp.getRemainingDailyQuota() for large lists (previous lesson).
• Only email people who expect to hear from you, and include a way to contact you.

Key points

• Read rows in bulk, build each message with template literals, write status back in bulk.
• A saved Status column skips completed rows. If a run fails before the batch write, already-sent messages can be sent again; reconcile Gmail and the sheet before retrying.
• createDraft is the safest way to test.