Google Docs mail merge script builder

Google Docs has no mail merge button. The one Google does ship lives in Gmail, sends emails rather than documents, and isn't offered on personal accounts. The free way is a short Apps Script: type column headers into your letter as {{First name}}, and it writes one copy per row of a Google Sheet. The script is below, ready to copy. Change the names in the form and it rewrites itself; paste your letter and headers and it checks them before you run anything.

The script from the video composed on the page, no account, nothing stored

function onOpen() {
  DocumentApp.getUi().createMenu('Mail merge')
    .addItem('Create letters', 'createLetters')
    .addToUi();
}

function createLetters() {
  const template = DocumentApp.getActiveDocument().getBody();
  const file = DriveApp.getFilesByName('Sign-ups').next();
  const rows = SpreadsheetApp.open(file).getSheets()[0]
    .getDataRange().getDisplayValues();
  const headers = rows.shift();

  const letters = DocumentApp.create('Letters');
  const body = letters.getBody();
  rows.forEach((row, i) => {
    if (i > 0) body.appendPageBreak();
    for (let j = 0; j < template.getNumChildren(); j++) {
      const part = template.getChild(j).copy();
      headers.forEach((h, k) => part.replaceText('{{' + h + '}}', row[k]));
      body.appendParagraph(part);
    }
  });
  body.getChild(0).removeFromParent();
  letters.saveAndClose();

  const link = '<a href="' + letters.getUrl() + '" target="_blank">Open the letters</a>';
  DocumentApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(link).setHeight(60), 'Letters ready');
}

noteThis version copies plain paragraphs. A letter with a table or a bulleted or numbered list stops it with “The parameters (DocumentApp.ListItem) don't match the method signature” — tick that option and it adds the three lines that handle them.

noteOnly the body is merged. A header or footer in the template does not come across to the new document — use the one-document-per-row mode if the letter depends on one.

noteIf a column header contains ( ) $ . * + ? [ ] ^ | or a backslash — “Amount ($)”, say — paste your header row in the box on the left. The script matches placeholders with a pattern in which those characters mean something, and the builder only adds the line that escapes them when it can see the headers.

noteIf two files in your Drive are called “Sign-ups”, the script takes whichever Drive returns first. Rename one.

noteEvery run makes new documents; earlier ones stay in your Drive until you delete them.

Where it goes

Write the letter in a Google Doc. Wherever a value should change, type the column's header in double curly braces: {{First name}}.
Make sure the spreadsheet is called exactly “Sign-ups”, with the headers in row one of its first tab.
In the letter, open Extensions, then Apps Script. Delete the starter code, paste the script, and save.
Reload the letter. A “Mail merge” menu appears at the end of the menu bar.
Click Mail merge, then Create letters. The first time, Google asks for permission: Review permissions, then pick your account. It warns that Google hasn't verified the app — you wrote it, so click Advanced, then Go to (your project) (unsafe). Tick Select all, then Continue. It runs, and it won't ask again.
Click the link in the dialog that appears.

Reading the script

onOpen — runs when the document opens and adds a “Mail merge” menu with one item, “Create letters”. That is why you reload the doc after saving.
getFilesByName('Sign-ups') — finds the spreadsheet in your Drive by its exact name, so no link or ID has to be pasted into the code.
getSheets()[0].getDataRange().getDisplayValues() — every filled row of the FIRST tab, as the text you see (dates and money come out formatted, not as raw numbers).
rows.shift() — takes row one off as the headers. Each header is a placeholder: a column called First name fills {{First name}}.
DocumentApp.create('Letters') — a new document in the root of My Drive. The template itself is never changed.
getChild(j).copy() — each piece of the letter, copied for every row, with a page break between letters.
replaceText — swaps each {{header}} for that row's value.
showModalDialog — a small dialog with the link to what it made. The link opens in a new tab.

Why it's a script, and why it lives here

Search for a Google Docs mail merge and most of what comes up is made by the companies that sell mail merge add-ons. An add-on is a fine answer, but it is a third party with access to your documents. A bound Apps Script is thirty lines you can read, it runs under your own account, and it is free.

It lives on this page rather than under a video because YouTube will not accept the characters < and > in a description, and this script needs both.

Seeing it done

The channel runs this script on camera in How to Mail Merge in Google Docs Without an Add-on. The one-minute version is Google Docs has no mail merge button. Here's a free one.