How to Mail Merge in Google Sheets (With Examples)

Sending out multiple emails with personalized content doesn’t have to be a time-consuming task. With Google Sheets, you can efficiently execute a mail merge without needing specialized software. 

In this article, we’ll guide you through the process of performing a mail merge in Google Sheets step by step.

For starters, mail merge is a technique that’s been in use since as early as 1980. It allows you to create customized documents or emails in bulk. It’s a powerful tool found in many word processing and spreadsheet software packages, simplifying the process of sending personalized content to multiple recipients.

While popular office suites like Microsoft Office include a built-in mail merge feature, Google Sheets does not offer this option by default. However, there are workarounds available to accomplish mail merge tasks effortlessly.

In this tutorial, we’ll explore two methods for achieving mail merge in Google Sheets:

• Using an App Script: App scripts allow you to add custom features to Sheets. For mail merge, a script can pull data from your sheet into an email template. 

• Using an Add-on: Add-ons are mini apps that add functionality to Sheets. Using add-ons, anyone can execute a mail merge in Google Sheets effortlessly. 

But before we delve into the practical aspect of how to mail merge in Google Sheets, let’s understand what mail merge is and how it works. 

What is Mail Merge?

Mail merge is a super useful tool for making lots of personalized letters or any type of document simultaneously. These documents are designed to be sent to many people at the same time.

Think of it as the secret sauce behind many business activities, especially when it comes to things like sending out marketing emails, creating ads, or dealing with important business letters.

It’s like having a magic wand when you need to send out the same basic email or letter to a bunch of people but with a special touch just for each of them.

And here’s the cool part: mail merge isn’t just for emails or letters. You can use it to do other things, too, like making a whole bunch of labels all at once or creating a stack of invoices without typing each one by hand. It’s a handy trick to save time and make your life easier.

Understanding How Mail Merge Works

Mail merge is a way to create many personalized emails or documents from one template. Here’s how it works in simple steps:

1. You create a spreadsheet with a list of recipients’ names and other details like email addresses or interests. This is called your recipient list.

2. You make a template email or document with placeholder text like “Dear [Recipient Name].” The placeholders will be filled in later.

3. You select both the recipient list and template in a mail merge tool. The tool will match up columns from your spreadsheet to the placeholders in your template.

4. The mail merge happens. The tool automatically fills in the template placeholders with data from your recipient list. So “Dear [Recipient Name]” becomes “Dear John” for the row with John’s name.

5. This creates customized versions of your template, one for each row in your spreadsheet. Now, you can send personalized emails or documents in just a few clicks. 

Copy Sample Sheet

If you’re pumped up and want to follow along with today’s tutorial, you can copy our sample sheet via the link below:

Click Here to Copy This Sheet

And if you have your own spreadsheet, you can simply replicate the steps in today’s guide to achieve the same result.

Using Google Apps Script for Mail Merge in Google Sheets: Step-by-Step Guide

To perform a mail merge in Google Sheets, you can use Google Apps Script. It’s a handy way to personalize emails for multiple recipients. Here’s what you’ll need to get started:

• A Gmail Account: You’ll need a Gmail account because this is where your emails will be sent from. If you don’t have one, you can easily create it for free.

• A Google Sheets Spreadsheet: Your spreadsheet will contain all the data you want to customize for each email recipient. This could include their names, the competition they won, and any other personalized information you want to include.

Now, the good news is that you don’t have to be a coding wizard to make this work. You don’t need to write the script yourself. It’s available online for you to simply copy and use, which makes the process much more accessible.

Let’s say you want to send congratulatory emails to the competition winners. For this demonstration, we’ll use the following sample data to represent each recipient:

Step 1: Set Up Your Gmail Account

To start the mail merge process, you’ll need a Gmail account. If you already have one, great. If not, you can easily create one for free. This Gmail account will be used to send out personalized emails to your recipients.

Step 2: Prepare Your Google Sheets Spreadsheet

Create a Google Sheets spreadsheet that contains all the data you want to customize for each email recipient. This could include details like their names, the specific competition they won, and any other personalized information you want to include. 

