Intermediate · I4

Gmail and mail merge

Emailing 35 students their own result by hand takes an hour. A mail merge takes ten seconds. This chapter sends plain and HTML email from a sheet, attaches files, and reads and tidies your inbox, with the safety habits that stop you emailing the wrong people.

Send your first email

Send to yourself while testing. This code reads your own address with Session:

Code.gs

function sendTest() {
  const me = Session.getActiveUser().getEmail();

  // The simplest form: to, subject, plain-text body.
  MailApp.sendEmail(me, 'Test from Apps Script', 'If you can read this, it worked.');

  // With options.
  MailApp.sendEmail({
    to: me,
    subject: 'Homework reminder',
    body: 'Plain text for email apps that cannot show HTML.',
    htmlBody: '<h2>Homework</h2><p>Due <b>Friday</b>. <a href="https://classroom.google.com">Open Classroom</a></p>',
    name: 'Computing Department',      // the sender name people see
    replyTo: me,
  });

  console.log(`Emails left today: ${MailApp.getRemainingDailyQuota()}`);
}

Execution log

Emails left today: 98

Option	Use
`to`, `cc`, `bcc`	Addresses; several separated by commas
`subject`, `body`	Subject line and plain text
`htmlBody`	A formatted version (bold, links, colours, tables)
`name`	The sender name shown, e.g. "Computing Department"
`replyTo`	Where replies go
`attachments`	An array of files (blobs)

MailApp can only send. GmailApp can send too, and also read, search, label and make drafts, so it asks for a much bigger permission. Use MailApp unless you need the rest.

Next: mail merge →

One email per row

Make a Students tab. Use your own address (or addresses you own) for the test rows.

	A	B	C	D
1	Name	Email	Mark	Sent
2	Ploy	you+ploy@gmail.com	72
3	Ben	you+ben@gmail.com	38

Tip: Gmail ignores anything after a + in the name part, so you+ploy@gmail.com arrives in your own inbox.

Code.gs

// Mail merge: one personal email per row of the "Students" tab.
// Columns: Name | Email | Mark | Sent
function sendResults() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Students');
  const data = sheet.getDataRange().getValues();
  const SENT = 3;                                   // column D, index 3

  for (let i = 1; i < data.length; i++) {           // start at 1: skip header
    const [name, email, mark, sent] = data[i];
    if (!email || sent) continue;                   // no address, or already sent

    const body =
      `Dear ${name},\n\n` +
      `Your mark for the Apps Script test is ${mark}/100.\n` +
      (mark >= 50 ? 'Well done, you passed.\n' : 'Please book a catch-up session with me.\n') +
      `\nBest wishes,\nKru Jazzy`;

    MailApp.sendEmail(email, 'Your Apps Script test result', body);
    sheet.getRange(i + 1, SENT + 1).setValue(new Date());   // i is 0-based, rows are 1-based
  }
}

	A	B	C	D
1	Name	Email	Mark	Sent
2	Ploy	you+ploy@gmail.com	72	2026-11-03 10:41
3	Ben	you+ben@gmail.com	38	2026-11-03 10:41

Run it a second time and nothing is sent, because every row has a Sent date. Clear a cell in column D to resend to just that person. This "mark as done" column is what makes a merge safe to re-run.

Next: HTML email →

Designed emails with a template file

Long HTML in a JavaScript string is hard to read. Instead, add an HTML file to the project (+ next to Files → HTML, name it Email) and use scriptlets:

Email.html

<!-- File: Email.html -->
<div style="font-family: Arial, sans-serif; max-width: 520px">
  <h2 style="color: #1a5fb4">Hello <?= name ?>,</h2>
  <p>Your mark is <b><?= mark ?>/100</b>.</p>
  <? if (mark >= 80) { ?>
    <p style="color: #188038">Excellent work! 🎉</p>
  <? } ?>
  <p>Kru Jazzy</p>
</div>

Scriptlet	Does
`<?= value ?>`	Prints a value, safely escaped
`<? code ?>`	Runs code (an if, a loop) without printing
`<?!= html ?>`	Prints without escaping, only for HTML you built yourself

Code.gs

function sendHtmlResult(name, email, mark) {
  const t = HtmlService.createTemplateFromFile('Email');   // the Email.html file
  t.name = name;                                            // variables for <?= ?>
  t.mark = mark;
  const html = t.evaluate().getContent();

  MailApp.sendEmail({
    to: email,
    subject: 'Your result',
    htmlBody: html,
    body: `Hello ${name}, your mark is ${mark}/100.`,
  });
}

function testHtmlEmail() {
  sendHtmlResult('Ploy', Session.getActiveUser().getEmail(), 88);
}

Any property you set on the template (t.name, t.mark) becomes a variable inside the HTML file. You will use the same templates for web pages in A2 and A3.

Attachments

Code.gs

