Mail merge from Google Sheets to Gmail, with a PDF for each row

Google gives you two ways to mail merge from a Google Sheet. Gmail’s own mail merge reads names and addresses from a sheet and sends each person a separate copy. It works only on seven Workspace plans, and it attaches the same file to every copy. If each person needs their own document, such as a certificate or an invoice, you need a short Apps Script instead.

This guide covers both. The script below fills a Google Docs template for each row, saves it as a PDF, emails it from Gmail and writes a status back to the sheet, so a second run skips rows already sent. We tested its logic on 5 sample rows in Node.js, with stand-ins for Google’s services, because we had no Google account to run it in. Limits are from Google’s pages, read on October 20, 2026.

Gmail’s mail merge sends one message many times

Gmail’s mail merge works on the email itself. Google’s help page says “each recipient gets a unique copy of the email in which the merge tags are replaced with your details.” Tags come from your column headers: a column named Course becomes @Course in the message. Recipients can’t see who else got it, and their replies arrive in separate threads.

That design sets three limits. You “can’t use merge tags in” subject lines or in linked text. A file you attach is the same file for everyone, and Google warns it is stored in every copy: 500 recipients with a 10 MB attachment use “about 5GB of your storage.” Mail merge also can’t be used for scheduled sends.

So the built-in tool fits an announcement or an event invitation. It does not fit “here is your certificate, Ada”, where the document itself changes per person.

Google Docs has no built-in merge from a sheet that we could find. Its variable chips let you type a value once and see it everywhere in one document, on eligible work or school accounts. You still enter each value by hand. Google’s own merge into Docs is a Docs API sample written in Python, which is a developer project, not a menu.

1. Pick the route by plan and attachment

	Gmail mail merge	Our Apps Script
Accounts	Workspace Individual, Business Standard and Plus, Enterprise Standard and Plus, Education Standard and Plus	Any Google account
A document per person	No: one attachment, same for all	Yes: a PDF made from a Docs template
Tags in the subject	No	Yes
Emails a day	Up to 1,500 recipients	100 on personal Gmail, 1,500 on Workspace
Unsubscribe link	Added to every email	None; add your own line
Remembers who got it	Sent folder banner	Status column in the sheet

Our view: use Gmail’s mail merge when everyone gets the same message and you have an eligible plan. It needs no code and adds the unsubscribe link for you. Use the script when each person needs their own PDF, or when you are on personal Gmail or Business Starter, which are not on Google’s list of eligible plans.

Workspace admins also control who you can reach. Google’s 2023 announcement said Enterprise and Education editions limit mail merge to internal recipients by default, while Business Standard and Plus allow external recipients. Admins can turn external sending on for a group or organizational unit.

2. Set up the sheet with one row per person

Both routes read the same kind of sheet: a header row, then one row per person. Gmail’s mail merge adds two rules. “Contact information must be in the first tab of your spreadsheet and can only contain text.” And a header with characters other than letters or numbers gets a tag by its position, such as @A for the first column, so plain headers like FirstName are easier to use.

For the script, the tab is named Recipients and needs a column called Email. Any other header becomes a placeholder. Our sample sheet:

Email	Name	Course	Date	Price
ada@lind.example	Ada Lind	Bookkeeping basics	10/14/2026	$1,200.00
carl@holm.example	Carl Holm Jr.	Payroll 101	10/15/2026	$900.00

The script reads each cell as it is shown, through getDisplayValues(), which “takes into account date, time and currency formatting.” So $1,200.00 arrives in the PDF as $1,200.00, not as 1200. Format a column in the sheet, and that format is what the reader sees.

3. Send with Gmail’s mail merge when one message fits all

If the built-in route fits, these are Google’s steps:

1. In Gmail, click Compose. On the right of the To line, click Use mail merge and turn on Mail Merge.
2. Click Add from a spreadsheet, pick the sheet and click Insert.
3. Choose the columns for Email, First name and, if you have one, Last name. Click Finish.
4. In the message, type @, pick a tag such as @Course, and press Enter.
5. Send. The message appears in Sent with a “Sent with mail merge” banner.

