How to use Mail Merge in Google Sheets [User Guide]

how to use Mail Merge in Google Sheets 22
Key Takeaways: How to use Mail Merge in Google Sheets

To use Mail Merge in Google Sheets

  1. Prepare Data.
  2. Add Apps Script Code.
  3. Set Up Email/Letter.
  4. Template.
  5. Save Email Template.
  6. Create a Draft.
  7. Send Mail Merge.
  8. Check Sent Emails.
  9. Verify Received Emails.

In this article we will learn about how to use mail merge in Google Sheets with example.

Mail marge? The name is suggesting something related to mail? Yes, mail merge is an idea that was brought to the table to manage automatic customize email and letters to each end-user. Mail merge is used by small businesses to automate their emails and customize them for each end-user. Let’s understand with an example, you run an online store and you sell beauty products. You have customers spread worldwide (wow, great), you are launching a discount offer and want to inform your users about it and you want to offer different discounts to them according to their previous purchasing history, in this case, you will have a letter, a small letter that tells a user about the discount offer is coming.

In each letter or mail, you will have to mention the customer name, their discount percentage, and other details that are unique for each user, you will do it manually if you have 10-20 or even 40 customers, but what if you have 1200 customers? What will you do? Will you consume your 3-7 days or hire a VA for this task? Not at all. We have mail merge we can do it for free in a few minutes. In this article, we will learn this entire system, and by the end, you will be able to send an automatic customized email. Let’s move on.

Use cases of Mail Merge in Google Sheet

As I described in the above section, mail merge is an excellent way to send automatic customized emails, using an email template. A template is an overall letter that has some message and some placeholders that holds the unique content, these placeholders may contain customer name, their location/address, their discount amount, etc. Using their names and their unique details make the email or letter very personalized and real for the reader. If I send a normal email/letter to 1000 customers, they will not feel special but if using mail merge I send 1000 emails to 1000 users in which each email/letter has the customer name, and their unique details, then they will definitely feel special and here I will be able to catch their attention.

Attention is the currency of the internet, so this personalized email/letter will ultimately improve the business. Using mail merge we can make great email/letters and can send them to all customers in one click. Really in one click? Yes, after setting up the things, it’s just a one-click distance to your customers. This is why we must learn how to use mail merge in google sheets.

How to use Mail Merge in Google Sheets

So, here we will see what steps we need to learn how to use mail merge in google sheets.

Firstly, we need the data of our customers (or any leads to whom we want to send the email/letter). We need to have their first name and email (mandatory).

This data for each customer is to be plotted on a google sheets file.

A Gmail/email account.

Then we need an email/letter template, you can write your template or you can use the one that will be provided in this article.

Then we need the app script code which will run the entire mail merge system.

Don’t worry you don’t need to write the code yourself. A great code is already written by a person, so you can use that for your mail merge. I will provide the code in this article.

Having the above, you’re ready to get into the mail marge to send your first automated customized email.

 

But, here is a question that may come to your mind, is not there any other method to do this?

We have more methods for using the mail merge technique within our google sheets file. We can use add-ons, there are many add-ons available that give you the functionality of the mail marge technique, we will see add-ons letter in this article.

The other way is using a google chrome extension. we have various extensions available that work like mail marge and they can help you do the same task as mail marge apps script code does.

Ok, so there is no native method inside google sheets to use mail merge. The above three methods are mainly used in which the app script code is the one that is highly reliable and faster.

Let’s move forward to learn how it’s done.

How to use Mail Merge in Google Sheets –  Using Apps Script

Here we will learn how to use mail merge in google sheets using the apps script. Firstly you have all the things I have described above. Let’s see what the google sheets file with customers’ or lead data look like, then we will move forward to further steps.

Step 1

For the pre-requisite data, you can copy this data. (you can also customize the data if you want)

how to use Mail Merge in Google Sheets 1

Step 2

Adding apps script code to your google sheets file

2.1 Go to Extension > Apps Script

how to use Mail Merge in Google Sheets 2

2.2 Remove all the code and paste the following code (change the important variables such as sheet name, file name, etc)

how to use Mail Merge in Google Sheets 3

2.3 Save the script
how to use Mail Merge in Google Sheets 4

2.4 Run the code

how to use Mail Merge in Google Sheets 5

2.5 A Mail Merge button will be added to your main menu (we will use it directly from the main menu)

how to use Mail Merge in Google Sheets 6

Step 3

Set up your email/letter template

3.1 Write a message to send to your customers.

how to use Mail Merge in Google Sheets 7

3.2 use {{}} double curly brackets for placeholders.

how to use Mail Merge in Google Sheets 8

how to use Mail Merge in Google Sheets 9

