Search in:

Software tracking using Google Sheets & Script

Social Media

If you have many licensed pieces of software that either renew or expire on an annual basis, it can be a nightmare to keep track of what & when, I’ve got around this problems by using Google Sheets and Google Script to email me when a license will expire within 30 days and also if it’s expired, and it’s keep nagging you until you do something about it

So first things first you need a tracking sheet, feel free to use this one Google Sheet

then you need the Google Script to do the magic

// Example of sending emails from google sheet if cell meets a certain criteria
// Written by Rich Easton.

function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 50; // Number of rows to process, could be coded to read the last row
var dataRange = sheet.getRange(startRow, 1, numRows, 15); //get columns 0 to 15
var data = dataRange.getValues();

for (var i = 0; i < data.length; ++i) { var row = data[i]; var istatus = row[8]; //reads the status of the license var emailAddress = row[11]; // reads owners email address from register var uname = row[10]; //reads owners name var ititle = row[0]; //read title of software var idays = row[7]; //reads the number of days before expiry if (istatus != "Active" && istatus != "Unknown") { //if is not unknown or active process if (istatus === "Expired") { // if is expired use this var subject = "Item: " + ititle + " on the license register needs your attention"; var message = "Dear " + uname + ",\n\n" + ititle + " in the license Register has " + istatus + "\n\nRegards \n\nLicense Register."; } if (istatus === "Renewal Pending") { // if is Renewal Pending use this var subject = "Item: " + ititle + " on the license register needs your attention"; var message = "Dear " + uname + ",\n\n" + ititle + " in the license Register has " + idays + " before it expires, Please contact the vendor to renew the license\n\nRegards \n\nLicense Register."; } var $ccmail = "Your email here"; MailApp.sendEmail(emailAddress, subject, message, {bcc: $ccmail}); //can send BCC, CC seperate with a "," sheet.getRange(startRow + i, 15).setValue("EMAIL_SENT"); //appends EMAIL_SENT in alert // Make sure the cell is updated right away in case the script is interrupted SpreadsheetApp.flush(); } } ++i; // increase row count by 1 }

then set a trigger to run every Monday at 8am, and it’ll email you when renewals are pending, job done.

Leave a Reply

Your email address will not be published. Required fields are marked *