This step-by-step guide will show you how to automatically send emails based on new entries to a Google spreadsheet using Lido.

Overview video - How to automatically send emails to new entries in a Google Spreadsheet

Step 1: Connect to your Google Sheet

Start with a Google Sheet of class signups. You might populate this sheet manually, as a result of a Google Form, or some other way.

Make sure that:

the first row contains header cells
there are no blank rows

Step 1

Copy the sheet URL.

Step 2

In Lido, click on "Connect Data" > Add Connection > and paste in the sheet URL

Step 3

Choose the columns you want to bring in and click on "Add Data"

Your Google Sheet data will live stream into Lido.

If you want to manually refresh the Google Sheet data click on the refresh data button.
when you set up automations, data is automatically refreshed before each automation is run.

Create Subject and Body Columns

The next thing we're going to do is create custom subject and body columns for each row in the spreadsheet using regular spreadsheet formulas pulling in the fields that we want from each record.

Step 1 - Create Subject Column

Right click on the previous column, and choose “Insert Table Column Right > Computed Column”. Computed Columns automatically copy formulas for all rows in a table.
In row 2 of the newly created column, type the following:
="Thanks for signing up "&A2

You should now see dynamic subjects for each row:

We’ll rename it by double clicking on the header cell and calling it Subject.

Step 2 - Create Body Column

Right click on the Subject Column, and choose “Insert Table Column Right > Computed Column”.
In row 2 of the newly created column, type the following:

="Hi "&A2&"thanks for signing up for "&C2

Rename the column by double clicking on the header cell, and naming it "Body".

📘
When you enter a formula in any row in a Computed Column, that formula is automatically updated for all rows and new ones

You should now see dynamic body column for each row.

Create a SENDGMAIL() column

Now we'll create a column that sends emails using one of Lido's action formulas, SENDGMAIL().

SENDGMAIL("[email protected]","[email protected]","Subject","Body")

The sender is any Gmail or Google apps account.

Right click on the Body Column, and choose “Insert Table Column Right > Computed Column”.
Type in the SENDGMAIL formula referencing the Subject and Body columns

SENDGMAIL("[email protected]", B2, D2, E2)

You should now have a column of SENDGMAIL() pulling in the appropriate values for each row:

You can send a single email as a test by right clicking on any of the send emails and choosing run action. Then you can look in your send items and see the email that the send out.

Make sure only one email goes out

Next, we want to make sure that only one email is sent to each lead. We'll do that by creating a "Sent" column that tracks whether an email has been sent and update that cell when an email is sent.

Step 1: Create "Sent" Column

Right click on the "Send the email" column, and choose “Insert Table Column Right > Linked Column”. A Linked Column is essentially a regular table column, but it works with live data, such as data streaming in from a Google Sheet. The values you type in a Linked Column stay tied to the correct source record, even if the order changes.
Choose "Email" as the unique ID.
Double click on the header cell and rename it "Sent"

Step 2: Update the "Sent" column when you send an email

Double click on any row (we'll use the 2nd) of the "Send the email" column, and wrap the SENDGMAIL() formula in another one of Lido's action formulas, UPDATECELL() like so:
=UPDATECELL(G3, SENDGMAIL("[email protected])", B3, D3, E3))

Send another test email by right-clicking on a single cell in the "Send the email" column, and choose "Run Action". You'll see that the status "success" is put in column G after its run.

Step 3: Use an IF statement to check if an email has already been sent

Wrap the formula in "Send the email" in an IF statement, checking if the "Sent" column equal to "success" as so:
=IF(G2<>"success", UPDATECELL(G2, SENDGMAIL("[[email protected]](mailto:[email protected])", B2, D2, E2)), "nothing to do")
To make it easier to understand, we add "nothing to do" if an email has already been sent. Now, your file should look something like this.

Step 4: Run the whole column

To run the whole column, click on the 3-dot menu in the header cell of the "Send the email" column, and choose "Run Column Now". Emails will go out for all rows except row 2, which has already been sent.

Automate the column to run every 5 minutes

Instead of manually running this column we want it to automatically send out emails based on new records.

Right Click on the "Send the email" column and choose Run Column on a Schedule, choose every 5 minutes, and then Save.

Every 5 minutes the following will happen:

The latest data from the Google Spreadsheet will be pulled
The column "Send the Email" will be run.
Any new records since the last time it was run (5 min ago) will be sent out and marked as success.

You're done!

The next time a new record is added to your original Google sheet, within 5 minutes it will be pulled into Lido and an email will be sent.

Troubleshooting

My Google Sheets data has disappeared or isn't displaying as expected

If your data isn't appearing as expected, chances are that you just need to reformat your Google Sheet to work with Lido's integration:

The first row of your data range should be your column headers
1. Column headers cannot be blank, contain special characters or line breaks
Remove empty rows and columns
1. Individual cells inside of the selected range can be blank, but a completely empty row or column will "stop" the range of data that's fetched

🚧
Changing column or worksheet names in your Google Sheet will break their connection with Lido
If you're missing columns or data, click edit data in your dataset then re-add the missing columns in the data browser.

Want more help?

Schedule a support video call
Join our Slack group
Use the live Chat on our main website