Set Up Expiration Reminders using Google Sheets

As a Systems Administrator, you may have to manage due dates in many areas such as SLA renewals, server certificate expirations, access revokes on employee resignations, periodic tasks and etc. To keep track of all those due dates, Google Sheets is one of the convenient ways to organize and manage these expiration reminders. But the only downside of this is, that you don’t have a built-in function to send emails on expiration reminders.

Recently I was assigned a task to set up email reminders on server certificate expiration two times one is one month before the expiration date and the next one is one week before the expiration date. I have managed all managed domains in Google Sheets and was able to accomplish this task using a Google Sheets script.

In this article, I will demonstrate to you how to set up automatic reminder emails using a Google Sheet with Google Apps Script.

Prepare your Reminders Sheet

First, you’ll need to create a new Google Spreadsheet where you plan to store your reminders and your script. As an example, my spreadsheet looks as follows.

Creating Google Apps Script

Once you are ready with the data, it’s time to write an automation script. Google Spreadsheets out of the box supports Google Apps Scripts (Javascript-like scripting language) with a built-in script editor. To add a script to your Spreadsheet, click Tools in the menu bar and then script editor. A new window will open and this is the location where you going to write the script.

Let’s discuss what each part of the script is doing so that you can get a clear understanding of its execution and customize it to your needs.

Getting the Spreadsheet and Cells of Interest

We start by grabbing the spreadsheet by sheet name. In this example, I’m using “Sheet1” as the active sheet in the spreadsheet document.

var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');

Once we select the required sheet, we need to use sheet.getRange() to get the actual interested spreadsheet data. In this case, required to tell the script the first row we want to look at and the number of rows to process (every valid row in the spreadsheet).

var startRow = 2; // Ignore the cloumn hedings row
var numRows = sheet.getLastRow() - 1; // Get the valid number of rows that has content with excluding heading row
var numColumns = sheet.getLastColumn(); // Get the valid number of column that has content

Now, we need to use a sheet.getRange() function to get the actual spreadsheet data. It looks like this:

The parameters for this function 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 process, and the number of columns you’d like to process.

//Get data range dynamically and will return a 2-D array
var dataRange = sheet.getRange(startRow, 1, numRows, numColumns);
var data = dataRange.getValues();

Extract the Required Records

Now that we’ve got our interested data in a 2-D array named “data”, we can loop over them and take action:

for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    //console.log(row[3]);
    var today = new Date(), // today's date
    exp_date = row[5] // exp date

    var cert_details = 
        {
          domain_name:row[0],
          cert_provider_name:row[1],
          account: row[2],
          cert_manager:row[3],
          cert_manager_email:row[4]
        };

    //Remove the time part from the date
    var t2 = new Date(exp_date);
    t2.setHours(0,0,0,0);
    var t1 = new Date(today);
    t1.setHours(0,0,0,0);

    //Calculate the ms difference between two date
    var difference_ms = Math.abs(t2.getTime() - t1.getTime());
    // 24*3600*1000 is milliseconds in a day
    var days_left = Math.round(difference_ms/(24*3600*1000));
    
    //Put the days_left to cert_details array
    cert_details.days_left = days_left;
    
    if (days_left == 3) {
      console.log(cert_details.domain_name+" expired in 3 days");
      sendEmail(cert_details);
    }
    else if (days_left == 7) {
      console.log(cert_details.domain_name+" expired in 7 days");
      sendEmail(cert_details);
    }
    else if (days_left == 30) {
      console.log(cert_details.domain_name+" expired in 30 days");
      sendEmail(cert_details);
    }
 }

In the above code snippet, go through every valid row of the 2-D array and hold the spreadsheet cell data in separate variables. Then I’m getting the current date and the certificate expiration date then remove its time by the setHours(0,0,0,0) function. Convert the two dates into milliseconds values and get the time difference between them. Finally, again convert into the days and it will return the number of days to the cert expiration date.

I’m comparing days_left to 3, 7, 30 which means that we’ll get reminder emails exactly 3 days, 1 week, and 1 month before the certificate is expired. You could change this to whatever you wish.

Sending Expiration Reminders via Emails

Create Email Template

Before writing the function to send emails, you need to prepare an email template. Here I’m using a new HTML file to prepare the email template. You can create a new file by clicking + in front of the File.

Create sendEmail Function

Finally, it’s time to notify the intended parties via email. Hence I have created a function called “sendEmail” and inside it, I’m calling a previously created template and passing some parameters required to generate an email template. The email sending part is pretty straightforward and Google really makes it simple to do using the MailApp.sendEmail() function.

function sendEmail(cert_details){
  //Get the html email template
  var templ = HtmlService.createTemplateFromFile('EmailTemplate');
  templ.cert_details = cert_details;
  
  var message = templ.evaluate().getContent();
  
  MailApp.sendEmail({
    to: cert_details.cert_manager_email,
    subject: "Your "+cert_details.cert_provider_name+" certificate expires in " + cert_details.days_left + " days",
    htmlBody: message
  });
}

You can find the final script, HTML email template, and sample Google Spreadsheet from the following GitHub link.

Get it from GitHub

Running the Script Manually

Once all the above steps are done, now it’s time to run the script (you can find the Run button on the script editor). When you run the script initially, this script required some permissions to allow from your Google Account. So then you have to allow it all to work successfully.

After successful script execution, you will get an email to the email addresses mentioned in the “E” column of the Google Spreadsheet according to the certificate expiration date as follows.

Scheduling the Script to Autorun

Here I will show you how to automatically run this script every day or every week or whatever often you’d like using Google Apps Script Triggers.

Google Apps Script has a built-in feature to automatically run the tasks with time-driven triggers. You can configure to run any one of your functions in your script by minute, hour, day, week, or month. Or else can be set to run on a specific date.

From the left menu, click Triggers. A new window will open. Click the blue +Add Trigger button in the bottom right corner.

Then a modal will pop up that allows you to configure the trigger. There are a few options you need to choose from based on how / when you’d like your script to run. According to this example, my requirement is to run the script every morning. After that click Save and your script will trigger based on the preferences you choose.

I hope this was a helpful article to manage all your SSL/TLS certificate expiration reminders to relevant admins. If you have any questions or a way to optimize this solution feel free to leave a comment below.