Audeza Doc Merge

How to make a PDF from each row in Google Sheets

File > Download > PDF saves your sheet as a table. If you need one document per row, like a letter, a confirmation or a certificate, you need a mail merge: a template with placeholders, filled in once per row and saved as a PDF. Here's how to do it with a short Apps Script, and with an add-on.

If you only need the table as a PDF

Click File > Download > PDF. In the export settings you can choose the current sheet, the whole workbook or just the selected cells, and set the orientation and scale. You get the grid as it looks on screen. There's no setting that turns each row into its own page or file.

Step 1: write the template

Make a Google Docs™ file that looks the way the finished document should. Wherever a value from the sheet belongs, type the column header between double angle brackets. For a sheet with the headers Name, Company and Start date:

Dear <<Name>>, welcome to <<Company>>. Your first day is <<Start date>>.

Keep the header text short and plain. It's what you'll type into the template, and it has to match.

Option A: a short Apps Script

This works on any Google account and costs nothing. In your spreadsheet, open Extensions > Apps Script, replace what's there with the script below, and paste in two IDs: the template's (the long part of its URL between /d/ and /edit) and the output folder's (the last part of the folder's URL). Click Run and approve the permissions.

// Fills a Google Docs template once per row and saves each copy as a PDF.
const TEMPLATE_ID = 'PASTE_TEMPLATE_ID';
const FOLDER_ID = 'PASTE_FOLDER_ID';

function makePdfs() {
  const rows = SpreadsheetApp.getActiveSheet().getDataRange().getDisplayValues();
  const headers = rows.shift();
  const template = DriveApp.getFileById(TEMPLATE_ID);
  const folder = DriveApp.getFolderById(FOLDER_ID);
  rows.forEach((row, i) => {
    const copy = template.makeCopy('Row ' + (i + 2), folder);
    const doc = DocumentApp.openById(copy.getId());
    headers.forEach((header, col) => replaceTag(doc.getBody(), header, row[col]));
    doc.saveAndClose();
    folder.createFile(copy.getAs('application/pdf')).setName(row[0] + '.pdf');
    copy.setTrashed(true);
  });
}

// Replaces <<header>> with the value, as plain text (no regex surprises).
function replaceTag(body, header, value) {
  const pattern = ('<<' + header + '>>').replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  let found;
  while ((found = body.findText(pattern))) {
    const text = found.getElement().asText();
    const start = found.getStartOffset();
    text.deleteText(start, found.getEndOffsetInclusive());
    if (value) text.insertText(start, value);
  }
}

For each row it copies the template, fills in every <<Header>>, saves a PDF named after the first column, and deletes the working copy. It uses getDisplayValues, so dates and amounts look the way the sheet shows them.

What it doesn't do, and what you'd add next:

Option B: an add-on

Audeza Doc Merge does the same job, with the items on that list already handled.

  1. Open Extensions > Audeza Doc Merge > Open and click New merge job.
  2. Pick the tab, the template (a Google Doc or Google Slides™ file) and the output folder.
  3. Set the file name with tags, like Welcome letter - <<Name>>.
  4. Click Test with first row, check the PDF, then Save.
  5. Click Merge now to make PDFs for the rows you already have.

Each row gets a status cell, "Merged" with a link to its PDF, so nothing is made twice. To redo a row, clear its status. Merge now runs in short steps with a progress bar, so a sheet with 500 rows finishes instead of stopping at 6 minutes. New rows merge every hour on their own, or within a minute if they come from a Google Form. The add-on can open only the template and folder you pick, not the rest of your Drive.

Mail merge to PDF, then email it

If each person should get their PDF by email, add an email column to the sheet. In Doc Merge, turn on Email each document, send to <<Email>>, and use tags in the subject and message. The PDF goes as an attachment from your own Gmail™ account. Google allows about 100 recipients a day on a free account and 1,500 on Google Workspace. When that runs out, the rest wait for the next day instead of failing.

Invoices with several line items

A merge fills one document per row, and each tag gets one cell. A table that grows with the number of items, one table row per sheet row, isn't supported yet. For a few items, give each its own columns (Item 1, Price 1, Item 2, Price 2) and leave the unused ones empty; empty tags print as nothing. Or join the items into a single cell with TEXTJOIN.

Which one to use

A one-off batch, a sheet of under a hundred rows, and you're comfortable in the script editor: the script is enough. Rows that keep coming, emails to send, or a Drive your admin locks down: use an add-on. Doc Merge is free for 50 PDFs a month and $49 a year for unlimited.

Starting from a Google Form instead? See Google Forms to PDF.