For this example, we will use the sample data we showed you earlier. 

Step 3: Compose Your Email Template in Gmail

Before you start the mail merge, go to your Gmail account and compose the email template you want to send to your recipients. You can include placeholders like “[Recipient’s Name]” or “[Prize]” in your template to be filled with personalized information from your Google Sheets spreadsheet.

Here is the email template we will use for this example:

Step 4: Access Google Apps Script

Having composed the email template like we showed you in the previous step, it’s time to launch the Google Sheets Apps Script. To do that, navigate to the Extensions menu and select the option for App Script. 

This action should instantly launch the Google Sheets Apps Script editor. 

Step 5: Copy and Paste the Mail Merge Script

As we reiterated earlier, you don’t need to be a coding expert for this. And the good news is, we have put together the script we will use to mail merge. All you need to do is copy the script below and paste it into the code editor window for Apps Script. 

This script will automate the process of sending personalized emails to your recipients. 

function sendMailMerge() {

// Access the active Google Sheet

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName('Sheet1'); // Replace 'Sheet1' with your sheet's name

// Access the Gmail draft

var draft = GmailApp.getDrafts()[0]; // Replace [0] with the draft's index

// Get the email template content

var emailTemplate = draft.getMessage().getBody();

// Get the data from the spreadsheet

var data = sheet.getDataRange().getValues();

// Loop through each row in the spreadsheet, starting from the second row (assuming the first row contains headers)

for (var i = 1; i < data.length; i++) {

var recipientName = data[i][0];

var recipientEmail = data[i][1]; // Email addresses are in the second column

var competition = data[i][2];

var prize = data[i][3];

// Check if the recipient's email address is not empty and is valid

if (recipientEmail && recipientEmail.indexOf('@') > 0) {

// Replace placeholders in the email template with actual data

var personalizedEmail = emailTemplate.replace('[Recipient\'s Name]', recipientName)

.replace('[Competition]', competition)

.replace('[Prize]', prize);

// Send the personalized email

GmailApp.sendEmail(recipientEmail, 'Congratulations on Your Victory!', '', {

htmlBody: personalizedEmail

});

// Pause briefly to avoid Gmail rate limits (e.g., 1 email per second)

Utilities.sleep(1000);

}

}

}

After copying and pasting the script, as we showed you in the previous step, all you need to do is save the script. To do this, simply follow these quick steps:

• Click the floppy disk icon or press Ctrl + S (Windows/ChromeOS) or Command + S (Mac) in the Google Apps Script editor.
• Give your project a name if prompted.
• Your script is now saved and ready for use.

With the script saved, you’re just one step away from personalizing and sending emails to your recipients. The next step is to execute the mail merge, which will send out the emails using the data from your Google Sheets and the email template you’ve prepared in Gmail.

Step 6: Test the Script

Before sending out actual emails, it’s a good practice to test the script. Run a test with a few recipients to ensure everything works as expected. This way, you can catch any issues or errors before sending out a larger batch of emails.

Step 7: Execute the Mail Merge

Once you’re satisfied with the test results, you can execute the mail merge. To do that, simply navigate to the toolbar section of your Apps Script window and click on the option for Run. 

The script will automatically send personalized emails to each recipient in your Google Sheets spreadsheet using the template you composed in Gmail. 

Each email will be tailored to the recipient with their specific information.

Step 8: Check Your Email

It is not enough to execute the script. You also need to confirm that it actually worked. And the best way to know if the mail merge worked is to check your Gmail. 

If everything worked as it should, your email should show a series of messages sent out after executing the script for mail merge. 

Here is a glimpse of what my email looks like after executing the previous step. 

By following these steps, you’ll be able to personalize and send emails to a list of recipients, just like sending out congratulatory messages to competition winners. 

Using Add-ons to Mail Merge in Google Sheets

In the previous section, we showed you how to use Apps Script to mail merge in Google Sheets. 

While we took our time to show you the step-by-step process, it can be somewhat challenging to execute, especially if you’re just getting started with Google Sheets. 

That’s why we have created another exciting and easier way to mail merge in Google Sheets. Instead of using Apps Script like we showed you in the previous step, we will use add-ons, which make the entire mail merging process easy. 

