Mail Merge Using Google Sheets and Gmail (with Apps Script)

If you’ve ever needed to send personalized emails to a list of contacts, but didn’t want to manually type each one, mail merge is the solution. Google Sheets, combined with Gmail and Apps Script, allows you to create a simple and efficient mail merge system that saves time and effort. With just a few lines of code, you can send personalized emails to hundreds (or even thousands) of recipients, making this feature invaluable for business owners, marketers, event planners, and anyone needing to send customized emails quickly.

In this article, we’ll explain what mail merge is, why it’s useful, and how to set it up using Google Sheets and Gmail with Apps Script. We’ll walk through the process step-by-step, provide real-life examples, and help you get started with your very own automated email campaign.

What Is Mail Merge and Why Should You Use It?

Mail merge is a process that allows you to send personalized emails to multiple recipients without having to manually customize each one. Using Google Sheets and Gmail, you can automate this process by merging data (like names, email addresses, and other personalized information) from your spreadsheet into a prewritten email template. This means you can easily create and send hundreds or thousands of personalized emails in just a few clicks.

Why Mail Merge is Useful:

• Save time: No more typing the same email over and over again for different people.
• Personalized outreach: Send tailored emails that feel more personal, which can improve engagement.
• Efficient for large groups: Perfect for marketing campaigns, event invitations, or sending updates to a large list.
• Easy to manage: Google Sheets keeps all your contacts and data organized in one place.

How to Set Up Mail Merge with Google Sheets and Gmail

To set up mail merge, you’ll need three key components:

1. Google Sheets: This is where you’ll store your contact information and personalized data.
2. Gmail: Gmail will be used to send your emails.
3. Google Apps Script: Apps Script allows you to automate the process of sending emails by connecting Google Sheets to Gmail.

Step 1: Prepare Your Google Sheets

Start by creating a Google Sheets document to store your contacts. This sheet will include columns like names, email addresses, and any personalized data you want to merge into the email (e.g., customer preferences, membership details, etc.). Here’s an example of what your sheet might look like:

Sample Contact Data

Name	Email Address	Subscription Type
Alice Johnson	alice@example.com	Premium
Bob Smith	bob@example.com	Basic
Carla Davis	carla@example.com	Premium

Step 2: Create Your Email Template

Next, write the email you want to send in Gmail. You can include placeholders for the data from your Google Sheets, which will be replaced by the actual data during the mail merge process. For example:

Subject: Special Offer for Premium Members!

Email Body:

Hello {{Name}},

 
We’re excited to offer you a special deal as a {{Subscription Type}} member. Enjoy an exclusive 20% off on your next purchase!

 
Best regards,
Your Company

In this email, `{{Name}}` and `{{Subscription Type}}` are placeholders that will be replaced with the corresponding values from your Google Sheets.

Step 3: Set Up Google Apps Script

Now, you’ll need to write a script to automate the process of merging data and sending emails. To do this:

1. Open your Google Sheets document.
2. Click on Extensions > Apps Script to open the script editor.
3. Delete any default code in the script editor and replace it with the following code:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); // Change "Sheet1" to the name of your sheet
  var startRow = 2; // Start at row 2 (to skip the header row)
  var numRows = sheet.getLastRow() - 1; // Get the number of rows of data
  var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn()); // Get the data range
  var data = dataRange.getValues(); // Fetch the data
  
  var subject = "Special Offer for Premium Members!"; // Email subject
  
  // Loop through each row of data
  for (var i = 0; i < data.length; i++) {
    var row = data[i]; 
    var emailAddress = row[1]; // Second column for email address
    var name = row[0]; // First column for name
    var subscriptionType = row[2]; // Third column for subscription type
    
    // Compose the email body, replacing placeholders with actual values
    var message = "Hello " + name + ",\n\n" +
                  "We’re excited to offer you a special deal as a " + subscriptionType + " member. Enjoy an exclusive 20% off on your next purchase!\n\n" +
                  "Best regards,\nYour Company";
    
    // Send the email
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

This script fetches the data from your Google Sheets and sends personalized emails using Gmail. The script replaces the placeholders with the actual names and subscription types from your sheet.

Step 4: Run the Script

To run the script, click on the play button in the Apps Script editor. The first time you run it, Google will ask for permission to access your Sheets and Gmail account. Grant the necessary permissions to allow the script to work. Once the script is authorized, it will automatically send the personalized emails to all recipients in your list.

Real-Life Example: Event Invitation Mail Merge

Imagine you’re hosting an event and need to send invitations to a large list of people. By using mail merge, you can personalize each invitation with the recipient’s name, event details, and registration status. Here’s how your invitation might look after merging data:

Subject: Invitation to the Annual Charity Gala

Email Body:

Hello Alice,

 
We are pleased to invite you to the Annual Charity Gala!

 
Date: May 10, 2025
Location: Grand Ballroom, Downtown

 
Please RSVP by May 1st. We look forward to seeing you there!

 
Best regards,
Event Coordinator

With just a few clicks, this email is automatically personalized and sent to all attendees, saving you hours of manual work.

Benefits of Using Mail Merge with Google Sheets and Gmail

• Personalized Emails: Tailor each email to the recipient using data from Google Sheets.
• Automated Process: Save time by automating the email sending process.
• Large Scale Outreach: Send personalized emails to hundreds or thousands of recipients with ease.
• Cost-Effective: No need for expensive email marketing software—Google Sheets and Gmail are free to use.

Quick Reference Cheat Sheet for Mail Merge

• Set up data: Use Google Sheets to store recipient information (e.g., names, emails, subscription types).
• Write email template: Use placeholders like `{{Name}}` to personalize the email.
• Write Apps Script: Automate sending emails by using the MailApp service in Google Apps Script.
• Run the script: Click the play button to execute the script and send emails.

Mail merge with Google Sheets and Gmail is an easy and efficient way to send personalized emails to a large group of people. Whether you’re sending event invitations, marketing campaigns, or personalized updates, this method saves time and helps ensure that every recipient gets a message tailored to them. By following the steps in this guide, you’ll be able to set up your own mail merge system and start sending customized emails in no time!