how to use Mail Merge in Google Sheets 10

how to use Mail Merge in Google Sheets 11

3.3 Add placeholder values as the “column headers” where you want to fetch the value from your google sheets file.

how to use Mail Merge in Google Sheets 12

how to use Mail Merge in Google Sheets 13

3.4 Use exact column header names inside the placeholder. (The spelling should be accurate, this is case sensitive as well)

Below is the sample email/letter template used in this article, you can copy the template.

Subject: Good News

Dear {{Name}}

We are glad to inform you that you have won the endorsement from {{About us}}. We are inviting you to buy any product from our website with a flat {{Discount}}% OFF, and a 100% replacement guarantee.

We hope you will enjoy this reward and buy our products.

Thank you

Team {{About us}}.

Step 4

Keep this email template in your Email > Compose. (Add a subject line in the subject section)

how to use Mail Merge in Google Sheets 14

Step 5

Keep this in the draft (don’t add any recipients), and copy the subject text from it

how to use Mail Merge in Google Sheets 15

how to use Mail Merge in Google Sheets 16

Step 6

Back to your google sheets file click on Mail Merge > Send Emails

how to use Mail Merge in Google Sheets 17

Step 7

It will ask for the subject line of the email. Paste the subject line (so the system can identify which email you want to send)

how to use Mail Merge in Google Sheets 18

how to use Mail Merge in Google Sheets 19

Step 8

Click Ok, and you will see a running script pop-up.

how to use Mail Merge in Google Sheets 20

how to use Mail Merge in Google Sheets 21

Step 9

Once the email is sent, you will have the sent time in your “Email sent”, column.

how to use Mail Merge in Google Sheets 22

Step 10

Verify the email on the receiving email address.

how to use Mail Merge in Google Sheets 23

This is how you can send emails automatically and can customize them in every possible way.

how to use Mail Merge in Google Sheets 24

Credits for the code go to Martin Hawksey, Learning Design and Technology Lead at Edinburgh Futures Institute, blogger, and Google Developer Expert. We would like to thank him for developing this code and making it easy for us to use it.

Download/Copy Google Sheets Practice Workbook

Make a copy, the app script code will be embedded, data will be there, and the email template is provided above. Go try out and enjoy.

Notes

  • The code is embedded with your google sheets file (if you followed the above steps)
  • There are add-ons method as well in which we can achieve the functionality of mail merge, but we will see in some other article because it’s a very long topic
  • Similarly, we have an option to use a browser extension to achieve the functionality of mail merge, I will address this method also in the next article.
  • You need to keep the placeholders inside double curly braces {{}}
  • You must write the spellings accurately, also they are case sensitive
  • The email/letter template can be customized, to add any other variable let’s say the customer address, you can add a new column in your google sheets file and add the address list for all customers, and you can fetch using a placeholder in your email/letter template.
  • The code used in this tutorial is owned by Martin Hawksey, don’t forget to credit it if you use this code anywhere else.

FAQs

How to use mail merge in google sheets?

To use mail merge in google sheets, we need some data on the people to whom we want to send the email/letter. (Provided in this tutorial), then we need an app script that performs the entire functions in this system. (Provided in this tutorial), then we need an email/letter template (also provided in this tutorial). Having all these we can set up the template using placeholders, we add column headers names inside the placeholders from which the placeholders get each value each time. Similarly, it takes all the values and sends emails n times. n is the number of rows we have in our data excluding headers.

What are some other ways to send auto-customized emails like mail marge?

We have two more ways to use mail merge functionality in google sheets. We can use many available add-ons to get mail merged. Or, we can install a browser extension to get the functionality of mail merge. These two methods are not highly used. I will teach these two methods later in some articles.

How to customize the email template?

Simply you can add placeholders {{}}, and pass the column header name inside the placeholder. The plain text can be changed simply. You can write your message and add placeholders where you want a unique value for example username, userId, etc.

Conclusion

So that’s all from how to use Mail Merge in Google Sheets. I hope you guys enjoyed today’s article and you are soon going to take advantage of this excellent feature of Mail Merge. It gives you freedom and saves a lot of time and reduces the margin for errors. So, in the first section, we saw what is mail merge. Then we moved on and talked about how to mail merge helps us. then we saw some requirements to use mail merge, and then we saw a complete method using app script code, and I showed you an email sent to the appropriate email address lists. I showed you how to customize the email template. It’s pretty easy to add placeholders and add as many unique variables as you want.

That’s all from how to use Mail Merge in google sheets. I will see you soon with another great learning tutorial for you to enhance your skill set. Thank you so much for the read. Give it a like. Keep learning with Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com
Logo
Enable registration in settings - general