If a recipient is missing a value for a tag, Gmail shows an error. You can type a default there, so “Hi @firstname” becomes “Hi friend”, or fix the sheet. Google’s page describes no preview step, so send one merge to yourself first with your own address as the only row.

Two catches. With recipients from a sheet you “can’t include Cc or Bcc recipients.” And Gmail adds an unsubscribe link to each email but you “can’t get a list of all unsubscribed recipients.” You get one notification email per unsubscribe, so keep a note of them.

4. Build the Docs template with placeholders in the body

The script route starts with a normal Google Doc. Write the certificate, letter or invoice once, and put each column name in double braces where the value should go: {{Name}}, {{Course}}, {{ Date }}. Spaces inside the braces are allowed. The names must match the headers exactly, capitals included.

Our test template had a header line, four body lines and a footer:

Example Training
Certificate
{{Name}}
has completed {{Course}} on {{ Date }}.
Fee paid: {{Price}}
Issued to {{Name}}

The script fills the body, the header and the footer. It reads only the first tab: Google’s reference says getBody() “retrieves the first tab’s Body”, and the same holds for the header and footer. Keep the template on one tab.

Copy the Doc’s ID from its address bar, the long string between /d/ and /edit. Make a Drive folder for the PDFs and copy its ID, the part of its address after folders/.

5. Paste the script and set CONFIG

Open the sheet and choose Extensions, then Apps Script. That makes a script bound to the sheet. Paste the code over the empty myFunction, edit CONFIG, save, and reload the sheet. A Mail merge menu appears.

The first run asks you to authorize the script. Sending through GmailApp needs full Gmail access, the https://mail.google.com/ scope. Google’s own mail merge sample notes you may see “This app isn’t verified”; for a script you wrote, choose Advanced, then go to the project.

// Mail merge: each row of a sheet becomes a PDF made from a Docs template and a Gmail message with it attached.
// Paste into Extensions > Apps Script in the spreadsheet, edit CONFIG, reload the sheet, use the Mail merge menu.
const CONFIG = {
  sheet: 'Recipients',                    // tab with a header row and a column named Email
  templateId: 'PASTE_TEMPLATE_DOC_ID',    // the Docs template, with {{Column name}} placeholders
  folderId: 'PASTE_DRIVE_FOLDER_ID',      // where the PDFs are saved
  fileName: 'Certificate {{Name}}',
  subject: 'Your certificate, {{Name}}',
  body: 'Hi {{Name}},\n\nYour certificate for {{Course}} is attached.\n\nBest regards,\nAnna',
  senderName: 'Anna at Example Training',
  optional: [],                           // columns that may be empty; any other empty placeholder skips the row
  skipDuplicates: true,                   // a second row with an address already sent to is skipped
  keepDocs: false,                        // true keeps each filled Doc copy next to its PDF
  testTo: '',                             // your own address: sends up to 3 rows to you and writes nothing back
  maxPerRun: 50,
};
const STATUS = 'Status', SENT_AT = 'Sent at';
const TIME_BUDGET_MS = 4.5 * 60 * 1000;   // stop well before the 6-minute limit per run

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Mail merge').addItem('Send pending rows', 'sendMerge').addToUi();
}

function sendMerge() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) return say('Another run is still sending. Try again in a minute.');
  try { return say(run()); } finally { SpreadsheetApp.flush(); lock.releaseLock(); }
}

