In this tutorial, we will walk through how to send emails to different recipients based on Google Forms responses. At the end of the tutorial, we will show you how to adapt this for sending different email content based on a form response.
Follow these steps to use Google Forms’ built in feature:
Copy the URL of the newly created Google Sheet.
Since you cannot send emails directly from Google Sheets, we will use a free software called Lido to do this part. You can create a free account at: www.lido.app/go/signup.
Then follow these steps:
Click Add Data. This will create a Lido table of your Google Form responses.
Go to a fresh worksheet. We will write our email templates in this new sheet. You can use table columns as email variables like so: [@Column].
Navigate back to your table. Then, create a computed column. This is a column type in Lido that applies the same spreadsheet formula to every row in a table. Type a column name in the first row of a cell next to your table (cell F1 in this example) to create a new column, then select Computed Column as the type.
Inside of the computed column we will use the STRINGTEMPLATE formula to replace the variable placeholders from the prior step with the actual content of each row. The formula for STRINGTEMPLATE is:
=STRINGTEMPLATE(“template_cell_location”)
In our example, our template cell for email subject is located in Sheet1!$B$1. So our formula for cell F2 of the computed column is:
```=STRINGTEMPLATE(Sheet1!$B$1)```
When submitted, our table now looks like this. Notice the dynamic replacement of the [@Name] variable in every row.
Repeat this process to create a dynamic email body column. First, create a new computed column. Then enter in the formula:
```=STRINGTEMPLATE(Sheet1!$B$2)```
Remember to replace the template_cell in this formula with the location of your actual template cell, and also to anchor the reference with $ so it pulls in the same template for each row in the table.
You should now have a table that looks like this:
In this step, we will add the logic to choose an email recipient based on the form response.
First, make another computed column for “Email recipient.” We will use an IF statement inside of this new computed column to determine which email address a new response should be sent to, depending on the content of the response.
In this simple example, our company sells two products: apples and oranges. If a new lead is interested in apples, I want an email to be sent to john@example.com. If a new lead is interested in oranges, then I want the email to go to melissa@example.com.
The formula to express this logic in the example file is:
```=IF(E2=”Apples”, “john@example.com”, “melissa@example.com”)
As you can see, my email recipient is now dynamic based on the response to my form question “Which product are you interested in”, which is located in column E.
If you have a more complex routing process, then instead of an IF statement you could create a matrix and use XLOOKUP instead. For example, if there are four different products with four different salespeople, then we could list out the routing information in a worksheet like this:
And then, in my email recipient column, use the following formula to match the form response to the email recipient:
```=XLOOKUP(E2, Sheet3!$A$2:$A$5, Sheet3!$B$2:$B$5)```
This formula looks for the form response selection (E2) in my Product array (Sheet3!$A$2:$A$5), and then returns the match from my Person array (Sheet3!$B$2:$B$5)
Now we are ready to set up our SENDGMAIL formula. This is an Action formula in Lido, which means that it only runs (e.g. sends an email) when it is explicitly triggered, either manually or via an automation.
The formula for SENDGMAIL is:
=SENDGMAIL(<sender-credential>, “recipient@example.com”, “subject”, “body”, “status”)
Create another computed column for “Send Email”. In this example the new computed column will be in column I. Then, in cell I2, write the following formula:
```=SENDGMAIL(<sender-credential>, H2, F2, G2, J2)```
Where:
<sender-credential> is the email address that will send the emails. You will need to connect to a Gmail account the first time you use this formula in Lido.
H2 is the recipient email, which we calculated in the prior step based on certain conditions being met
F2 is the Email subject
G2 is the Email body
J2 is an empty cell in the next column where we will place the status of whether an email has been sent
To test this action, right click on a SENDGMAIL formula and select “Run action.”
You will be prompted to login to the sender email the first time. After the email is sent, you'll notice a "success" message briefly appear in the cell. The cell you designated as the status cell (J2 in this example) will also be updated to display "success."
Next, we want to update our logic so that we only send one email per new form response.
First, make a new Linked Column for Status. This is a column type in Lido where you can enter text (like “success”) and keep it linked to the original record it was entered in.
You will be prompted to choose an ID column. This is what Lido uses to keep track of records so it is important to pick a column that has unique values, like timestamp.
Back in the Send email column, update your formula to include an IF statement to check for whether an email has already been sent.
```=IF(J2<>”success”, SENDGMAIL(<sender-credential>, H2, F2, G2, J2))```
Make sure that the status cell you check in the IF statement (J2 in this example) is the same as the status cell you have designated in the SENDGMAIL formula.
After making this change, you will see that the SENDGMAIL formula does not appear anymore in the column after an email has already been sent.
All that is left is to automate our SENDGMAIL column so that the emails are automatically triggered as new rows get added.
Click in the 3-dot menu for your Send email column and select Run column on a schedule.
This will open the automation panel. In this screen, you will need to select an interval for how frequently you want Lido to check for updates.
Every time an automation runs, Lido first fetches the newest data from your Google Sheet, which is connected to your Google Form, then triggers all of the SENDGMAIL actions in the column.
We will choose a 15 minute automation interval in this example. Then hit save.
Your automation is now running! When you receive a new form response, Lido will automatically send an email based on the content of that response.
It is easy to also send different email content based on the form response.
Let’s say we still want to send emails based on whether somebody has selected Apples or Oranges, but we want the messages to differ based on the response. In this case, we can set up a matrix of our different email responses in Step 3: Make response email templates.
And then, in Step 4: Construct dynamic email content, we will need to add an XLOOKUP to the STRINGTEMPLATE formula that we use to construct the dynamic emails.
The formula for Email Body is:
```=STRINGTEMPLATE(XLOOKUP(E2, Sheet1!$D$2:$D$3, Sheet1!$F$2:$F$3))```
Where:
E2 is the form response value that dictates which message is sent (e.g. Apples vs Oranges)
Sheet1!$D$2:$D$3 is the range in my email template matrix that matches to different response possibilities (e.g. Apples and Oranges)
Sheet1!$F$2:$F$3 is the range in my email template matrix that contains my different email bodies
For example, you could route emails to different team members based on form responses. If you're using Google Forms for tech support, you can notify the manager when the customer picks "high" for the severity level. A school might send parents' responses to different teachers based on the student's class choice.
You can also change your email content based on a form response. For example, an online store could send different auto-responses to customers depending on the product they chose.
We hope that you now understand how to send emails based on a response in google sheets. Check out some of our other articles for even more tutorials! We recommend reading our guide on how to forward responses from Google Forms to your email and send responses to multiple emails from Google Forms.
