Send Personalized Emails from Google Sheets in 30 Lines of Apps Script
Mail merge is one of those things that looks like it should be solved. It's been a feature in Word since the 90s. And yet, every team eventually hits the moment when they need to send a personalized email to 200 customers and discover that the actually-good tools cost $30+ per month per user, and the free ones limit you to 50 sends a day.
The script below replaces all of that. It reads recipients and merge fields from a Google Sheet, sends each person a personalized email using your own Gmail account, marks the row as sent, and handles the obvious failure modes. It's about 30 lines of code. It's free. And because it runs through your own Gmail, the emails have your reputation, your signature, and (importantly) your reply-to address.
What you need before starting
- A Google account with Gmail.
- A Google Sheet with at least these columns:
Email,First Name,Status. Add whatever other merge fields you want (Company, Plan, Last Login, etc.). - About 5 minutes.
The spreadsheet layout
Set up Sheet1 with row 1 as headers and rows 2+ as data:
| Email | First Name | Company | Status |
|--------------------|------------|------------|--------|
| [email protected] | Ada | Analytical | |
| [email protected] | Linus | Kernel Co | |
| [email protected] | Grace | COBOL Inc | |Leave the Status column blank initially. The script will populate it with "Sent" + a timestamp after each successful send, so you can resume a partial run without re-emailing anyone.
The full script
In your Sheet, click Extensions → Apps Script, paste this in, replace the subject and body with whatever you want to send, and save:
function sendMergeEmails() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
const headers = data[0];
const emailCol = headers.indexOf('Email');
const firstNameCol = headers.indexOf('First Name');
const companyCol = headers.indexOf('Company');
const statusCol = headers.indexOf('Status');
const subjectTemplate = 'Quick question, {{FirstName}}';
const bodyTemplate = 'Hi {{FirstName}},\n\n' +
'I wanted to reach out about {{Company}} specifically. ' +
'We just shipped a feature I think would be useful for your team.\n\n' +
'Worth a 15-minute call next week?\n\n' +
'Best,\nYour Name';
for (let i = 1; i < data.length; i++) {
if (data[i][statusCol]) continue; // skip already-sent
const email = data[i][emailCol];
if (!email) continue;
const subject = subjectTemplate.replace('{{FirstName}}', data[i][firstNameCol]);
const body = bodyTemplate
.replace(/{{FirstName}}/g, data[i][firstNameCol])
.replace(/{{Company}}/g, data[i][companyCol]);
try {
MailApp.sendEmail({ to: email, subject: subject, body: body });
sheet.getRange(i + 1, statusCol + 1).setValue('Sent ' + new Date().toISOString());
Utilities.sleep(1500); // gentle throttle
} catch (err) {
sheet.getRange(i + 1, statusCol + 1).setValue('FAILED: ' + err.message);
}
}
}Run the function once from the Apps Script editor (▶ button), authorize when prompted, and watch the Status column populate as each row is processed.
Why each piece is there
The "skip already-sent" check. Apps Script has a 6-minute execution limit per run. If your list has 1,000 recipients and the script times out at row 600, you don't want to re-email rows 1-600 on the next run. The Status check makes the script idempotent.
The 1.5 second sleep between sends. Gmail rate-limits hard if you blast 100 sends per second. A small delay keeps you well under any threshold and also gives Google's spam-detection a normal sending pattern. For your own personal Gmail account, the daily limit is 100 emails; Workspace accounts get 1,500-2,000 per day.
The try/catch around the send. One bad email address shouldn't kill the run. By catching and writing the error to the Status column, the script continues processing the rest of the list and you have a record of exactly which addresses failed.
The two-pattern replace (one for subject, regex for body). The body uses a regex with the global flag (/g) so it replaces all occurrences of {{FirstName}}, not just the first one. The subject usually only has one, so a simple string replace is fine.
Adding HTML formatting
The basic sendEmail sends plain text. To send HTML (with links, bold, basic formatting), use the htmlBody option:
const htmlBody = '<p>Hi ' + data[i][firstNameCol] + ',</p>' +
'<p>Take a look at our <a href="https://swiftools.com">new feature</a>.</p>';
MailApp.sendEmail({
to: email,
subject: subject,
body: body, // plain-text fallback
htmlBody: htmlBody // HTML version
});Most modern mail clients render HTML; the body field becomes the fallback for plain-text-only clients. Include both - it's a deliverability best practice and the work to add a plain-text version is trivial.
Adding an unsubscribe link (and why you should)
Even for "transactional" or "internal" emails to your own customer list, having a working unsubscribe is the right move. The cheapest version: a mailto: link that pre-fills a removal request.
const unsubscribe = '\n\n---\nTo stop receiving these: reply with UNSUBSCRIBE.';
const body = bodyTemplate + unsubscribe;Then add an Apps Script rule (Pattern 1 from our Apps Script patterns post) that watches your Gmail for replies containing "UNSUBSCRIBE" and marks the sender's row in the sheet as opted-out. Genuinely takes 10 minutes.
For larger lists where this becomes operationally heavy, the right move is to graduate to a proper email service like Brevo, Buttondown, or MailerLite - all of which handle the List-Unsubscribe header automatically per RFC 8058.
Common pitfalls
You'll hit your daily quota. Personal Gmail caps at 100 emails per day. The script will throw an exception around send #100 and refuse to continue. If you need more, either upgrade to Workspace (raises the cap), split the send across two days, or move to a real ESP.
Recipients in the "To" field, not "BCC". Always use one email address per send. If you put 200 addresses in the "To" field of a single send, recipients will see every other recipient and you'll have a privacy incident. The script above already does this correctly.
Test with one address first. Set the first row of your sheet to your own email, run the script, verify the output, then add the rest of the list. Catching a typo in the merge template after sending 500 emails is unpleasant.
Beware of unintended HTML. If your merge data contains a stray < or > character (a customer with a name like "Sam & Co") and you're sending HTML, the rendering can break. For HTML sends, escape your merge values with a small helper:
function escapeHtml(s) {
return String(s)
.replace(/&/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>');
}What this script doesn't do
This is deliberately a minimal version. Things it doesn't include: open and click tracking, scheduled future sends, A/B subject testing, complex segmentation, attachments, multi-step drip sequences. If you need those, you've outgrown a script and should pay for a real tool.
But for the specific case of "I have a list of 50-1,500 people and I need to send each of them a one-off personalized email" - which covers most outreach, most launch announcements, most invoice reminders, most personalized follow-ups - this 30 lines does everything you actually need, with no recurring cost and complete control over what gets sent.