AutoSheet Solutions

Managing expiry dates for various items can be challenging, especially if you have numerous items to track. Google Apps Script offers a convenient way to automate this task by sending email notifications when items are nearing their expiry date or have already expired. In this post, I'll show you how to set up a script to track expiry dates and send stylish email alerts.Copy the AppScript code below:

First, set up a Google Sheet with the following structure:

Column A: Item Name Column B: Expiry Date Column C: Approaching Expiry Threshold (in days)

Here's an example of how your sheet might look:

Item Name	Expiry Date	Threshold
Vehicle Insurance	28/07/2024	3
Pollution Certificate	26/07/2024	2
VitaminD	29/07/202	5

Name this sheet Sheet1.

Next, use the following Google Apps Script to check expiry dates and send email notifications:

To run the script, open the Script Editor in Google Sheets (Extensions > Apps Script), paste the script, and save it. You can set up a time-driven trigger to run this script daily or at a frequency that suits your needs (Triggers > Add trigger).

function checkExpiryviahtmlmailWithLink() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var dataRange = sheet.getRange('A2:C' + sheet.getLastRow()).getValues();
  var currentDate = new Date();
  currentDate.setHours(0, 0, 0, 0);
  
  var expiredItems = [];
  var approachingExpiryItems = [];
  
  dataRange.forEach(function(row) {
    var itemName = row[0];
    var expiryDate = new Date(row[1]);
    expiryDate.setHours(0, 0, 0, 0);
    var daysUntilExpiry = Math.ceil((expiryDate - currentDate) / (1000 * 60 * 60 * 24));
    
    var approachingExpiryThreshold = row[2];
    
    if (itemName && daysUntilExpiry <= 0) {
      expiredItems.push({name: itemName, days: daysUntilExpiry});
    } else if (itemName && daysUntilExpiry <= approachingExpiryThreshold) {
      approachingExpiryItems.push({name: itemName, days: daysUntilExpiry});
    }
  });
  
  var subject = "Expiry Alert";
  var sheetUrl = ss.getUrl();
  var htmlMessage = "<html><body style='font-family: Arial, sans-serif;'>";
  
  if (expiredItems.length > 0) {
    htmlMessage += "<h2 style='color: #FF0000;'>The following items have expired:</h2>";
    htmlMessage += createTable(expiredItems, true);
  }
  
  if (approachingExpiryItems.length > 0) {
    htmlMessage += "<h2 style='color: #FF4500;'>The following items are approaching expiry:</h2>";
    htmlMessage += createTable(approachingExpiryItems, false);
  }
  
  htmlMessage += "<p>For more details, visit the <a href='" + sheetUrl + "' style='color: #1E90FF;'>Google Sheet</a>.</p>";
  htmlMessage += "</body></html>";
  
  if (expiredItems.length > 0 || approachingExpiryItems.length > 0) {
    MailApp.sendEmail({
      to: Session.getActiveUser().getEmail(),
      subject: subject,
      htmlBody: htmlMessage
    });
  }
}

function createTable(items, isExpired) {
  var tableHtml = "<table style='border-collapse: collapse; width: 100%;'>";
  tableHtml += "<tr style='background-color: #4CAF50; color: #FFFFFF;'><th style='border: 1px solid #ddd; padding: 8px;'>Item Name</th><th style='border: 1px solid #ddd; padding: 8px;'>Days</th></tr>";
  
  items.forEach(function(item, index) {
    var rowColor = index % 2 === 0 ? "#f2f2f2" : "#ffffff"; // Alternating row colors
    var daysColor = isExpired ? "#FF0000" : (item.days <= 3 ? "#FF4500" : "#FFD700"); // Color coding for days (expired: red, approaching: orange, other: golden)
    tableHtml += "<tr style='background-color: " + rowColor + ";'>";
    tableHtml += "<td style='border: 1px solid #ddd; padding: 8px; font-family: Arial, sans-serif; font-size: 14px;'>" + item.name + "</td>";
    tableHtml += "<td style='border: 1px solid #ddd; padding: 8px; font-family: Arial, sans-serif; font-size: 14px; color: " + daysColor + ";'>" + item.days + "</td>";
    tableHtml += "</tr>";
  });
  
  tableHtml += "</table>";
  return tableHtml;
}

The message will look like this

The following items have expired:

Item Name	Days
Vehicle Insurance	0
pollutionCertificate	-2

The following items are approaching expiry:

Item Name	Days
vitaminD	1

AutoSheet Solutions

Monday, February 24, 2025

How to get Automatic Expiry Alert

The following items have expired:

The following items are approaching expiry:

No comments:

Post a Comment