Mail Merge from Google Sheets: 3 Ways (Free Script, Gmail, Add-on)

A mail merge takes one template and a Google Sheet of data, and produces one personalized output per row — a personalized email for every contact, or a personalized document for every client. There are three good ways to do it from Google Sheets: Gmail's built-in mail merge (easiest, if your plan includes it), a free Apps Script (works on any account), and a merge add-on (when you need PDFs or Word documents, not just emails). Here's how each one works, what it costs, and where it hits a wall.

The 3 routes at a glance

• Way 1 — Gmail's built-in mail merge: zero setup, but only on paid Google Workspace plans, only four merge tags, and it sends emails only.
• Way 2 — Free Apps Script: works on any Google account (including free @gmail.com), any {{Column}} tag you want, but you paste code and live with Apps Script's email quotas.
• Way 3 — A merge add-on (Batch Merge): when the output needs to be a document — an invoice, certificate, or letter as PDF/DOCX per row — not an email body.

Way 1: Gmail's built-in mail merge

Gmail has native mail merge built into the compose window — no add-on, no script. You write one email, turn on mail merge, attach a Google Sheet of recipients, and Gmail sends each person their own individual copy.

Who gets it

This is the first wall: built-in mail merge is not available on free personal Gmail accounts. Per Google's help documentation, it requires one of these Google Workspace plans: Workspace Individual, Business Standard, Business Plus, Enterprise Standard, Enterprise Plus, Education Standard, or Education Plus. Notably, Business Starter is not on the list.

How to use it with a Google Sheet

