How to mail merge to PDF from Google Sheets

Last updated: 2026-09-22 · Working Apps Script, no add-on required

One row in, one PDF out. This guide fills a Google Docs template with each row's values, exports that document to PDF, and emails it to the address in that row — invoices, certificates, statements, offer letters.

It also covers the one line that silently breaks most versions of this script found online: the replaceText call, which takes a regular expression, not a plain string.

1. Build the template once

Create a Google Doc and write the document exactly as you want it to read, marking the parts that change with double braces. The names inside the braces must match your spreadsheet's header row.

INVOICE

Bill to: {{Name}}
Amount due: {{Amount}}
Due date: {{DueDate}}

Thank you for your business.

Take the template's file ID from its URL — the long segment between /d/ and /edit. Do the same for the Drive folder you want the PDFs written to.

2. Lay out the sheet

One row per document. Header names must match the placeholders, plus an Email column for delivery.

NameEmailAmountDueDate
Dana Okonkwodana@example.com$420.002026-10-15
Rin Takahashirin@example.com$118.502026-10-15

3. The trap: replaceText takes a regex

This is the line that makes scripts fail in ways that are hard to see:

// Looks right. Is not.
body.replaceText('{{Name}}', 'Dana Okonkwo');

DocumentApp's replaceText interprets its first argument as a regular expression. In regex, { and } are quantifier syntax — a{2,3} means "two or three a's". Passing raw braces means you are relying on the engine's error-recovery for a malformed quantifier, which is not something to build on.

Escape them:

// Correct: braces escaped so they are matched literally.
body.replaceText('\\{\\{Name\\}\\}', 'Dana Okonkwo');

The same applies to any value you substitute in: a replacement containing $ can be read as a capture-group reference. Currency amounts are the usual victim. If your values can contain $, prefer a placeholder syntax without special characters, or verify the output before sending anything.

4. The merge script

This makes a copy of the template per row, substitutes every column, exports a PDF, and throws away the intermediate document.

const TEMPLATE_ID = 'PASTE_TEMPLATE_DOC_ID';
const FOLDER_ID   = 'PASTE_OUTPUT_FOLDER_ID';

function mergeToPdf() {
  const sheet  = SpreadsheetApp.getActiveSheet();
  const values = sheet.getDataRange().getValues();
  const header = values.shift();

  const template = DriveApp.getFileById(TEMPLATE_ID);
  const folder   = DriveApp.getFolderById(FOLDER_ID);

  values.forEach((row) => {
    // Build a {ColumnName: value} object for this row.
    const data = {};
    header.forEach((h, i) => { data[h] = row[i]; });
    if (!data.Email) return;

    const title = 'Invoice - ' + data.Name;

    // 1. Copy the template.
    const copy = template.makeCopy(title, folder);
    const doc  = DocumentApp.openById(copy.getId());
    const body = doc.getBody();

    // 2. Substitute every column. Braces escaped — see section 3.
    Object.keys(data).forEach((key) => {
      body.replaceText('\\{\\{' + key + '\\}\\}', String(data[key]));
    });

    // 3. Flush the edits to Drive before exporting.
    doc.saveAndClose();

    // 4. Export to PDF and keep only that.
    const pdf = copy.getAs(MimeType.PDF).setName(title + '.pdf');
    folder.createFile(pdf);
    copy.setTrashed(true);
  });
}

doc.saveAndClose() is required, not tidiness. Exporting the file before the document's pending edits are written can hand you a PDF of the untouched template — with the placeholders still in it. This failure is easy to miss because the script reports success.

5. Attach it to that row's email

Replace step 4 with this to send the PDF instead of only filing it. The PDF never has to be saved to Drive at all — getAs returns a blob you can attach directly.

    doc.saveAndClose();

    const pdf = copy.getAs(MimeType.PDF).setName(title + '.pdf');

    MailApp.sendEmail({
      to: data.Email,
      subject: 'Invoice for ' + data.Name,
      body: 'Hi ' + data.Name + ',\n\n'
          + 'Your invoice for ' + data.Amount + ' is attached. '
          + 'It is due on ' + data.DueDate + '.\n\nThanks.',
      attachments: [pdf]
    });

    copy.setTrashed(true);

Two ceilings apply to the sending half, both from Google's published Apps Script quotas: 25 MB of attachments per message, and a daily recipient cap of 100 for consumer accounts or 1,500 for Google Workspace accounts. The companion guide covers how to keep a long run inside those limits and make it resumable: how to send bulk email from Google Sheets.

6. The permission cost of doing it this way

Worth knowing before you authorise the script. DriveApp — used above to fetch the template and the output folder — is backed by the full Drive scope. Authorising this script grants it the ability to read every file in your Drive, not only the template you named.

For a script you wrote yourself and can read line by line, that is usually an acceptable trade. It is a much worse trade for a third-party add-on, which is why the narrower drive.file scope exists: it grants access only to files the user explicitly opens with or creates through that app.

When you evaluate any mail-merge add-on, this is the thing to check first — a tool asking for full Drive access to fill one template is asking for far more than the job needs.

7. Before a real run

If you would rather not maintain this

Bulk Email & PDF Merge is a Google Sheets add-on doing exactly the job on this page — template per row, PDF per row, delivered to that row's recipient, with resumable batches and a preview that sends nothing. It requests drive.file rather than full Drive access, and no Gmail API scope at all.

It is still in development and not yet on Google Workspace Marketplace. The script above is complete and yours to keep either way.