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 Sheetsif you simply want to send an email.
How to Send an Email When a New Row is Added
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.
Step 1: Prepare your Google Sheet data
Ensure that the Google Sheet you want to automate satisfies the following requirements:
Column headers are in the first row of the sheet
Column headers do not contain any special characters or line breaks
There are no empty rows between your header and your data or empty columns
This is an example of a properly formatted Google Sheet:
Step 2: Create a Free Lido Account
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.
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.
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.
Step 4: Create Email Subject and Body Templates
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:
New Form Submission: [@Name}
We have received your form submission. You indicated interest in:
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.
Step 5: Create Dynamic Email Subject and Body Columns
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.
Then do the same for the email body. In our example we will enter:
This is what our spreadsheet now looks like:
Step 6: Create a Send Email Column that uses =SENDGMAIL()
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:
First, let’s run the SENDGMAIL formula to test it. Right click on one of the SENDGMAIL formulas and click Run Action. You will need to authenticate into your Gmail account the first time. 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.
Step 8: Add Trigger Logic to Send Email Column
Lido automations work as follows. On your specified interval, Lido will:
Fetch the latest data from your connect data source (Google Sheets in this example)
Re-run all spreadsheet calculations based on the latest data
Trigger all Action formulas in the automated column
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.