Build a merged-address letter generator

Every December Northwind sends a physical thank-you letter to each partner along with their gift. The text is the same for everyone — only the name, address, and date change — but the studio still ends up copying a template Doc by hand, swapping in three fields, and saving it under a new name, dozens of times over. It is an afternoon of dull, error-prone clicking.

This script does the mail merge for you. It reads a sheet of recipients, copies the letter template once per row, and replaces the placeholders with that person’s details. You end up with a folder of finished, named Docs — one per partner — ready to print and post.

What you’ll need

• A letter template Google Doc containing the placeholders {{name}}, {{address}}, and {{date}} wherever the merged values should appear.
• A Mailing sheet with a header row and columns named name and address — one row per recipient.
• A Drive folder to hold the generated letters, so they do not clutter the root of your Drive.
• The IDs of the template Doc, the mailing sheet, and the output folder, pasted into the config constants below.

The script

// The letter template Doc, with {{name}}, {{address}} and {{date}} placeholders.
const TEMPLATE_ID = '1abcLetterTemplateId';

// The sheet of recipients (columns: name, address).
const MAILING_ID = '1abcMailingId';

// The Drive folder that finished letters are saved into.
const LETTERS_FOLDER_ID = '1abcLettersFolderId';

/**
 * Generates one personalised letter Doc per row of the mailing sheet,
 * copying the template and filling in its placeholders.
 */
function generateLetters() {
  // 1. Format today's date once — every letter carries the same date.
  const today = Utilities.formatDate(new Date(), 'GMT', 'd MMMM yyyy');

  // 2. Read the mailing sheet and map header names to column indexes.
  const [header, ...rows] = SpreadsheetApp.openById(MAILING_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  if (!rows.length) {
    Logger.log('No recipients in the mailing sheet — nothing to do.');
    return;
  }

  // 3. Grab the output folder and the template file once, up front.
  const folder = DriveApp.getFolderById(LETTERS_FOLDER_ID);
  const template = DriveApp.getFileById(TEMPLATE_ID);

  // 4. Walk each recipient row.
  let created = 0;
  for (const row of rows) {
    const name = row[col.name];
    if (!name) continue; // skip blank rows

    // Copy the template into the letters folder, named after the recipient.
    const copy = template.makeCopy('Letter — ' + name, folder);

    // Open the copy and swap the placeholders for this recipient's details.
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    body.replaceText('{{name}}', name);
    body.replaceText('{{address}}', row[col.address]);
    body.replaceText('{{date}}', today);
    doc.saveAndClose();
    created++;
  }

  Logger.log('Generated ' + created + ' letters.');
}

How it works

1. generateLetters formats the current date once with Utilities.formatDate so every letter in the batch shows the same, consistently formatted date.
2. It reads the Mailing sheet in a single call and builds a col lookup, so the code refers to col.name and col.address rather than fixed numbers.
3. If the sheet has no data rows it logs a message and stops, avoiding an empty run.
4. It fetches the output folder and the template file once, before the loop, so Drive is not queried again for every recipient.
5. For each row it copies the template into the letters folder with a clear, recipient-specific name, then opens that copy.
6. It calls replaceText for each placeholder — {{name}}, {{address}}, {{date}} — and saveAndClose to commit the changes. A blank name row is skipped so a stray empty row does not produce an untitled letter.

Example run

Given a Mailing sheet like this:

name	address
Acme Components Ltd	14 Bridge Street, Leeds, LS1 4AB
Harbourview Design	2 Quay Road, Bristol, BS1 5TH

After generateLetters runs, the letters folder contains two Docs:

• Letter — Acme Components Ltd
• Letter — Harbourview Design

Inside the first, the template’s Dear {{name}}, has become Dear Acme Components Ltd,, the address block reads 14 Bridge Street, Leeds, LS1 4AB, and the date line shows today’s date. Each Doc is ready to open, check, and print.

Run it

This runs once a year when the mailing list is ready, so trigger it by hand:

1. In the Apps Script editor, select generateLetters and click Run.
2. Approve the Docs, Sheets, and Drive authorisation prompt the first time.
3. Open the letters folder in Drive to review and print the finished Docs.

Watch out for

• replaceText only changes text that appears in the document body. A placeholder in a header, footer, or text box is not touched — keep all placeholders in the main body, or extend the script to call replaceText on getHeader() and getFooter() as well.
• The placeholders in the template must match the script exactly, braces and all. A template that says {name} or {{ name }} will be left unmerged.
• Re-running the script does not overwrite the previous batch — it creates a fresh set of copies. Clear the letters folder before a re-run, or you will end up with duplicates.
• Copying and editing one Doc per recipient is several Drive and Docs operations per row. For a few dozen letters this is fine; for many hundreds the run can approach the script’s six-minute execution limit.
• The script does not deduplicate the mailing sheet. Two rows for the same partner produce two letters — clean the list first if duplicates are likely.