function run() {
  const started = Date.now();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.sheet);
  if (!sheet) throw new Error('No tab named ' + CONFIG.sheet);
  const data = sheet.getDataRange().getDisplayValues();
  const header = data[0].map(h => h.trim());
  if (header.indexOf('Email') < 0) throw new Error('No column named Email');
  const test = Boolean(CONFIG.testTo);
  for (const name of [STATUS, SENT_AT]) {
    if (!test && header.indexOf(name) < 0) { header.push(name); sheet.getRange(1, header.length).setValue(name); }
  }
  const emailCol = header.indexOf('Email'), statusCol = header.indexOf(STATUS), sentAtCol = header.indexOf(SENT_AT);
  const mark = (row, text) => test ? console.log(`row ${row}: ${text}`) : sheet.getRange(row, statusCol + 1).setValue(text);

  const seen = new Map();                 // address -> first row that was sent, or may have been
  data.forEach((r, i) => { if (i && /^(Sent|Sending|Check)/.test(r[statusCol] || '')) seen.set(norm(r[emailCol]), i + 1); });
  const unsure = data.filter((r, i) => i && /^(Sending|Check)/.test(r[statusCol] || '')).length;

  let quota = MailApp.getRemainingDailyQuota(), sent = 0, stop = '';
  for (let i = 1; i < data.length; i++) {
    const row = i + 1, r = data[i], status = r[statusCol] || '';
    if (/^(Sent|Sending|Check)/.test(status)) continue;   // done, or waiting for a person to look
    if (r.every(v => v === '')) continue;                 // blank row
    const email = norm(r[emailCol]);
    if (!email) { mark(row, 'Skipped: no email'); continue; }
    if (!/^[^\s@,;]+@[^\s@,;]+\.[^\s@,;]+$/.test(email)) { mark(row, 'Skipped: not one email address'); continue; }
    if (CONFIG.skipDuplicates && seen.has(email)) { mark(row, 'Skipped: same address as row ' + seen.get(email)); continue; }
    if (sent >= (test ? 3 : CONFIG.maxPerRun)) { stop = 'the per-run limit'; break; }
    if (Date.now() - started > TIME_BUDGET_MS) { stop = 'the time budget'; break; }
    if (quota < 1) { stop = 'the daily email quota'; break; }

    const values = Object.create(null);    // no inherited keys, so {{constructor}} is not a column
    header.forEach((h, c) => { values[h] = (r[c] || '').trim(); });
    let pdf, subject, body;
    try {
      subject = fill(CONFIG.subject, values);
      body = fill(CONFIG.body, values);
      pdf = makePdf(values);
    } catch (e) { mark(row, 'Error: ' + e.message); continue; }     // nothing was sent; the next run tries again

    if (!test) { mark(row, 'Sending'); SpreadsheetApp.flush(); }  // a crash after this line leaves a visible trace
    try {
      GmailApp.sendEmail(test ? CONFIG.testTo : email, subject, body, { attachments: [pdf], name: CONFIG.senderName });
    } catch (e) {
      mark(row, 'Check: ' + e.message + '. Look in Sent, then clear this cell to retry.');
      continue;
    }
    quota--; sent++;
    seen.set(email, row);
    if (!test) { mark(row, 'Sent'); sheet.getRange(row, sentAtCol + 1).setValue(new Date()); }
  }
  return `Sent ${sent}` + (test ? ' test emails to ' + CONFIG.testTo : '')
    + (stop ? `. Stopped at ${stop}; run again to continue.` : '.')
    + (unsure ? ` Rows marked Sending or Check: ${unsure}. Look in Sent before clearing them.` : '');
}

function say(msg) {
  console.log(msg);
  SpreadsheetApp.getActiveSpreadsheet().toast(msg, 'Mail merge', 15);
  return msg;
}

function makePdf(values) {
  const folder = DriveApp.getFolderById(CONFIG.folderId);
  const name = fill(CONFIG.fileName, values);
  const copy = DriveApp.getFileById(CONFIG.templateId).makeCopy(name, folder);
  try {
    const doc = DocumentApp.openById(copy.getId());
    for (const part of [doc.getBody(), doc.getHeader(), doc.getFooter()]) {
      if (!part) continue;
      const keys = new Set([...part.getText().matchAll(/\{\{\s*([^{}]+?)\s*\}\}/g)].map(m => m[1]));
      for (const key of keys) replaceLiteral(part, key, lookup(values, key));
      const left = part.getText().match(/\{\{[^{}]*\}\}/);
      if (left) throw new Error('template still contains ' + left[0]);
    }
    doc.saveAndClose();
    const pdf = copy.getAs('application/pdf').setName(name + '.pdf');  // getAs alone cuts names after a period
    if (!CONFIG.testTo) folder.createFile(pdf);   // a test run keeps no PDFs
    return pdf;
  } finally {
    if (!CONFIG.keepDocs) copy.setTrashed(true);
  }
}

