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.
First, we need to format our Google Sheet so that it works optimally. Please make sure your Google Sheet follows these guidelines:
Here is an example of a properly formatted Google Sheet:
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.
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:
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]"```
Repeat these steps for your email body. You should have two columns now, one for subject and one for body.
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")```
There are a few ways to accomplish this.
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".
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:
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.
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.
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.
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.
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:
Your file does not need to be open for the automation to run, automations will run continuously once they've been set up.
