In this article we’ll walk through how to send email in google sheets based on expiring subscription dates in a Google Sheet. Read on to learn more:
Clean up your Google Sheet so that it looks like the image below and follows these guidelines:
Lido is a tool that allows you to automate emails based on dates in Google Sheets. If you don’t have a Lido account, you can create one for free here.
Create a new Lido file, then click on Connect Data. Select Google Sheets and paste in your Google Sheet URL. If it’s your first time connecting a Google Sheet to Lido you will be asked to authenticate into an account that has access to the selected sheet.
In the next screen you will be prompted to choose which columns from your Google Sheet you want to connect to Lido.
Click Add Data. This creates a Table in Lido. Refresh the table to automatically pull in the latest data from your Google Sheet. To edit or add more records, make the changes in your Google Sheet first.
Your spreadsheet should now look like this:
In this example I want to send my emails exactly 30 days before the renewal date. I can do this by adding a Computed Column to my Table. This is a type of column that takes a formula and applies it down every single row. To do this right click on the last column header in your table and choose Insert > Insert Computed Column Right.
The formula in row 2 for 30 days before my renewal date in column F is:
```=F2-30```
Enter this into Cell G2 and hit enter. This will create a Computed Column that calculates 30 days before each renewal date in the Table.
Now we need to write templates for our reminder email subject and body.
When you imported your google sheets data a new spreadsheet was automatically created. Click on Sheet1 at the top of the spreadsheet and write out your desired messages in this spreadsheet. You can reference table column headers as variables like this: [@Column]
Tip: you can add line breaks in the cell by using cmd+enter on a mac or alt+enter on a PC. Line breaks in a spreadsheet cell will also send as line breaks in the email.
Next we need to go back to our Table and add two more Computed Columns for email subject and body. Again, right click on the last column header in your sheet and click Insert > Insert Computed Column Right.
Lido’s STRINGTEMPLATE formula will let us substitute the [@Column] variables we used in the templates with the actual values from those columns in each row.
The subject in the example is in Sheet1!B1. So we need to add a computed column with the formula:
```=STRINGTEMPLATE(Sheet1!$B$1)```
It’s important to remember to anchor your cell reference using $. Otherwise your formula will error.
Notice how the Name and Renewal date have been substituted in with the details for each row. Now do the same thing for body:
```=STRINGTEMPLATE($B$2)```
Now we’re ready to send our emails. The formula to send emails in Lido is:
=SENDGMAIL(sender-credential, recipient, subject, body)
In this example, if you want to send emails to yourself, your formula in row 2 will be:
```=SENDGMAIL(<sender-credential>, “your@email.com”, H2, I2)```
If, instead, you want to send emails to each individual row in the table with the details of their upcoming renewal, your formula will be:
```=SENDGMAIL(<sender-credential>, B2, H2, I2)```
Create a new computed column with your selected formula.
Right click on the SENDGMAIL formula and select Run Action to send an email as a test. You will need to authenticate into the sender email address the first time. Double check to make sure that you haven’t mis-spelled it!
The last step is to automate running our SENDGMAIL actions 30 days before the renewal date. Lido automations work by checking a range you select (like a column) for Action formulas, and triggering all of the Actions at every automation interval.
Therefore, since we only want SENDGMAIL to appear on days that are also our Reminder send date, we need to update our formula in column J a bit to account for that:
```=IF(TODAY()=G2, SENDGMAIL(<sender-credential>, “your@email.com”, H2, I2))```
Replace G2 with whatever column your Reminder date is located in, if necessary.
Now, we should only see SENDGMAIL formulas returned for today’s date:
Click on the 3-dot column menu in your Send email Action column and select Run column on a schedule.
This will open the automation panel. In the automation panel, select Every 1 Days then pick a time for when you want the emails to send. Beware that if you set a more frequent automation interval, you'll receive multiple emails throughout the day, as the action trigger condition remains active all day.
Click Save and your automation will be immediately active. Remember that you must send at least one email first manually by clicking Run action before your automation will work, since otherwise there is no way for Lido to access your email sending credentials.
We hope that you now have a better understanding of how to send emails based on date in google sheets.
If you enjoyed this article, check out our article on how to send reminders in google sheets or how to attach google sheets to email messages.
