How to Send Automated Notification Emails (Easiest Way in 2024)
In this article, we explain how to set up automated email notification system for Google Forms responses. Simply follow the steps below!
Send Automatic Email Notifications for Google Forms Responses
Below we outline the steps on how to send automated email notifications for Google Forms responses:
Step 1: Connect Google Form Responses to a Google Sheet
To integrate Google Forms with Google Sheets, follow these instructions:
- Open your form in Google Forms
- Select "Responses" from the top menu
- In the upper right corner, click the spreadsheet icon
- Choose "Create a new spreadsheet"
- Click “Create”
Copy the URL of the spreadsheet you just created.
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.'
- Insert the URL of the Google Sheet from Step 1. You will need to sign in to a Google account that has access to this sheet.
- Choose the specific columns you wish to import into Lido; they will be automatically 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: Create 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, and set its type to Computed Column.
Inside of this 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 looks like this:
=STRINGTEMPLATE(“template_cell_location”)
For instance, our template cell for the email subject is in Sheet1!$B$1, so the formula for cell F2 in the computed column would be:
```=STRINGTEMPLATE(Templates!$B$1)```
When submitted, our table now looks like this. Notice the dynamic replacement of the [@Name] variable in every row.
Repeat these actions to make another dynamic computed column for the email body. Begin by creating a new computed column and inputting:
```=STRINGTEMPLATE(Sheet1!$B$2)```
Ensure to adjust “template_cell" in this formula to reflect your specific template cell location, and remember to use $ to fix the cell reference, ensuring consistency across all rows.
You should now have a table that looks like this:
Step 5: Add Column for Conditional Email Recipients
Next, we introduce a method to select email recipients based on form responses.
Create an additional computed column named “Email recipient.” We will use an IF formula within this new column to decide which email address should receive a message, depending on the response content.
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”) ```
This configuration dynamically adjusts the recipient in our email based on the answer to the form question about product interest, 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 in the email recipient column, use a formula like this to match form responses to the appropriate email:
```=XLOOKUP(E2, Sheet3!$A$2:$A$5, Sheet3!$B$2:$B$5)```
This formula searches for the selected form response (E2) in a product list (Sheet3!$A$2:$A$5) and retrieves the corresponding contact from my Person array (Sheet3!$B$2:$B$5).
Step 6: Add SENDGMAIL Formula
We're now ready to use the 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 SENDGMAIL formula is structured as follows:
=SENDGMAIL(<sender-credential>, “recipient@example.com”, “subject”, “body”, “status”)
To set this up, add another computed column labeled “Send Email” in column I. Then, enter this formula in cell I2:
=SENDGMAIL(<sender-credential>, H2, F2, G2, J2)
Where:
Here, <sender-credential> represents the sending email address. 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."
Step 7: Add Logic so Emails Only Send Once
Our next goal is to adjust our logic to ensure each form response triggers only one email. First, establish a new Linked Column named "Status," which will store text such as “success” and link back to the original data record.
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 or respondee email.
Modify the Send Email column formula to include an IF statement checking if an email has been sent:
```=IF(J2<>"success", SENDGMAIL(<sender-credential>, H2, F2, G2, J2))```
Ensure the IF statement’s status cell (J2 in this example) is consistent with the one used in your SENDGMAIL formula.
After this modification, the SENDGMAIL formula will no longer display in the column once an email has been dispatched.
Step 8: Set up Automation
Finally, we will automate the Send Email column to have emails sent automatically as new responses are added.
Click the three-dot menu on 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. Here's how we do it:
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
When might someone want to send emails based on response?
Emails can be routed to various team members based on form responses, such as tech support severity or educational class selections. This system also allows for customized auto-responses in settings like online retail based on selected products.
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 automated notification emails based on responses in Google Sheets.
Check out some of our other articles for even more tutorials! If you enjoyed this article, you might also like our article on how to send automated emails to customers.