In this article:

How to send emails to different recipients based on a column in Google Sheets

In this article, we'll cover how to automatically send emails to different people based on a column in Google Sheets. Read on to see how we can accomplish this in a few minutes, without writing any custom code.

Prepare your Google Sheet

First, we need to format our Google Sheet so that it works optimally. Please make sure your Google Sheet follows these guidelines:

  1. The first row in the sheet should be column headers
  2. There should be no special characters (like brackets [] or quotes " ) in the column headers
  3. Remove all empty rows and columns, including hidden or grouped rows and columns

Here is an example of a properly formatted Google Sheet:

Connect the Google Sheet to Lido

Since there is no way to send emails directly from a Google Sheet, we'll need to connect the data in the Google Sheet to Lido.

If you don't already have a Lido account, you can create one here: https://www.lido.app/go/signup. There is a free plan that will let you send up to 20 emails / month, and then paid plans starting at $19/month after that.

Create a new file. Then, click Connect Data and select Google Sheets as the datasource.

Next, paste in the URL from the Google Sheet that we connected to the Google Form in the first step.

On the next screen, select which columns you want to connect to Lido. At a minimum, you'll need to select any columns that you want to reference as variables in the automated email, as well as the column that you want to use as the conditional trigger.

Then click Add Data. After your data is successfully connected to Lido, it will look like this in your Lido spreadsheet.

Note: it's recommended that you have at least one form response recorded so that things work in Lido. If this is a new form, then we'd recommend entering in a dummy form response that you can delete later.

Create Email Subject and Body Columns

Next, we need to add the email templates for subject and body. We can make the content of each email dynamic by referencing data from each Google Sheet as a variable.

First, add a computed column to your spreadsheet table. This is a column type that will repeat the same formula down every row in the table, which is what we want an email template to do. Here is how to add a computed column:

How to add a computed column.

We will write the email templates inside of the first row of the computed column. To make the contents of the email dynamic for every row, we can use the [@Column Name] syntax. Make sure you wrap everything in quotes ( " ).

For example:

```="Hello [@Name]"```

Example computed column with dynamic email subject utilizing [@Column Name] syntax.

Repeat these steps for your email body. You should have two columns now, one for subject and one for body.

Calculate the email recipient based on a Google Sheets column

Now, we need to add the logic for who an email should be sent to based on a the value in a Google Sheets column.

Add a new computed column and name it Email recipient.

We will use a simple example here and assume that you have two different potential recipients (john@example.com and joe@example.com). In the computed column, use the formula:

```=IF(A2="John", "john@example.com", "joe@example.com")```

This formula does the following:

If the value in cell A2 is "John", then the recipient email will be "john@example.com". Otherwise, it should be "joe@example.com".

If the column you want to trigger different recipients off of is different than column A, then make sure you change this in the formula. For example, if you want to use column C to determine the email recipient, then the formula in the second row would be

```=IF(C2="John", "john@example.com", "joe@example.com")```

What to do if you have more than two recipient options

There are a few ways to accomplish this.

Option 1: Nest IF statements

For example:

```=IF(A2="John", "john@example.com", IF(A2="Joe", "joe@example.com", "jacob@example.com"))```

This formula adds a third potential person. It says if the value in cell A2 is "John", then the recipient email will be "john@example.com". Otherwise, if the value in cell A2 is "Joe", then it should be "joe@example.com". Otherwise, if it's neither of those two options, return "jacob@example.com".

Option 2: Set up a matrix and use XLOOKUP

As you can imagine, this has the potential to get unwieldy if you have a lot of potential recipients. If you want to keep things more neatly organized, you can set up an email recipient matrix in a separate tab, like this:

Example of an email recipient matrix.

Then, you can use an XLOOKUP() formula in your computed column to find the correct recipient.

For example:

```=XLOOKUP(A2, 'Sheet1'!$A$2:$A$4, 'Sheet1'!$B$2:$B$4)```

When placed inside of a computed column, this formula will lookup the value in A2 in the first column of your email recipient matrix (located in Sheet1'!$A$2:$A$4 in this example) and return the email address located in the Email recipient column (Sheet1'!$B$2:$B$4 in this example).

Remember to change out the sheet references and column variables to match your actual data.

How to send emails to multiple recipients

You can send emails to multiple recipients by adding them in a comma separated list. For example:

```=IF(A2="John", "john@example.com", "joe@example.com, jacob@example.com")```

In this example, if A2 is not John, then emails will be sent to both Joe and Jacob.

Add the SENDGMAIL formula

Now we are ready to add the formula to send out emails. Lido has a proprietary action formula called SENDGMAIL() which takes in data from the spreadsheet and sends an email when triggered.

Note: if you use Outlook instead of email, you can substitute =SENDOUTLOOK() everywhere you see =SENDGMAIL() and it will work exactly the same.

The syntax for SENDGMAIL is:

=SENDGMAIL(<sender-credential>, recipient, subject, body, status)

Here's what each of these components means:

sender-credential: this is the account that emails will be sent from. You'll need to authenticate into a Gmail or Google Apps account the first time you set this formula up.

recipient: the email address that the email is being sent to

subject: the email subject

body: the email body

status: the location of a spreadsheet cell that will get the output of whether the email was sent (either "success" or an error message)

In our example, the formula in row 2 is:

```=SENDGMAIL(<sender-credential>, I2, G2, H2, J2)```

Importantly, the recipient input (I2 in the example) needs to reference the location of the Email recipient column that we added in the previous step.  This is how the emails are sent to different people based on different Google Sheet column values.

SENDGMAIL formula with dynamic recipient referencing the Email recipient column created in the previous step.

Next, add a Linked Column to hold the returned statuses of our SENDGMAIL actions. Make sure that the location of the Linked Column lines up with the status cell location in your SENDGMAIL formula (column K in this example).

Now we are ready to test our column. To send out a test email you can click the 3 dot menu in a SENDGMAIL cell and click Run action. This will actually send out an email.

After an email has been successfully sent, you'll see "success" displayed in the status linked column in column K.

Since we only want our emails to go out once, we need to wrap this entire thing in an IF statement that checks whether an email has already been sent.

```=IF(K2<>"success", SENDGMAIL(<sender-credential>, I2, G2, H2, J2) )```

This formula says check the status column to see if there is a success message. If there is not, then display the SENDGMAIL formula. If there is, then display FALSE.

As you can see, after an email has been sent for the row, there is no longer a SENDGMAIL formula in that row.

Create an automation

Finally, we are ready to create an automation. Lido automations will fetch the latest data then run all of the actions displayed in the automated column. This is why it was important to add the IF statement in the previous step, otherwise you'd get a new email every time the automation runs regardless of whether one has already been sent.

In the column that contains your SENDGMAIL formulas, go to the header cell and click on the 3 dot menu. Click on Run Column on a Schedule.

This will open the automation panel. Here, you can select a frequency for how often to check for updates. Then click save.

Every time the automation interval passes, the following will happen:

  1. Lido will pull in the latest data from Google Sheets
  2. All of the rows with a SENDGMAIL() formula in the automated column will have the formula triggered

Your file does not need to be open for the automation to run, automations will run continuously once they've been set up.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started