// Inserts the value as plain text. replaceText() would read the template's placeholder as a regular expression.
function replaceLiteral(part, key, value) {
  const pattern = '\\{\\{\\s*' + key.replace(/[.*+?^${}()|[\]\\]/g, '\\$&') + '\\s*\\}\\}';
  for (let hit = part.findText(pattern); hit; hit = part.findText(pattern)) {
    const text = hit.getElement().asText();
    const start = hit.isPartial() ? hit.getStartOffset() : 0;
    const end = hit.isPartial() ? hit.getEndOffsetInclusive() : text.getText().length - 1;
    if (value) text.insertText(start, value);   // an empty optional value only deletes
    text.deleteText(start + value.length, end + value.length);
  }
}

function fill(template, values) {
  return template.replace(/\{\{\s*([^{}]+?)\s*\}\}/g, (_, key) => lookup(values, key));
}

function lookup(values, key) {
  if (!(key in values)) throw new Error('no column named ' + key);
  const v = values[key];
  if (!v && !CONFIG.optional.includes(key)) throw new Error('empty ' + key);
  if (v.includes('{{')) throw new Error(key + ' contains {{');
  return v;
}

function norm(v) { return String(v || '').trim().toLowerCase(); }

The status column stops a second run from sending twice

The status column is the memory. Before each send the script writes Sending and calls SpreadsheetApp.flush(), which “applies all pending Spreadsheet changes.” After the send it writes Sent and the time. Any later run skips rows marked Sent, Sending or Check.

A failed send never retries by itself. If sendEmail throws an error, the row gets Check and the error text. If the run dies between the send and the Sent mark, the row stays Sending. Neither tells you whether the email left. So the script waits for you: look in Gmail’s Sent folder, and clear the cell only if the email is not there. We chose a missed email over a double one.

Duplicates and bad cells are skipped. A second row with the same address, in any capitals, gets Skipped: same address as row 2. Set skipDuplicates to false if one person should get two documents. A cell with two addresses is refused, because sendEmail takes “the addresses of the recipient” and would send to both. The check catches a malformed address, not a dead one. Clean old lists first: Gmail’s help for personal accounts says that after many emails bounce, you “may be able to send emails again after 24 hours.”

Empty values stop the row. An empty cell behind a placeholder gives Error: empty Name instead of “Hi ,”. List columns that may be empty in optional. A misspelled placeholder such as {{Nmae}} gives Error: no column named Nmae. Error rows are tried again on the next run, since nothing was sent.

Values go in as plain text. Apps Script’s replaceText() reads its search pattern as a regular expression, Google’s Body reference says. It doesn’t say how the replacement treats characters like $. So the script finds each placeholder and inserts the value with insertText() instead. A value like $1,200.00 stays as typed.

PDF names keep their periods. Google’s File reference says getAs() treats the text after the last period as an extension and replaces it: “ShoppingList.12.25.2014” becomes “ShoppingList.12.25.pdf”. A row for “Carl Holm Jr.” would lose part of its name, so the script sets the name again.

Two runs can’t overlap. A script lock “prevents any user from concurrently running a section of code.” Click the menu twice and the second click waits 10 seconds, then gives up.

6. Send a test to yourself first

Set testTo to your own address and run Mail merge, then Send pending rows. The script makes the PDFs for the first 3 sendable rows and emails all 3 to you. It writes nothing to the sheet and saves no PDFs in the folder.

