Send Email Based on a Response in Google Forms (Easiest Way in 2023)
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.
Send Emails Based on a Google Forms Responses
Step 1: Connect Google Form Responses to a Google Sheet
Follow these steps to use Google Forms’ built in feature:
Open your form in Google Forms
Click Responses in the top bar
Click on the spreadsheet icon in the top right
Select Create a new spreadsheet
Copy the URL of the newly created Google Sheet.
Step 2: Connect Google Form Responses Spreadsheet to Lido
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:
Create a new Lido file
Click on 'Connect Data' then select 'Google Sheets.'
Paste the URL of the Google Sheet from Step 1. You'll be prompted to log in to a Google account with access to that particular Google Sheet.
Now, select the specific columns you want to import into Lido. All of them will be selected by default.
Click Add Data. This will create a Lido table of your Google Form responses.
Step 3: Make Response Email Templates
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].
Step 4: Construct Dynamic Email Content
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:
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:
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:
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:
Step 5: Add Column for Conditional Email Recipients
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 email@example.com. If a new lead is interested in oranges, then I want the email to go to firstname.lastname@example.org.
The formula to express this logic in the example file is:
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:
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)
Step 6: Add SENDGMAIL Formula
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.
email@example.com is the email address that will send the emails. Make sure that this is a Gmail account you have access to.
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."
Step 7: Add Logic so Emails Only Send Once
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.
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.
Step 8: Set up Automation
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.
How to Send a Different Email Subject or Body Based on a Form 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.
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
When might someone want to send emails based on response?
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!