Automated Email Reminders

Since last we spoke I’ve published a couple behind the scenes videos at how I write and distribute my two newsletters…

1. Notion + Beehiiv + Canva system: https://youtu.be/uQtD_FV75tc

2. Daily podcast system with Transistor: https://youtu.be/DY_-bEhvO9c

Now to the sheets…

Spreadsheet Automation

I had the chance to check out a new piece of software recently called Lido. They make automating data from your spreadsheets easier.

You know I love Apps Script and diving into the weeds with automating my own spreadsheets, but sometimes you need an easy, reliable solution.

Reminder Emails

For our project, let’s say you’ve got a list of clients with contract renewals coming up. You want to send out a reminder email to them a few days ahead of time.

You also have all the master data in a Google Sheet and would prefer not linking up a third-party software that takes you out of your comfort zone of spreadsheets.

Here’s our data👇

After logging into Lido, we connect our Google Sheet (they’ve got a bunch of other platforms they integrate with too).

We select and connect our Google Account and the spreadsheet where our data lives.

We select the sheet where our data lives and can preview what will be imported.

This brings our data into a table inside of Lido. Now, out of the gate this is a big plus in my book since one of my pet peeves with Google Sheets is the inability to create an actual data table. 😀

Email Template Setup

The green shaded cells are the un-editable data from our Google Sheet. Next to them, we will create a few columns of our own now.

First up is a reminder email date a few days ahead of the due date. A simple formula will work here =D2-3.

Lido fills this down automatically since it’s linked to a table (similar to Microsoft Excel’s behavior). This is a small, but welcome quality of life improvement.

Now we need to template our email. We can create a new sheet by selecting the plus icon in the top left. We can do this anywhere, but having it on a separate sheet makes things a little more organized.

We need a template for our subject line and our body.

To use data from a column in our Lido table, we’ll use the bracketed notation [@Col_Name]. Replace “Col_Name” with whatever you’ve named your columns. In our case, it’s simply Contact.

STRINGTEMPLATE() Function

Lido has some special, built-in functions. The STRINGTEMPLATE() function takes a cell as an argument and replaces columns formatted with the bracket notation above with column values in a corresponding row of the given table.

So, for the Contact column in our current table, each row in the Subject and Body columns will replace [@Contact] with the actual contact’s name i.e. Leo, Donny, Raph…etc.

We do the same thing in the Body column.

SENDGMAIL Function

The other custom function we’ll use is SENDGMAIL(). This lets us hook up our Gmail account and send an email. We have to have at least a recipient email address, a subject line and a body.

By checking to see if today is equal to the reminder email date in F2, we create a TRUE or FALSE condition for SENDGMAIL.

=IF(TODAY()=F2, SENDGMAIL([email protected], C2, G2, H2, J2))

By selecting the three dots in the top corner of the SENDGMAIL cell, we can preview what the email will look like for the ones where our IF statement condition is true.

Now, we can run these functions by selecting “Run Action” after clicking those three dots, but the point of all of this work is to set up an automation.

Automating the Emails

Open the Automation menu from the top right.

From here, we can automate a cell or a column.

Now we select our table, column and schedule for the automation.

For Lido’s free plan, we have access to a once per day schedule for the automation. And for the current example, that’s all we’d want it to run for anyway since we are checking for whether today is equal to the reminder date or not.

But on the paid plans, you can run automations monthly, weekly, daily, hourly or up to as frequently as every five minutes.

There is also a history log that records success and error messages when the automation runs.

Set it and Forget it

Every time an automation runs in Lido, it will do the following even if the file is closed or you are logged out:

1. Pull in any new Google Sheet rows.

2. Update the Lido spreadsheet cell values to depict the current state of your Google Sheet.

3. Auto-send the emails that need to be sent according to the set conditions (in this example, the IF statement checking today’s date against the reminder date).

Thank you so much!

It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!

Would love to say hi. Here are the best places to find me:

• 📺️ Videos on YouTube

• 💾 Products on Gumroad

• ✉️ Connections on LinkedIn

Need More Help?

• Use my partner link to check out Lido for yourself. You'll get 15% off for the first 12 months if and when you upgrade.

• Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems.

• Hire an expert to help complete your next Google Sheets, Apps Script or Google Workplace project.