Originally written for bulldo.gs — republished here with the canonical link pointing home.
I have a spreadsheet of names and email addresses and I want to send each person a personalized message from my Gmail account without copy-pasting or using a paid tool.
// Mail merge: Sheet cols A=Name, B=Email, C=Sent
// Run from Apps Script; authorize Gmail + Sheets scopes
function sendMerge() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange().getValues();
var quota = MailApp.getRemainingDailyQuota();
var sent = 0;
for (var i = 1; i < rows.length; i++) {
if (rows[i][2] === 'Sent') continue;
if (sent >= quota) {
Logger.log('Quota reached at row ' + (i + 1));
break;
}
var name = rows[i][0];
var email = rows[i][1];
var subject = 'Hey ' + name + ', here is your update';
var body = 'Hi ' + name + ',\n\nYour personalized content goes here.\n\nThanks';
MailApp.sendEmail(email, subject, body);
sheet.getRange(i + 1, 3).setValue('Sent');
sent++;
}
}
Set up your sheet and open the script editor
Put names in column A, email addresses in column B, and leave column C blank — the script writes 'Sent' there as it goes. Header row in row 1 is assumed; the loop starts at index 1 (row 2) to skip it. Open the script editor from Extensions > Apps Script, paste the function, and save.
The first time you run sendMerge() Google will ask you to authorize two scopes: Sheets (read/write the active spreadsheet) and Gmail (send mail on your behalf). Both are required. If you only see a Sheets prompt, delete the file and re-paste — a cached partial authorization sometimes skips the Gmail scope on older script files.
Why the Sent column is the whole point
Consumer Google accounts cap at roughly 100 outgoing recipients per 24-hour rolling window via MailApp. If your list has 200 rows and you run the script at 11 pm, it will send 100 and log 'Quota reached at row 101'. Without the Sent check, a second run the next morning resends rows 1-100 to people who already heard from you.
The guard is two lines: skip rows already marked Sent, and check getRemainingDailyQuota() before each send rather than once at the top. Quota resets at midnight Pacific, not at a fixed offset from your first send, so a pre-loop check can be stale by the time you reach row 80. Checking inside the loop costs nothing and catches the edge.
I keep a copy of this pattern in a utils file I paste into every client sheet project — the first time I forgot the Sent column I sent a 'welcome' message to a 40-person list twice in one afternoon. Nobody complained loudly, but it was avoidable.
Personalize beyond name substitution
The body string uses plain concatenation rather than a template, which makes the highlighting cleaner but also makes it easy to pull additional columns. Add a column D for a custom note, read it as rows[i][3], and concatenate it into the body the same way. You can also pass an options object as a fourth argument to MailApp.sendEmail() to set a replyTo address, cc, or an HTML body — the signature is sendEmail(recipient, subject, body, options).
For HTML email, set options.htmlBody to a string of markup. The plain body argument still needs to be present as a fallback for mail clients that strip HTML. Keep both in sync or you will confuse recipients whose clients show the plain version.
Workspace (paid Google) accounts get a higher quota — 1,500 recipients per day — and can send as an alias using GmailApp.sendEmail() with a 'from' field. MailApp does not support alias sending; if that matters, swap the send call to GmailApp.sendEmail() with the same argument order.
FAQ
What is the daily email limit for Apps Script MailApp?
Consumer (free) accounts: approximately 100 recipients per 24-hour window. Google Workspace accounts: 1,500 per day. getRemainingDailyQuota() returns the live number so you can check mid-loop rather than guessing.
Can I send HTML email with this script?
Yes. Pass a fourth argument to MailApp.sendEmail(): an options object with an htmlBody property set to your HTML string. Keep the plain-text body argument in place as a fallback — it is required even when htmlBody is set.
The script sent duplicates. What happened?
Most likely the Sent column check was missing or the column index was wrong (columns are zero-indexed in getValues(), so column C is index 2, not 3). Confirm sheet.getRange(i + 1, 3).setValue('Sent') writes to the correct column before your next run.
How do I run this on a schedule instead of manually?
In the Apps Script editor, open Triggers (the clock icon), add a time-driven trigger pointing to sendMerge, and set your interval. The Sent column makes repeated scheduled runs safe — already-sent rows are skipped automatically.
Want the plain-English version? Describe the automation at bulldo.gs and get working Apps Script back — free, no login.
