Trigger Email Reminders Based on Dates in Google Sheets

Recently I was asked to set up a system that sends an email reminder about expiring subscriptions listed in a Google Sheet. I completed this task using a Google Sheets script. I am going to show you how to set this up on your own. If you are just copying and pasting the script, don’t forget to change the recipient email to your own, paste in your email where it says subscriptionscript@gmail.com.

Note: if you’re reaching out for help please provide your script and share a sample of your spreadsheet with me.

This article is split into three sections:

• Creating your Google Script.
• Running the Script.
• Scheduling the Script to Autorun.

Creating your Google script.

Spreadsheet example, sample data from Mockaroo

Here is a sample Google Sheet I will be using to explain the process. The trigger is based on the date in the expiration date column (column G).

Here is what the email reminder looks like.

Sample email notification from triggered via Google Sheets

To add a script click Tools in the menu bar and then script editor. A new window will open and this is where we’ll write our script.

Below is the script we will be using. I will discuss what each part of the script is doing so you can understand and customize it to your needs.

Let’s break it down.

// today's date information
  var today = new Date();
  var todayMonth = today.getMonth() + 1;
  var todayDay = today.getDate();
  var todayYear = today.getFullYear();
// 2 weeks from now
  var twoWeeksFromToday = today;
  twoWeeksFromToday.setDate(twoWeeksFromToday.getDate() + 14);
  var twoWeeksMonth = twoWeeksFromToday.getMonth() + 1;
  var twoWeeksDay = twoWeeksFromToday.getDate();
  var twoWeeksYear = twoWeeksFromToday.getYear();
// 1 month from now   
  var newToday = new Date()  var oneMonthFromToday = new        Date(newToday.setMonth(newToday.getMonth()+1));  
  var oneMonthMonth = oneMonthFromToday.getMonth() + 1;  var oneMonthDay = oneMonthFromToday.getDate();  
  var oneMonthYear = oneMonthFromToday.getYear();

This part of the code is saving different dates into variables such as: today, twoWeeksFromToday, and oneMonthFromToday. My client requested email alerts of subscriptions that are expiring on the current day, 2 weeks from the current day, and 1 month from the current day.

Every time this code runs today’s date will be generated, the date for 2 weeks from now will be generated, and the date for 1 month from now will be generated.

I am creating variables for the day, month, and year of each of these dates to compare with the day, month, and year of the expiration dates on the sheet. I’m creating these variables because JavaScript dates include time. If we compare 2 dates that are the same but have different times, no email alerts will be sent out.

Other things to consider:

• When you use the .getMonth() method, January will equate to 0, February to 1, and so on. I add 1 to the month variable so we get the correct number for the month.
• twoWeeksFromToday.setDate(twoWeeksFromToday.getDate() + 14); I had to play around with adding 14 days to the current date. This ensures that the month changes if 2 weeks from today’s date is inside of a different month.
• If you want to log out any data, use Logger.log(), run your script, and then click View -> Logs to see what is output. Skip to the Running the Script section to learn how to run your script if you’d like to view logs. Before you run your script make sure you save it, I named mine emailAlert. You can save it using File -> Save.

// getting data from spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 100; // Number of rows to process
var dataRange = sheet.getRange(startRow, 1, numRows, 999);
  var data = dataRange.getValues();

This part of the code is grabbing the actual data we need from the spreadsheet. Learn more about these methods here.

• SpreadsheetApp.getActiveSheet() This gets the active sheet in the spreadsheet. I only have one sheet in my example. If you have multiple sheets you may want to use SpreadsheetApp.getSheetByName(name)
• var startRow tells the script the first row we want to look at
• var numRows tells the script the number of rows to process
• sheet.getRange(startRow, 1, numRows, 999) The parameters for this method are: the first row you want to get data from, the first column you want to get data from, the number of rows you’d like to get data from, and the number of columns you’d like to get data from.
• dataRange.getValues() this is taking the range we declared and grabbing the data we want to look at.

Next, we loop through the data we just grabbed using a standard for loop. We grab 1 row of data to examine. It may be helpful to use Logger.log() here to see what you’re looking at! You could insert Logger.log(row) underneath the row variable to see what the row contains.

//looping through all of the rows
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];

The next part of the code is formatting the email that will be sent. This code is a bit ugly but unfortunately, you cannot use template literals inside of Google Scripts yet.

var expireDateFormat = Utilities.formatDate(
      new Date(row[6]),
      'ET',
      'MM/dd/yyyy'
    );
// email information
    var subject = '';
    var message =
      ' A subscription is expiring. ' +
      '\n' +
      ' Name: ' +
      row[0] +
      '\n' +
      ' Phone Number: ' +
      row[1] +
      '\n' +
      ' Email Address: ' +
      row[2] +
      '\n' +
      ' Description: ' +
      row[3] +
      '\n' +
      ' Notes: ' +
      row[4] +
      '\n' +
      ' Sign Up Date: ' +
      row[5] +
      '\n' +
      ' Expiration Date: ' +
      expireDateFormat;