The limits (verified against Google's docs)

• Emails only. There is no document output of any kind — no PDFs, no letters, no per-row files. If that's what you need, skip to Way 3.
• Only four merge tags — first name, last name, full name, email. You can't merge in an invoice number, an amount due, or any other column from your sheet. Tags work in the body only, not in subject lines or in hyperlinked text.
• Sending limits: up to 1,500 recipients per message, and at most 1,500 mail-merge recipients per day (your account's remaining daily capacity is reserved for regular email).
• Cc/Bcc is restricted: you can include only one Cc or Bcc recipient, and you can't use Cc/Bcc at all when your recipients come from a spreadsheet.
• Incompatible with replies, forwards, Schedule send, and Confidential mode — and Gmail automatically adds a unique unsubscribe link to the bottom of each message.

Way 2: Free Apps Script (works on any Google account)

Google publishes an official mail merge Apps Script sample that works on any account, including free @gmail.com. The pattern: you write a Gmail draft as your template with {{Column Header}} placeholders, and a script in your sheet sends one copy per row, filling each placeholder from that row's columns — any column, not just names.

Set up the sheet and the draft

Your sheet needs a Recipient column (the email address), an empty Email Sent column (the script timestamps it, so re-running never double-sends), and any data columns you want to merge:

Then write a Gmail draft like: "Hi {{First name}}, invoice {{Invoice Number}} for {{Amount Due}} is due this month…" — placeholders must match your column headers exactly (they're case-sensitive). Note the subject line; the script uses it to find the draft.

The script

This is a simplified (but runnable) version of Google's sample — open Extensions → Apps Script from your sheet, paste it, save, and refresh the sheet:

const RECIPIENT_COL = 'Recipient';
const EMAIL_SENT_COL = 'Email Sent';

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Mail Merge')
    .addItem('Send Emails', 'sendEmails').addToUi();
}

function sendEmails() {
  const subjectLine = Browser.inputBox('Mail Merge',
    'Paste the subject line of your Gmail draft template:', Browser.Buttons.OK_CANCEL);
  if (subjectLine === 'cancel' || subjectLine === '') return;

  // Find the Gmail draft whose subject matches, and use it as the template
  const draft = GmailApp.getDrafts()
    .filter(d => d.getMessage().getSubject() === subjectLine)[0];
  if (!draft) throw new Error('No draft found with that subject line.');
  const msg = draft.getMessage();
  const template = { subject: subjectLine, text: msg.getPlainBody(), html: msg.getBody() };

  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getDisplayValues();
  const heads = data.shift();
  const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);

  const out = [];
  data.forEach(row => {
    const rowData = heads.reduce((o, k, i) => { o[k] = row[i] || ''; return o; }, {});
    if (rowData[EMAIL_SENT_COL] !== '') { out.push([rowData[EMAIL_SENT_COL]]); return; }
    // Replace every {{Column Header}} token with that row's value
    const fill = s => s.replace(/{{[^{}]+}}/g, m => rowData[m.replace(/[{}]+/g, '')] || '');
    GmailApp.sendEmail(rowData[RECIPIENT_COL], fill(template.subject),
      fill(template.text), { htmlBody: fill(template.html) });
    out.push([new Date()]);
  });
  sheet.getRange(2, emailSentColIdx + 1, out.length).setValues(out);
}

Run it from the new Mail Merge → Send Emails menu in your sheet, paste the draft's subject line when prompted, and it sends one personalized email per unsent row. Google's full sample on GitHub adds attachment and inline-image support plus more robust escaping — use that version for anything serious.

Honest caveats

• First-run friction: the first time you run it, Google shows an authorization screen warning that the script is unverified ("Google hasn't verified this app"). That's expected for personal scripts — you click through Advanced → Allow — but it surprises people.
• Quotas: per Apps Script's quota table, scripts can email at most 100 recipients/day on free consumer accounts and 1,500/day on Workspace accounts. Hit the cap mid-run and the rest of your rows wait until tomorrow (the Email Sent column tracks where you left off).
• You maintain it. There's no UI, no error report beyond the script log, and if a column header drifts out of sync with a placeholder, that tag silently merges as blank.
• Still emails only. Like Gmail's built-in merge, this script personalizes email — it doesn't generate files.

Way 3: When you need documents, not just emails

Both routes above end at the same wall: the output is an email body. A lot of "mail merge from Google Sheets" jobs actually need a file per row — an invoice PDF for every client, a certificate for every student, an offer letter for every hire, a contract for every vendor. For that you want a document merge add-on. That's exactly what Batch Merge does: one Google Docs (or Slides) template + one Sheet = one finished PDF, DOCX, or Google Doc per row.

Emailing comes back into the picture on the paid tier: Pro ($6.99/month or $59/year) adds per-row email delivery (each generated document sent to that row's email address), scheduled merges, Google Slides templates, and batch notifications. If your job is email-first rather than document-first, it's worth comparing dedicated email-merge tools too — see our honest comparisons vs YAMM and vs Mailmeteor.

Which way should you use? Side-by-side

FAQ

Can Gmail's built-in mail merge create PDFs or documents?

No. Gmail's mail merge sends personalized emails only — there's no document or file output. To generate a PDF, Word file, or Google Doc per spreadsheet row, use a document merge add-on like Batch Merge (Way 3 above) or write a considerably more involved Apps Script against the Docs and Drive APIs.

Is mail merge free in Gmail?

It's included at no extra cost with eligible Google Workspace plans (Workspace Individual, Business Standard and up), but it isn't available on free personal @gmail.com accounts at all. On a free account, the Apps Script route (Way 2) is the no-cost option — with a 100-recipients-per-day script quota.

How many emails can I send per day with each method?

Gmail's built-in merge allows up to 1,500 mail-merge recipients per day on eligible Workspace plans. The Apps Script route is bound by Apps Script's email quota: 100 recipients/day on consumer accounts, 1,500/day on Workspace accounts. Batch Merge's per-row email delivery (Pro) runs through your Google account and is subject to the same Google sending limits.

Do my merge tags have to match my column headers exactly?

For the Apps Script and for Batch Merge, yes — {{Invoice Number}} only fills from a column headed exactly Invoice Number, and matching is case-sensitive. Batch Merge shows you matched and unmatched tags before you run, which catches typos early. Gmail's built-in merge sidesteps this by only offering four fixed tags mapped during spreadsheet setup.

Merge-ready templates