If you’re new to Google Sheets and just hearing about add-ons for the first time, you’ll be thrilled to know that they are helpful tools that you can add to Google Sheets to simplify the mail merge process. 

What’s even more amazing about this option is that there are several add-ons you can use to mail merge in Google Sheets.

Now, let’s show you how to mail merge in Google Sheets using add-ons. 

Step 1: Extension > Add-ons> Get Add-ons

To start mail merging in Google Sheets using add-ons, you must first find the right tool for the job. Let’s walk you through the process. Open your Google Sheet and look for the “Extensions” menu at the top of your spreadsheet.

When you click on “Extensions,” a menu will appear. Scroll down until you see “Add-ons.” Click on “Add-ons,” and you’ll see two choices. From these options, select “Get Add-ons.”

After executing the last step, as we showed you, Google Sheets will instantly launch the Google Sheets Workspace Marketplace. It should look something like this: 

Step 2: Search for Mail Merge Add-ons

Once the Google Sheets Marketspace launches, as shown in the image above, all you need to do is click on the search bar, type in “Mail Merge,” and then hit enter. This action will provide you with different mail merge add-ons you can use. 

Step 3: Choose A Mail Merge Add-on

When you search for “mail merge” in the Google Sheets workspace marketplace, you’ll find various options to pick from. But for our example, we’ll go with the first one called “Yet Another Mail Merge.”

This means we’ll use “Yet Another Mail Merge” to perform our mail merge. It’s a straightforward choice to help us get started with the process.

Once you’ve chosen the “Yet Another Mail Merge” add-on, you’ll be taken to a new page where you’ll need to install the add-on. It’s a simple process. Just click on the “install” button and then wait for the installation to finish. 

Step 4: Launch Yet Another Mail Merge

Once the installation is complete, it’s time to launch the add-on. What you want to do is navigate to the Extension menu, scroll all the way down, and select the option for Yet Another Mail Merge. A sub-menu will launch, providing you with more options to opt for. From those options, choose Start Mail Merge. 

When you open the add-on, it’s quite simple. Here’s what you need to do:

• First, type in the name you want to appear as the sender of your emails.
• Next, pick the email draft you prepared earlier. Remember, we set this up in the previous method using Apps Script for mail merge in Google Sheets.

After you’ve adjusted these settings to your liking, there’s just one more step:

• Click on the “send” option. Your emails will be sent instantly to all the email addresses in your spreadsheet.

Step 5: Check Sent Emails

Now that you’ve used the “Yet Another Mail Merge” add-on to send your emails, it’s time to ensure they were sent successfully.

Here’s how to check:

• Go to your email account.
• Look for the “sent” folder or section.
• If everything went well, you should see that the emails were indeed sent.

Here is what ours looks like: 

From the screenshot above, you can see that it actually worked. 

Final Thoughts

In this guide, we’ve explored the fascinating world of mail merging in Google Sheets. Whether you’re new to this or looking for simpler alternatives, we’ve got you covered. Let’s wrap up with some key takeaways:

Mail merge might sound complex, but it doesn’t have to be. With the right tools and a few simple steps, you can personalize and send emails or documents to multiple recipients effortlessly.

In today’s tutorial, we showed you different ways to mail merge in Google Sheets. We kicked things off by showing you how to use Apps Script to mail merge in Google Sheets. 

While it may be a little challenging, especially if you’re just getting started, by following the steps we outlined, you should be able to mail merge effortlessly using Apps Script. 

And If scripting isn’t your thing, mail merge add-ons offer a user-friendly solution. There are various add-ons available, each with its own features. 

We discussed how to find, install, and use one such add-on, “Yet Another Mail Merge.” It simplifies the process with a few clicks.

Note: After sending your emails, always check your “sent” folder in your email account to ensure they were successfully sent. This step provides peace of mind and confirms that your recipients have received your personalized messages.

In a nutshell, mail merging can be used for various purposes, from congratulating competition winners to email marketing. Don’t hesitate to experiment and discover how it can streamline your communication and make it more personal.