function sendWithPdf() {
  const file = DriveApp.getFilesByName('Certificate – Ben').next();
  MailApp.sendEmail({
    to: 'ben@school.example',
    subject: 'Your certificate',
    body: 'Congratulations! Your certificate is attached.',
    attachments: [file.getAs(MimeType.PDF)],
  });
}

Combine this with the certificate maker from I3 to email each student their own PDF.

Next: reading Gmail →

Search, read and label

GmailApp.search uses the same search words as the Gmail search box:

Query	Finds
`is:unread`	Unread mail
`from:office@school.example`	From one sender
`subject:homework`	A word in the subject
`has:attachment`	Mail with files
`newer_than:7d` / `older_than:1y`	By age
`label:office in:inbox`	Combine words with spaces

Code.gs

function unreadToSheet() {
  const threads = GmailApp.search('is:unread newer_than:7d', 0, 20);
  const rows = threads.map((t) => {
    const m = t.getMessages()[0];
    return [m.getDate(), m.getFrom(), t.getFirstMessageSubject(), m.getPlainBody().slice(0, 100)];
  });

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Inbox') || ss.insertSheet('Inbox');
  sheet.clearContents();
  sheet.appendRow(['Date', 'From', 'Subject', 'Preview']);
  if (rows.length) sheet.getRange(2, 1, rows.length, 4).setValues(rows);
}

Results are threads (conversations). Each thread has messages, and each message has a sender, date and body.

Code.gs

// Put a label on every email from the school office and archive it.
function tidyOfficeMail() {
  const label = GmailApp.getUserLabelByName('Office') || GmailApp.createLabel('Office');
  const threads = GmailApp.search('from:office@school.example in:inbox');
  threads.forEach((t) => t.addLabel(label).moveToArchive());
  console.log(`Moved ${threads.length} threads`);
}

Execution log

Moved 4 threads

Put tidyOfficeMail on an hourly trigger (I2) and your inbox sorts itself.

Next: limits and care →

Quotas

Account	Email recipients per day (about)
Personal Gmail	100
Google Workspace (school or business)	1,500

Every address in to, cc and bcc counts. When you run out, sendEmail throws "Service invoked too many times for one day: email". Check MailApp.getRemainingDailyQuota() before a big merge, and stop early if it's too low. Google can change these numbers, so check the current quotas page if a script hits a limit.

Before you press Run on a real list

Test with your own addresses first.
Print the emails with console.log instead of sending, and read a few.
Or make drafts (Practice 1), check them in Gmail, then send.
Keep the Sent column so a re-run never double-sends.
Use bcc, or one email per person, so you never show everyone's address to everyone.

An email can't be unsent. A minute of checking saves a very awkward apology.

Next: practice →

Practice

1. Drafts first Easy

Change the merge so it creates a Gmail draft for each student instead of sending.

Show a solution

Code.gs

// Safer than sending: create drafts, read them, then send by hand.
function draftResults() {
  const data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Students').getDataRange().getValues();
  data.slice(1).forEach(([name, email, mark]) => {
    if (email) GmailApp.createDraft(email, 'Your result', `Dear ${name}, your mark is ${mark}/100.`);
  });
}

2. Homework reminders Medium

Add a checkbox column E "Handed in?". Email only the students whose box isn't ticked, then show a toast saying how many reminders went.

Show a solution

Code.gs

// Email everyone whose "Handed in?" box (column E) is not ticked.
function remindMissing() {
  const data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Students').getDataRange().getValues();
  const missing = data.slice(1).filter((r) => r[1] && r[4] !== true);
  missing.forEach(([name, email]) => {
    MailApp.sendEmail(email, 'Reminder: homework', `Hi ${name}, I haven't received your homework yet.`);
  });
  SpreadsheetApp.getActiveSpreadsheet().toast(`${missing.length} reminders sent`);
}

3. Summary table Medium

Send yourself one HTML email with the Name, Email and Mark columns as a table.

Show a solution

Code.gs

// Send yourself one email listing the class results as a table.
function emailSummary() {
  const rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Students').getDataRange().getDisplayValues();
  const html =
    '<table border="1" cellpadding="6" style="border-collapse:collapse">' +
    rows.map((r) => '<tr>' + r.slice(0, 3).map((v) => `<td>${v}</td>`).join('') + '</tr>').join('') +
    '</table>';
  MailApp.sendEmail({
    to: Session.getActiveUser().getEmail(),
    subject: 'Class summary',
    htmlBody: `<p>Results so far:</p>${html}`,
  });
}

4. Quota guard Challenge

Change sendResults so it stops with a clear alert, before sending anything, when there are more unsent rows than getRemainingDailyQuota().

Next: quiz →

Check yourself

1. What is the difference between MailApp and GmailApp?

2. Why does the mail merge write a date into a "Sent" column?

3. In the loop, data[i] is row i + 1 of the sheet. Why?

4. Why give both htmlBody and body?

5. A free Gmail account can email roughly how many recipients a day from Apps Script?

6. GmailApp.search('from:office is:unread') returns…