How to Set Up a Mail Merge in Google Sheets with Gmail Using Apps Script and Filter Conditions
How to Set Up a Mail Merge in Google Sheets with Gmail for Personalized Bulk Emails
Sending personalized bulk emails doesn’t require expensive third-party tools. With Google Sheets, Gmail, and a simple Apps Script, you can create a powerful mail merge system that sends customized emails to hundreds of recipients — complete with filter conditions to target exactly the right audience. This step-by-step guide walks you through the entire process from spreadsheet setup to automated sending.
What Is a Mail Merge with Google Sheets and Gmail?
A mail merge is a method of sending personalized emails to multiple recipients by pulling individual data from a spreadsheet. Each email is customized with the recipient’s name, company, order details, or any other dynamic field stored in your Google Sheet. When combined with Apps Script, Gmail becomes a programmable email engine capable of sending filtered, conditional bulk messages without any paid add-ons.
Prerequisites Before You Begin
- A Google account with access to Google Sheets, Gmail, and Apps Script- Basic understanding of spreadsheet columns and rows- Gmail daily sending limits: 500 emails/day for free accounts, 2,000 for Google Workspace- Recipients who have opted in to receive your emails
Step-by-Step Guide to Building Your Mail Merge
Step 1: Prepare Your Google Sheet
Create a new Google Sheet and set up your columns. At minimum, include the following headers in Row 1:
| Column | Header Name | Example Data |
|---|---|---|
| A | john@example.com | |
| B | FirstName | John |
| C | Company | Acme Corp |
| D | Status | Active |
| E | Plan | Premium |
| F | EmailSent | (leave blank) |
Step 2: Open the Apps Script Editor
From your Google Sheet, navigate to Extensions > Apps Script. This opens the built-in code editor where you will write your mail merge function. Delete any default code in the editor.
Step 3: Write the Mail Merge Script with Filter Conditions
Paste the following Apps Script code into the editor:
function sendMailMerge() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
const headers = data[0];
const emailCol = headers.indexOf(‘Email’);
const nameCol = headers.indexOf(‘FirstName’);
const companyCol = headers.indexOf(‘Company’);
const statusCol = headers.indexOf(‘Status’);
const planCol = headers.indexOf(‘Plan’);
const sentCol = headers.indexOf(‘EmailSent’);
for (let i = 1; i < data.length; i++) {
const row = data[i];
// Filter conditions: only send to Active Premium users
if (row[statusCol] !== 'Active' || row[planCol] !== 'Premium') {
continue;
}
// Skip already-sent rows
if (row[sentCol] === 'Sent') {
continue;
}
const email = row[emailCol];
const name = row[nameCol];
const company = row[companyCol];
const subject = 'Exclusive Update for ' + company;
const body = 'Hi ' + name + ',\n\n'
+ 'As a valued Premium member at ' + company + ', '
+ 'we wanted to share an exclusive update with you.\n\n'
+ 'Best regards,\nYour Team';
try {
GmailApp.sendEmail(email, subject, body);
sheet.getRange(i + 1, sentCol + 1).setValue('Sent');
SpreadsheetApp.flush();
} catch (e) {
sheet.getRange(i + 1, sentCol + 1).setValue('Error: ' + e.message);
}
}
SpreadsheetApp.getUi().alert(‘Mail merge complete!’);
}
This script iterates through each row, applies your filter conditions (Active status and Premium plan), personalizes the email with the recipient’s name and company, and marks each row as sent.
Step 4: Add HTML Email Support (Optional)
For richer formatting, replace the GmailApp.sendEmail call with HTML body support:
const htmlBody = ‘
Hello ’ + name + ’
’
- ‘
As a Premium member at ’ + company
- ’, here is your exclusive update.
’;
GmailApp.sendEmail(email, subject, ”, { htmlBody: htmlBody });This allows you to include bold text, links, images, and structured layouts in your merged emails.
Step 5: Authorize and Run the Script
- Click the Save icon (or Ctrl+S) in the Apps Script editor.- Select
sendMailMergefrom the function dropdown at the top.- Click the Run button (▶).- On first run, Google will prompt you to authorize the script. Click Review Permissions, select your account, and click Allow.- The script will execute, sending emails to all rows matching your filter conditions.
Step 6: Customize Filter Conditions
You can modify the filter logic to match any criteria. Common examples include:
- By region:
if (row[regionCol] !== ‘US’) continue;- By date:if (new Date(row[dateCol]) < new Date(‘2026-01-01’)) continue;- By multiple statuses:if (![‘Active’,‘Trial’].includes(row[statusCol])) continue;- By empty field:if (row[phoneCol] === ”) continue;
Step 7: Set Up Automated Triggers (Optional)
To run the mail merge on a schedule, add a time-driven trigger:
- In Apps Script, click the clock icon (Triggers) in the left sidebar.- Click Add Trigger.- Choose
sendMailMergeas the function, Time-driven as the source, and set your desired frequency (e.g., daily at 9 AM).- Click Save.This automates your mail merge so new rows matching the filter conditions receive emails without manual intervention.
Best Practices for Mail Merge Success
- Test first: Add your own email to the sheet and run a test before sending to your full list.- Respect sending limits: Gmail allows 500 emails/day (free) or 2,000/day (Workspace). Add
Utilities.sleep(1000)between sends to avoid rate limiting.- Track results: Use the EmailSent column to monitor delivery status and catch errors.- Use BCC for privacy: If sending to groups, ensure each email goes individually — the script above handles this by default.- Comply with regulations: Include an unsubscribe option and follow CAN-SPAM or GDPR guidelines.
Troubleshooting Common Issues
| Problem | Solution |
|---|---|
| Authorization error on run | Re-authorize by going to Extensions > Apps Script > Run and accepting permissions again |
| Emails land in spam | Avoid spammy subject lines, add SPF/DKIM records, and keep volume under daily limits |
| Script times out | Google Apps Script has a 6-minute execution limit. Process rows in batches of 100 and use triggers for continuation |
| Duplicate emails sent | Ensure the EmailSent column check is working and use SpreadsheetApp.flush() after each update |
Can I include attachments in the mail merge emails?
Yes. Use the attachments option in GmailApp.sendEmail(). For example: GmailApp.sendEmail(email, subject, body, { attachments: [DriveApp.getFileById(‘FILE_ID’).getBlob()] }). You can even pull different attachment file IDs from a column in your spreadsheet to personalize attachments per recipient.
How do I send more than 500 emails per day with a free Gmail account?
Free Gmail accounts are limited to 500 outgoing emails per day. To exceed this, upgrade to Google Workspace, which allows up to 2,000 emails per day. Alternatively, split your recipient list across multiple days by using a date-based filter condition in your script or by processing a fixed batch size per run.
Is it possible to apply multiple filter conditions at the same time?
Absolutely. You can chain as many filter conditions as needed in the if statement. For example, to send only to active users in the US on a Premium plan, use: if (row[statusCol] !== ‘Active’ || row[regionCol] !== ‘US’ || row[planCol] !== ‘Premium’) continue;. You can also use arrays with .includes() for matching against multiple acceptable values in a single field.