I put the expiration date in the subject of the email as well as the body of the email so I wanted to remove the time from that date to make it look prettier. This is whats going on in the expireDateFormat variable.

I save an empty variable called subject. We will customize this based on the timeframe of the expiration date. The message variable will be the body of our email. It contains information from the rows in the spreadsheet. I insert data from the spreadsheet by extracting it from the current row array. In this example Name is in the 1st column of the spreadsheet, we can retrieve this information from the first element in the row array which is at the zeroth index row[0]. You don’t have to include every column or piece of information in the body of your email, this is up to you to customize.

//expiration date information
    var expireDateMonth = new Date(row[6]).getMonth() + 1;
    var expireDateDay = new Date(row[6]).getDate();
    var expireDateYear = new Date(row[6]).getYear();

Next, I grab the expiration date from the current row we’re looking at. I am using the same format as the dates in the first section of the code.

//checking for today
    if (
      expireDateMonth === todayMonth &&
      expireDateDay === todayDay &&
      expireDateYear === todayYear
    ) {
      var subject =
        'A subscription expired today: ' + row[0] + ' - ' +  expireDateFormat;
      MailApp.sendEmail('subscriptionscript@gmail.com', subject, message);
    }
//checking for 2 weeks from now
    if (
      expireDateMonth === twoWeeksMonth &&
      expireDateDay === twoWeeksDay &&
      expireDateYear === twoWeeksYear
    ) {
      var subject =
        'A subscription is expiring in 2 weeks: ' +
        row[0] +
        ' - ' +
        expireDateFormat;
MailApp.sendEmail('subscriptionscript@gmail.com', subject, message);
    }
//checking for 1 month from now
    if (
      expireDateMonth === oneMonthMonth &&
      expireDateDay === oneMonthDay &&
      expireDateYear === oneMonthYear
    ) {
      var subject =
        'A subscription is expiring in 1 month: ' +
        row[0] +
        ' - ' +
        expireDateFormat;
MailApp.sendEmail('subscriptionscript@gmail.com', subject, message);
    }

This part of the code is doing the same thing 3 times in a row, with different date requirements. To explain, I will discuss the code that is checking whether the current row’s expiration date is today.

We compare the expiration date month with today’s month, the expiration date day with today’s day, and the expiration date year with today’s year. If they all match we create a subject line for our email that includes text saying a subscription expires today, the name of the person, and the expiration date.

We then use the MailApp.sendEmail() method to send an email to the recipient. The sendEmail() method takes 3 parameters: the recipient email, the subject, and the message or body of the email. The email will be sent from the owner of the spreadsheet.

We have finished writing our script! How do we run it?

Running the Script.

Screenshot of the menu in Google Sheets script editor.

To run your script press the play button located in the menu. Before you run your script you will be required to save it.

After you hit the play button, a pop-up window will appear saying Authorization is required. Click Review Permissions.

Next, you will have to sign in to your Google account. Choose the account you’d like to log into.

After you log in, the pop up will say This app isn’t verified. Click Advanced.

After you click advanced, more text will pop up. Click Go to email alert (unsafe). email alert is what my script is saved as it may say something different if you saved your script under a different name.

Next the popup will say email alert wants to access your Google Account. Click Allow. The pop up should close and we will be able to run our script. Create a row of fake data with today’s date to test your script. Click the play button to run the script. If everything is working correctly, you should have received an email.

Scheduling the Script to Autorun.

Next, I will tell you how to automatically run this script every day or every week or however often you’d like. It would be cumbersome to have to manually run this script every day.

First, let’s make sure we are working in the correct time zone. Click File in the top menu, then click Project Properties. A modal will pop up and you will be looking at the info tab. Scroll down to the bottom and there will be a section called Time zone. Choose the time zone you are in. I am in NYC so I am choosing GMT-05:00 Eastern Time.

Screenshots showing how to choose the correct time zone when setting up a script.

Now let’s set up our trigger. Press edit in the menu and then click Current project’s triggers. A new window will open. Click the blue +Add Trigger button in the bottom right corner.

A modal will pop up that allows us to configure our trigger. There are a few options you can choose from based on how / when you’d like your script to run. My client wanted their script to run every morning so when they got into work they could review any customers with expiring subscriptions. Below is a photo of my preferences.

We only have one function in our file so emailAlert is the only function to choose.

We only have Head as a deployment option.

I want my script to run every day at a certain time so I chose Time-driven as the event source.

I want a daily trigger, so I chose Day Timer. Other options are hourly / monthly / weekly.

Select Time of day allows you to choose when the script will run. My script runs in the morning between 6 am-7 am.

Another way to set up the trigger is based on when the spreadsheet is touched, to set this up your event source will be From spreadsheet. You then have the option to run the script when the spreadsheet is opened, when it is edited, when it is changed, or when a form is submitted. There are lots of different options here so feel free to play around!

Click save and your script will trigger based on the preferences you choose. I suggest inputting a couple of fake rows/dates in your spreadsheet for tomorrow or the next day to make sure the script is working properly.

I hope this was a helpful tutorial. If you have any questions or a way to optimize this solution feel free to leave a comment below. You can also reach me on Twitter or LinkedIn.