Open each PDF. Check the name, the dates and the money columns, and that no {{ is left anywhere. Then clear testTo.

7. Send for real and read the Status column

Run Send pending rows again. A popup in the corner of the sheet reports the result, such as Sent 3. Each row now has one of these:

Status	Meaning	What to do
Sent	Email sent; Sent at has the time	Nothing
Skipped: ...	No email, two addresses, or a duplicate	Fix the cell; the next run tries again
Error: ...	Template or data problem; nothing was sent	Fix it; the next run tries again
Check: ...	Gmail gave an error during the send	Look in Sent, then clear the cell to retry
Sending	The run stopped mid-send	Look in Sent, then clear the cell to retry

The script stops after 50 emails a run, after 4.5 minutes, or when Google’s daily email count runs out. The popup says which. Run it again to continue.

Our test: 5 rows, 10 cases, no Google account

We could not run Apps Script itself. So we ran the script unchanged in Node.js 22 with small stand-ins for SpreadsheetApp, DocumentApp, DriveApp, GmailApp, MailApp and LockService, written to follow Google’s reference pages. The 5 rows were invented, on .example addresses: Ada, a row with no email, Carl Holm Jr., Ada again as ADA@lind.example with a trailing space, and Dana Berg with (Q4) $1 \n in her name.

What the 10 cases showed:

Case	Result
Three runs on the 5 rows	Run 1: 3 sent, no-email row and duplicate skipped. Run 2: 0 sent. Run 3, after we filled in the missing email: that 1 row sent
Gmail error on one row, once	Row marked Check; not resent on 2 more runs
Same, with skipDuplicates off	Same result
Run killed after a send, before Sent	Row stayed Sending; next run sent only the last row
2 emails left in the day’s quota	2 sent, stopped; the rest sent on the next run
Test mode	3 emails to the tester; sheet unchanged; no PDFs kept
{{Nmae}} in the template	0 sent; every row Error; Doc copies trashed
{{toString}} in the template	0 sent; Error: no column named toString
Two addresses in one cell	Refused
Lock already held	Nothing sent

In the first run, our stand-in sheet already showed Sending for each row at the moment its email went out. Values with $, brackets and a backslash landed in the PDF as typed, and “Carl Holm Jr.” kept his full name in the file name. We also broke the script on purpose six ways, such as removing the duplicate check. Each break made at least one case fail, so the cases catch those bugs.

What it does not prove: anything about Google’s servers. Our stand-ins follow the documentation, but the real findText(), PDF export, quotas, timing and Gmail delivery were not part of the run. We don’t know how long one row takes in real Apps Script, so 50 a run is our guess, and the 4.5-minute stop is the backstop. Send yourself a test before any real list.

Know the daily limits before the list grows

Apps Script counts emails by recipient, per user, and the count resets “24 hours after the first request,” according to Google’s quotas page (updated September 3, 2026). That is not midnight. If the day’s first email went out at 3 p.m., the count resets around 3 p.m. the next day.

Limit	Personal Gmail	Google Workspace
Script email recipients a day	100	1,500
Documents created a day	250	1,500
Files converted a day	2,000	4,000
One run	6 minutes	6 minutes
Attachments per email	25 MB in total	25 MB in total
Gmail’s own daily send limit	500	2,000
Gmail mail merge a day	Not available	1,500 recipients

The first line decides most cases. On personal Gmail the script stops at 100 emails a day, even though Gmail itself allows 500. On Workspace, 1,500 emails and 1,500 Doc copies line up, one each per row.

We assume each template copy counts as a document created; Google’s page doesn’t say. Each PDF export likely counts as a conversion too, since the File reference points getAs() to that quota. The script checks what is left with MailApp.getRemainingDailyQuota(), which Google describes as “the number of recipients you can send emails to for the rest of the day.”

Gmail’s mail merge shares its 1,500 with your normal email. Once your regular and merge emails reach 1,500 in a day, the rest of the 2,000 “are reserved for regular emails only.” Google’s Workspace limits page applies its limits “over a rolling 24-hour period” and notes that “Quotas might be different if you’re using Google Apps Script.” It doesn’t say how the script’s 1,500 and Gmail’s 2,000 combine, so plan as if they share one budget.

Gmail’s own limits block more than the merge. A Workspace user who reaches one “can’t send new messages for up to 24 hours,” and a personal account can send again “within 1 to 24 hours.” Run a big merge on a quiet day, not before a deadline.

Keep newsletters out of both routes past 5,000 a day

Both routes send from your own address, so its reputation carries the risk. Google’s sender guidelines warn that when mail from a domain is often reported as spam, “future messages from you are more likely to be marked as spam.” They ask every sender to keep reported spam “below 0.3%,” and say plainly: “Don’t send messages to people who didn’t sign up to get messages from you.”

Stricter rules start at volume. Google’s sender FAQ defines a bulk sender as one sending “close to 5,000 messages or more to personal Gmail accounts within a 24-hour period,” counted across the whole domain. One person can’t get there with these limits.

A hypothetical team of four on one company domain, each running a 1,500 merge to customers on gmail.com, would send 6,000 and cross it. And “senders who meet the above criteria at least once are permanently considered bulk senders.”

Bulk senders need SPF, DKIM and DMARC set up for the domain. Their marketing messages need one-click unsubscribe, done with List-Unsubscribe headers; a link in the body alone doesn’t count. Our script can’t add those headers: the options Google documents for sendEmail have no field for them.

The rule applies only to marketing. “Transactional messages are excluded,” the FAQ says, naming confirmations as examples.

That gives a simple split:

• Certificates, invoices, offer letters, one per person: the script is a good fit. Keep business and promotion apart; Google’s guidelines say not to put promotions in receipts.
• One announcement to people who opted in: Gmail’s mail merge, which adds the unsubscribe link for you.
• A newsletter, a sales list, or thousands a day from your domain: a dedicated email service. At that volume Google requires one-click unsubscribe headers on marketing mail, and its guidelines recommend dropping addresses that bounce again and again. You also need a list of who left. Gmail’s merge can’t give you that list, and the script tracks none of it. Check a service’s documentation for those before you move the list, and check that it signs your mail with SPF and DKIM.

Check the Sent folder after the first real run

Whatever you pick, the first real run is the one to watch. Open Gmail’s Sent folder and compare it with the rows marked Sent. The counts should match, and each PDF should carry the right name.

If you also want to fill this kind of sheet from incoming email, our Gmail to Google Sheets guide has a tested script for that, and our checks for scheduled scripts help a silent failure speak up. More guides are under automation.

Start with a copy of your real sheet holding just two rows, both with your own address. With skipDuplicates on, one row should come back Sent and the other Skipped: same address as row 2.

How we made this

We read Google’s help, quota and sender pages on October 20, 2026. The script ran unchanged in Node.js 22 against stand-ins we wrote for Google’s services, on 5 invented rows and 10 cases. It has not run in Apps Script or sent a real email.

Sources

• Google, Send personalized emails with mail merge (Gmail Help: plans, steps, limits, unsubscribe, restrictions)
• Google Workspace Updates, Google Sheets now integrated with Gmail mail merge (June 2023; admin defaults)
• Google, Insert and use variable chips (Docs Help)
• Google, Mail merge with Docs API and Create a mail merge with Gmail and Google Sheets (developer samples)
• Google, Quotas for Google Services (Apps Script; updated September 3, 2026)
• Google, Limits for sending and getting mail (personal Gmail) and Gmail sending limits in Google Workspace
• Google, Email sender guidelines and Email sender guidelines FAQ
• Google, Apps Script reference: Body, Document, File, GmailApp, MailApp, LockService, Range, SpreadsheetApp, Container-bound scripts
• 28creation test, October 20, 2026: 5 invented rows and 10 cases, our script run unchanged in Node.js 22 against stand-ins for Google’s services, not in Apps Script or a live Google account