Home Tips & Tricks Set Up Expiration Reminders using Google Sheets

Set Up Expiration Reminders using Google Sheets

by Lakindu Jayasena
3.1k views 6 mins read
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 a Google Sheet 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 that you plan to store your reminders and your script. As an example, my spreadsheet looks like follows.

Sample Google Spreadsheet

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.

Google App Script Editor

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 the “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 the remove its time by 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 the 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 HTML Email Template 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 pass 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.

Google App Verification
Google Account Authorization

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

Expiration Reminder Email

Scheduling the Script to Autorun

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

Google Apps Script having 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 the minute, hour, day, week, or month. Or else can be set it to run on a specific date.

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

Apps Script Triggers

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.

Trigger Options

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

Related Articles

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.