Did you know that there is no native reminder function in Google Sheets? However, with just a few clicks, you can easily import your Google spreadsheet into Lido and set up email reminders. Exciting, right?
Setting Up Email Reminders in Google Sheets using Lido
1. Prepare and Copy the Link to Google Sheets
Before we dive in, make sure your Google spreadsheet is formatted like our example sheet here:
- Your data should be in a continuous table without any empty rows or columns.
- The column headers (Name, Email Address, etc.) should be in the first row and should not contain any special characters or line breaks.
Copy the URL of your Google spreadsheet. We will need it to connect your spreadsheet to Lido.
2. Connect the Google Spreadsheet to Lido
If you don’t have a Lido account, don’t worry! You can easily create one for free at Lido Signup.
Lido offers premium spreadsheet formulas that are not supported in Google Sheets, such as SENDGMAIL(). This formula will allow us to send our emails.
Create a new file, then click on the green Connect Data button.
Select Google Sheets from the list of integrations and paste the URL of your Google spreadsheet into the text box.
The first time you connect, you will need to log in to a Google account that has access to your Google spreadsheet. Make sure to check the box to give Lido permission to access your Google Sheets.
Choose the columns you want to add for the connection with Lido, then click Add Data.
3. Add a Column to Calculate When to Send the Reminder
Reminders should be sent before the deadline. For our example, we will send reminders two days before the deadline.
We will use a formula to calculate the reminder date: =deadline_date - 2, where deadline_date is the cell that contains our deadline. In our example, the deadlines are stored in column C. So, the deadline of the first entry listed is in C2.
Create a new calculated column and name it “Send Date”.
4. Add the Subject and Body of the Reminder Email
Now we are ready to add the subject and body of the reminder email.
Create email templates using the column names from our table by referencing them with the syntax [@Column].
Set up the templates for the subject and body of the email in a new spreadsheet.
Next, we need to combine these templates with our table to make the content dynamic. We will do this by creating two new calculated columns for the subject and body using the formula STRINGTEMPLATE().
You will also need to add a sending email address to your Gmail (or Google Apps) account by clicking “+ Add Sending Email Address” when you are inside the first argument of the SENDGMAIL formula.
5. Add a New Column for the SENDGMAIL() Formula
Unlike Google Sheets, Lido’s spreadsheet has a special type of formula called “action formulas.” SENDGMAIL() is one such action formula.
The SENDGMAIL() formula works like this:
=SENDGMAIL(sender, recipient, subject, body, [status_cell], [cc], [bcc], [attachments])
In our example, the basic SENDGMAIL formula looks like this:
=SENDGMAIL(<sender>, B2, E2, F2)
You will need to add authorization for SENDGMAIL by clicking “+ Add Credentials” in the first argument of the SENDGMAIL formula.
6. Automate the “Send Reminder” Column
Now, we can automate the execution of the “Send Reminder” column. For the rows where SENDGMAIL appears in the column, Lido can automatically run them once a day, even if the file is closed.
Click on the menu of the “Send Reminder” column and select “Run Column on a Schedule.”
Choose the time you want your messages to be sent and click “Save.”
Every day at 1:15 PM (or at the time you chose), Lido will retrieve the latest records from your Google spreadsheet, automatically check if there are reminder emails to be sent where TODAY = reminder_send_date, and if there are, it will execute each SENDGMAIL formula and send the emails.
If you found this article helpful, check out our article on how to set up mass email sending from Google Sheets or how to send emails based on a date in Google Sheets. You can also visit Crawlan.com for more information on advanced features in Google Sheets.
