A spreadsheet full of recipient names, email addresses, and personalization details is one of the most common ways teams keep track of who needs an email, which makes it a natural data source for a mail merge.
This script reads every row of a sheet, builds a personalized subject and body from a template, sends the email with GmailApp.sendEmail, and writes Sent back into a status column so the same row is never emailed twice.
Personalization works by substituting column values into a template string wherever a bracketed column name appears, so the same script and template work for recipient lists with different sets of fields.
Because sending happens row by row with a status check first, you can safely re-run the function after adding new rows without worrying about resending to people who already received their email.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Sheet: Recipients | Role |
|---|---|---|
| A | Name | Greeting |
| B | GmailApp To | |
| C | Company | Body merge field |
| D | Status | Blank until Sent |
What It Does
The script reads every row below the header, skips rows already marked Sent in the status column, builds a personalized subject and body from the row's values, and sends the email through GmailApp.sendEmail.
After a successful send, the script writes Sent and a timestamp back into that row's status columns immediately, so a script that fails partway through leaves an accurate record of exactly how far it got.
Prerequisites
Your sheet needs a header row whose column names match the bracketed fields used in your subject and body templates, since the merge function looks up template tokens by exact column header text.
Confirm your Gmail sending quota is high enough for the size of your recipient list; consumer accounts and Workspace accounts have different daily limits on GmailApp.sendEmail.
Walkthrough
Add Status and SentAt columns to your sheet if they do not already exist, fill in SUBJECT_TEMPLATE and BODY_TEMPLATE at the top of the script with bracketed field names, then paste in the sendMailMerge function.
Add one test row using your own email address, run sendMailMerge manually, and confirm you received an email with the correct personalization before pointing it at the real recipient list.
Once the test row's email looks right and its Status cell shows Sent, run the function again against the full list and spot-check a few more of the outgoing emails.
Edge Cases
Rows with an empty email address are skipped and logged as errors rather than causing GmailApp.sendEmail to throw and stop the entire merge for every row after it.
If a template references a column name that does not exist in the header row, that token is left in the sent email unchanged, so double-check bracket spelling against the actual header text before a real send.
Testing
Run the merge against three test rows with different personalization values and confirm each received email contains the correct name and details rather than values from another row.
Manually mark one row as already Sent before running the script and confirm that row is skipped entirely, both in terms of not sending an email and not overwriting its existing timestamp.
Hardening
Add a small Utilities.sleep delay between sends to stay comfortably under Gmail's per-minute sending limits when merging into a large list.
Wrap each row's send call in try/catch and write the specific error message into a dedicated column, so a handful of bad email addresses do not prevent the rest of the list from being processed.
Variations
Swap plain text bodies for an HTML body built from a Doc template, passing htmlBody to GmailApp.sendEmail for richer formatting than plain text allows.
Add a batch limit constant so a single run only sends the next 50 unsent rows, which keeps individual executions well inside Apps Script's execution time limit for very large lists.
sendMailMerge.gs
sendMailMerge reads every unsent row from a Recipients sheet, substitutes bracketed column names into subject and body templates, sends the email, and marks the row Sent.
// Merge sheet rows into personalized emails and mark them Sent
function sendMailMerge() {
var SHEET_NAME = 'Recipients';
var SUBJECT_TEMPLATE = 'Hello [Name], your update is ready';
var BODY_TEMPLATE = 'Hi [Name],\n\nYour account [Account] was updated on [Date].\n\nThanks!';
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
var data = sheet.getDataRange().getValues();
var headers = data[0];
var statusCol = headers.indexOf('Status');
var sentAtCol = headers.indexOf('SentAt');
for (var i = 1; i < data.length; i++) {
var row = data[i];
if (row[statusCol] === 'Sent' || !row[headers.indexOf('Email')]) continue;
var subject = SUBJECT_TEMPLATE;
var body = BODY_TEMPLATE;
for (var c = 0; c < headers.length; c++) {
var token = '[' + headers[c] + ']';
subject = subject.split(token).join(row[c]);
body = body.split(token).join(row[c]);
}
GmailApp.sendEmail(row[headers.indexOf('Email')], subject, body);
sheet.getRange(i + 1, statusCol + 1).setValue('Sent');
sheet.getRange(i + 1, sentAtCol + 1).setValue(new Date());
}
}- Line 15: Skipping rows already marked Sent, or missing an email address entirely, is what makes re-running the merge safe after adding new rows.
- Line 20: Building the token from the header name means the same substitution loop works for any column, not just the ones named explicitly in the templates.
- Line 21: split and join together perform a global string replacement, swapping every occurrence of a bracketed token with that row's actual value.
- Line 25: sendEmail fires only after every token has been substituted, so the subject and body sent are always fully personalized.
- Line 26: Writing Sent immediately after a successful send means a script that fails partway through leaves an accurate record of exactly how far it got.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: mail merge from sheet
- 1Sheet has Status and SentAt columns alongside recipient data
- 2SUBJECT_TEMPLATE and BODY_TEMPLATE written with bracketed field names
- 3Gmail sending quota checked against recipient list size
- 4Test row added with your own email address
- 5Test send confirmed correct personalization before a real run
- 6Already-sent row confirmed to be skipped on a second run
- 7Delay or batching added for large recipient lists
Frequently asked questions
The script skips that row rather than calling GmailApp.sendEmail with a blank recipient, which would otherwise throw and could stop the whole merge.
Yes, just clear that row's Status cell manually and it will be picked up again the next time the script runs.
Pass an htmlBody option to GmailApp.sendEmail as described in the variations section, built from an HTML template instead of a plain string.
Add one row with your own email address first, confirm the output looks correct, and only then run the merge against the full recipient list.
It can, which is why the hardening section suggests adding a short delay between sends and, in variations, batching runs to stay under daily and per-minute limits.
Yes, as long as the header row's column names match the bracketed tokens used in your templates, the same function works unchanged.