Mail merge with personalized PDF attachments

Northwind Studios sends monthly retainer recaps to every client. One email per row of the Clients sheet, each with its own PDF summary — the client’s name, their retainer figure, the month it covers. Doing this by hand means copying a Doc, swapping three values, exporting to PDF, and attaching it to an email, forty times over. It is an hour of dull work that nobody enjoys and everybody puts off.

This script does the whole merge in one pass. It reads the Clients sheet, copies a Google Doc template for each active client, swaps the placeholder tokens for real values, renders the result as a PDF, and emails it. Run it on a monthly trigger and the recaps simply go out on the first of the month with no one touching them.

What you’ll need

• A Clients sheet with columns: name, email, monthlyRetainer, status. Only rows where status is active are processed.
• A Google Doc template containing the tokens {{name}}, {{retainer}}, and {{month}} wherever those values should appear.
• A recaps/ Drive folder to hold the generated PDFs. Keeping them gives you an archive — and the original Docs can be deleted later if you want to save space.

The script

// The Clients sheet, the Doc template, and the folder for generated PDFs.
const CLIENTS_SHEET = '1abcClientsSheetId';
const RECAP_TEMPLATE = '1abcRecapTemplateId';
const RECAPS_FOLDER = '1abcRecapsFolderId';

// Only clients with this status receive a recap.
const ACTIVE_STATUS = 'active';

/**
 * Reads the Clients sheet and sends each active client a personalised
 * recap email with a freshly generated PDF attached.
 */
function sendMonthlyRecaps() {
  // The month label used in the subject line, file name, and document.
  const month = Utilities.formatDate(new Date(), 'GMT', 'MMMM yyyy');

  // 1. Read every row of the Clients sheet.
  const sheet = SpreadsheetApp.openById(CLIENTS_SHEET).getSheets()[0];
  const [header, ...rows] = sheet.getDataRange().getValues();

  if (!rows.length) {
    Logger.log('No client rows found — nothing to send.');
    return;
  }

  // Map header names to column indexes so the code reads by name.
  const col = Object.fromEntries(header.map((h, i) => [h, i]));
  const folder = DriveApp.getFolderById(RECAPS_FOLDER);
  let sent = 0;

  for (const row of rows) {
    // 2. Skip anyone who isn't an active client.
    if (row[col.status] !== ACTIVE_STATUS) continue;

    const name = row[col.name];
    const email = row[col.email];
    const retainer = row[col.monthlyRetainer];

    // One bad row shouldn't halt the whole run — isolate each send.
    try {
      // 3. Copy the template Doc into the recaps folder.
      const copy = DriveApp.getFileById(RECAP_TEMPLATE)
        .makeCopy(`${name} — ${month}`, folder);

      // 4. Open the copy and swap the placeholder tokens for real values.
      const doc = DocumentApp.openById(copy.getId());
      const body = doc.getBody();
      body.replaceText('{{name}}', name);
      body.replaceText('{{retainer}}', `$${retainer.toLocaleString()}`);
      body.replaceText('{{month}}', month);
      doc.saveAndClose();

      // 5. Attach the PDF render of the Doc and send the email.
      GmailApp.sendEmail(email, `Your ${month} recap from Northwind`,
        `Hi ${name},\n\nThis month's recap is attached.\n\n— Northwind Studios`, {
        attachments: [copy.getAs('application/pdf')],
        name: 'Northwind Studios',
      });
      sent++;
    } catch (err) {
      // Log and move on so the rest of the list still goes out.
      Logger.log(`Failed for ${email}: ${err.message}`);
    }
  }
  Logger.log(`Sent ${sent} recap email(s) for ${month}.`);
}

How it works

1. sendMonthlyRecaps formats the current month into a label like June 2025, used in the subject line, the PDF file name, and the document itself.
2. It reads every row of the Clients sheet and bails out early if the sheet is empty. A header-to-index map (col) lets the rest of the code reference columns by name rather than fragile numeric positions.
3. For each row it skips anyone whose status isn’t active, so paused or churned clients never get a recap.
4. It copies the Doc template into the recaps/ folder, opens the copy, and uses replaceText to swap {{name}}, {{retainer}}, and {{month}} for real values. The retainer is formatted with a dollar sign and thousands separator.
5. copy.getAs('application/pdf') renders the finished Doc to a PDF blob, which is attached to a Gmail message sent under the Northwind sender name.
6. Each iteration is wrapped in try/catch, so a missing email address or a permissions error on one row is logged and skipped rather than stopping the whole batch.

Example run

Say the Clients sheet holds these rows:

name	email	monthlyRetainer	status
Acme Co	[email protected]	4500	active
Belltower	[email protected]	2800	active
Crayfish Ltd	[email protected]	3200	paused

A run in June produces two emails — Crayfish is skipped because it is paused. Acme receives a message titled “Your June 2025 recap from Northwind” with a PDF named Acme Co — June 2025.pdf attached. Inside that PDF the template tokens have become real text: the name reads Acme Co, the retainer reads $4,500, and the month reads June 2025. The execution log records Sent 2 recap email(s) for June 2025.

Trigger it

1. In the Apps Script editor, open Triggers and click Add trigger.
2. Function: sendMonthlyRecaps. Event source: time-based. Type: month timer, day 1, around 9am.
3. Save. The recaps now go out on the first of every month unattended.

Watch out for

• Gmail quotas cap how many recipients you can reach per day — 100 on free accounts, 1,500 on Workspace. A few dozen clients is fine, but a large list needs pacing. See Throttle bulk sends to stay under Gmail quotas.
• Every run leaves a Doc copy behind in recaps/. That is a useful archive, but the folder grows monthly — periodically clear out old Docs, or delete each copy after the email sends if you only need the PDF.
• replaceText only touches the document body. If a token sits in a header, footer, or table cell that isn’t part of the body, it won’t be replaced — keep all three tokens in the main body of the template.
• retainer.toLocaleString() assumes the cell holds a number. If the column is formatted as text, the value arrives as a string and the formatting silently does nothing — store retainers as plain numbers.
• A blank or malformed email cell throws inside the loop. The try/catch keeps the run alive and logs the failure, so check the execution log after each run to catch rows that quietly didn’t send.