In this article:

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

May 14, 2024

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 customer outreach, 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.

1. Create a free Lido account

Since Google Sheets doesn't have any native email functionality and we don't want to deal with Google Apps script, we can use Lido to mail merge and send emails with your Google Sheets data. Create a free account here.

2. Prepare your Google Sheets data

In order to make things work smoothly, clean up your Google Sheet so that it meets these criteria:

  1. Column headers (like first name, last name, and email) are in the first row
  2. No empty rows or spaces between the data that you want to mail merge
Example of a properly formatted Google Sheet for mail merge.

3. Connect your Google Sheet to Lido

Create a new Lido file. Then, click the Connect Data button in the top left.

Select Google Sheets, then paste in the URL of your Google Sheet from step 2. You will be prompted to log into a Google account with access to the Google Sheet the first time that you connect a Google Sheet to Lido.

Next, select the columns that you want to connect. Make sure you include every column that you want to include in the message of your mail merge email.

Click Add Data. This will create a table in Lido. Your Google Sheets data will stay connected and refresh automatically.

4. Create email subject and body

Next, we will draft our email subject and body. To save time, we'll use Lido's computed columns to apply the same template to every row in the table that we want to send an email to.

Hover over the table, then click the menu and select Add Computed Column. This will create an empty blue column.

Inside of the new computed column, we can use the [@Column Name] syntax to reference values from different columns directly in our email. For example:

Remember to put your text in quotes (" "). Notice how First name from each row has been substituted into where [@First name] is in the formula.

We'll use this method to make one column for email subject, and another for email body.  You can reference as many different columns as you want inside of the email template.

Your file at this stage should look like this:

5. Add the SENDGMAIL() formula

Next, we will use Lido's SENDGMAIL() formula to send out our emails to every row.

First, create another computed column.

Inside of the new computed column, type =SENDGMAIL

If it's your first time using this formula in Lido, you will need to click + Add Credential to connect your Gmail account.

After you have successfully added your credential, we can continue with the rest of the formula.

The formula for SENDGMAIL is:

```=SENDGMAIL(<sender-credential>, "", "Subject", "Body")```

In this example, we want to pull our email recipient, subject, and body all from our spreadsheet contents. So we can reference them with spreadsheet cell references. For example:

In this image, in row 2 my recipient is in cell C2, my email subject is in cell F2 and the email body is in cell G2.

Since this formula is inside of a computed column, the references will dynamically populate for every row in my table.

6. Click "Run column now" to send emails

Now all that's left to do is send our emails!

In the column with your SENDGMAIL formulas, click on the 3 dot menu at the top. Then select Run column now.

Note that this will actually send all of the emails in the column when you click it! If you're not ready to send all your emails, you can send yourself a test email by right clicking any SENDGMAIL formula cell and either previewing the email or running the single action.

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. Edit your SENDGMAIL formula

Lido's SENDGMAIL formula is very robust.  All of the fields in [] are optional, and you can skip to the one you want in the formula by adding a comma (,). The formula supports cc, bcc, attachments, different sender emails, and reply to.

2. Tracking whether an email has been opened

You can create a tracking pixel for each email with the pixel_id_cell argument in the SENDGMAIL formula. Simply choose a different cell in your spreadsheet where the tracking pixel will be outputted (e.g. H2). Then, when SENDGMAIL runs, cell H2 will automatically be populated with a tracking pixel ID.

You can then used the Lido formula =PIXELOPENED() to check the pixel to see if it has been opened. Just wrap the formula around your pixel id cell, e.g. =PIXELOPENED(H2)

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

Automatically Send Email From Google Sheets

Google Sheets Mail Merge Labels

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->