In this article we show you exactly how to send an email notification when a new row is added to a Google Sheet from a new response coming from Google Forms. Check out our other article on How to send an email from Google Sheets if you simply want to send an email.
Follow the steps below to easily send an email when a new row is added in Google Sheets. This is commonly used by people who want to easily email google form submissions or receive a notification for each new submission, but there are many use cases.
Ensure that the Google Sheet you want to automate satisfies the following requirements:
This is an example of a properly formatted Google Sheet:
Lido is a new spreadsheet built to automate manual tasks like sending emails. It’s fully compatible with Google Sheets so you can seamlessly connect an existing Sheet.
Click Here to create a free account
In a new Lido file, click Connect Data then select Google Sheets.
Paste your Google Sheet URL and then connect to your Google Sheet account. Make sure that you login with an account that has access to the Google Sheet you want to connect.
The first time you connect you will be asked to log into a Google Account that has access to the spreadsheet URL. Make sure that you check the box to give Lido access to your Google Spreadsheets.
.png)
Then, select which columns you want to add and click Add Data.
This will create a live data Table in Lido. Updates to your Google Sheets data will automatically populate in Lido. To make changes to this data, edit it in the Google Sheet then refresh your data in Lido.
Lido automatically created a new spreadsheet tab for your data called Sheet2. Click on Sheet1 at the top of the Screen. We will enter the subject and body messages you intend to send here and reference them later.
You have the flexibility to use various column names from your connected Table as variables by referencing them with [@ColumnName] syntax:
In our example this is:
Subject:
New Form Submission: [@Name}
Body:
Hi [@Name],
We have received your form submission. You indicated interest in:
[@service]
We will be in touch soon.
*If you hold Command (Mac) / CTRL (Windows) and click enter you will be able to line break within the cell.
Next, we need to add columns to our Table to make our email subject and body messages dynamic for each row.
First, add a computed column to your table by right clicking on the header of the last column in your table and selecting Insert > Insert Computed Column Right. This is a column type that applies the same formula down every row of a table.
In the computed column, we want to use the formula STRINGTEMPLATE. This is a formula that replaces template text with the actual content in the specified columns from that row.
For our email subject, we will reference the cell that we created in Step 4 with the email subject by entering Sheet1!$B$1. Remember to anchor this cell reference with $ so that it stays the same for every row in the table.
```=STRINGTEMPLATE(Sheet1!$B$1)```
Then do the same for the email body. In our example we will enter:
```=STRINGTEMPLATE(Sheet1!$B$2)```
This is what our spreadsheet now looks like:
Lido has a special type of spreadsheet formula called an Action. These are formulas that can behave like buttons and send information outside of the spreadsheet when triggered. Action formulas don't evaluate immediately when you type them (as normal spreadsheet formulas do). You need to explicitly run them manually or with an automation. The Action formula to send emails is:
=SENDGMAIL(<sender-credential>, recipient, subject, body, status)
The sender email must be a Gmail or Gsuite account. The status argument is a spreadsheet cell location where you want to output whether the email has successfully sent.
We want to create a computed column for our SENDGMAIL formula so that we can send emails for each row in our table as needed. Create a new computed column and type in your =SENDGMAIL formula in Row 2.
In our example, our SENDGMAIL formula in cell F2 is:
```=SENDGMAIL(<sender-credential>, B2, D2, E2, G2)```
Start typing =SENDGMAIL(
When you're in the first argument, click on the "+" to add a sender credential to your Gmail (or Google Apps) account.
Upon successfully adding your SENDGMAIL formula, your spreadsheet should look like this:
First, let’s run the SENDGMAIL formula to test it. Right click on one of the SENDGMAIL formulas and click Run Action. This will actually send an email, so use a test recipient address if needed.
If your formula was successfully run, you should see “success” in cell G2.
Let’s turn column G into a Status column. Type the word "Status" into cell G1, and when prompted, choose "Linked Column". A Linked Column is a special kind of column in Lido that lets you add data in Lido that stays "stuck" to the correct row in your source data.
For a Linked Column to work, it needs to use another column as an ID column. It’s important to pick an ID column with unique values, since this is how Lido keeps track of which rows have been sent even if the order of your data changes. We will choose email as our unique ID.
Lido automations work as follows. On your specified interval, Lido will:
In order to make sure we only send an email when a new row is added, we want our SENDGMAIL formula in our Action column to only display if an email has not already been sent.
All we need to do is add an IF statement to account for this logic. When an email is sent our status column is marked “success”. We will use an IF statement to check if the status column already has "success" in it. So replace the formula in row 2 of your Send Emails column with the code below and the SENDGMAIL formula will no longer display for rows where emails that have already been sent. You can modify this formula for your unique cell references.
```=IF(G2<>"success", SENDGMAIL(<sender-credential>, B2, D2, E2, G2))```
As you can see in this example, there is an action formula displaying in row 3 but not row 2 - which is marked FALSE because an email has already been sent.
To re-set the action for that row again, simply delete the “success” from the Status column.
Now we are ready to automate triggering actions in our Send emails column.
Click on three dots beside your "Send Emails" column header and select Run column on a schedule.
This will open the Lido automation panel. Simply select a schedule for how frequently you want Lido to check for new Actions to trigger and click Save.
What happens if you simply want to send an email to yourself every time a new row is added to your google sheet from your google form?
The only difference is that instead of using the email in each row as the recipient, you will use your own email address. In our example we would use the following formula.
```=IF(G2<>"success", SENDGMAIL(<sender-credential>, "your@email.com", D2, E2, G2))```
Note that B2 has been replaced with our email address as the recipient email.
You should also change your subbject and body messages to account for this difference. In our example we will use:
Subject: New Form Submission
Body: The Customer [@Name], Indicated interest in: [@Service]
If you enjoyed this article, then check out our article on how to automate email from google sheets or how to set up google sheets email notifications.
