Blog
>
Tutorials

Mail Merge in Google Sheets the Easy Way [2023 Update]

8 Minutes

Click here to Send an Email From a Lido Spreadsheet

Use Google Sheets together with your Gmail account to generate and distribute customized bulk messages quickly and easily. The method explained in this tutorial is ideal for sending personalized seasonal greetings to friends or employees, for making labels, or for issuing transaction documents to lists of clients/customers. The obvious advantage of this, is that your computer does the repetitive work, saving you time and frustration, and it's completely free for personal or small business use.

How To Mail Merge in Google Sheets

Mail Merge takes a draft email message, selects information from a spreadsheet of data, (each row containing details pertaining to an individual client/customer), and combines these inputs into individual emails. The spreadsheet template we will use in this tutorial contains a script that directs Google Sheets to execute this process, and dispatch the output automatically using Gmail.

1. Open Google's sample mail merge spreadsheet - Click this link

Make a copy of the Gmail/Sheets mail merge template from https://docs.google.com

2. Click on file -> "Make a Copy" and give your spreadsheet a name

3. Choose the Column Headers you want to include in your email

 Each column header is a field you will include in your email template. For example the email template we are going to send out will say “Hi {{First Name}} {{Last Name}}," and will auto fill all of the values under that column heading.

You can modify and add column headers but must always have "Recipient" and "Email Sent" headers as these are required by the script.

In our spreadsheet we are going to include the column headers: "First Name", "Last Name", "Recipient", “Status Level”, "Discount %", “Start Date” and “Email Sent”.

4. Create an Email in Gmail and save it as a draft

This spreadsheet will be sending all of your emails from your gmail account. Log into the gmail account you plan to send the email from and create a new email.

We will use this template:

It's important to note that the email subject is how the script will know to use the draft you created.

5. Connect your mail merge spreadsheet to your Gmail:

 Click on the "mail merge" button at the top of your spreadsheet and choose "Send Emails".

This will start the Mail Merge script running in Google Sheets

Click continue on the authorization notification:

The script cannot run in google Sheets without permission

Copy and paste the Subject Line from your draft email then click OK

The subject line text provides a link between the datasheet and the draft Gmail message to be merged

Click on the Gmail account you want to use:

You will get an error saying "This App isn't verified", however this is a spreadsheet provided by google and is confirmed to be safe. Click "Advanced" and click "Go to Gmail/Sheets Mail Merge unsafe”.

 

Google Sheets issues this warning because the Mail Merge app script has not yet been released

Click "Allow" when you get the notification asking if you want to connect your Gmail to your Google Sheets spreadsheet.

Google sheets requires permission to use the selected account

6. Click “Mail merge” and “Send emails”.

This will start the Mail Merge script running in Google Sheets

All of the recipients in your spreadsheet will now be emailed a custom email based on your template.

You can confirm the emails have been sent as there will now be a date in the "Email Sent" Column beside each recipient in your mail merge spreadsheet.

The script enters a date and time for each row of data that was successfully merged by Google Sheets

It's important to note that Gmail has limitations on the number of emails you can send per day. If you need to send large amounts of emails each day a paid solution may be a better choice

Advanced Capabilities of the Mail Merge Spreadsheet:

You can unlock advanced features including BCC and CC fields, a custom Sent From email address, Name of Sender, Reply To address and No Reply fields.

1. Click on Extensions Apps Script

2. Scroll down to the line which reads: “ // Uncomment advanced parameters as needed (see docs for limitations)”

Several features included are deactivated by “//”, which designates them as comments. 

3. To activate any one of these features remove the forward slashes and substitute a valid email address.

If you enjoyed this google sheets mail merge article, you may also enjoy some of our other articles:

Send Email From Google Sheets

Google Sheets Mail Merge Labels

Tired of constantly monitoring your spreadsheets? Scale operations with Lido automations and free up time to focus on what really matters.