Create a google sheet with your data.
Open the sheet
then go to Extensions > Apps Script
This will open your app script project, give it a name, and you need to edit the script, as per your logic
Here is my sample sheet structure
This is the script (Generated via ChatGPT, didn't test the script in detail), you have to update the script based on your need. If you need some help with the script, you can ask, me or someone else can help.
Code:
function checkExpiringDates() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Change to your sheet name
const LAST_ROW = sheet.getLastRow();
const EXPIRATION_COLUMN = 3; // Column C (Expiration Date)
const NAME_COLUMN = 1; // Column A (Name)
const DAYS_BEFORE_EXPIRATION = 90; // 3 months = 90 days
const RECIPIENTS = "[email protected], [email protected]"; // Main recipients (separated by commas)
const CC_RECIPIENTS = "[email protected], [email protected]"; // CC recipients (optional)
const data = sheet.getRange(2, 1, LAST_ROW - 1, 3).getValues(); // Get all rows except headers
const today = new Date();
const futureDate = new Date();
futureDate.setDate(today.getDate() + DAYS_BEFORE_EXPIRATION);
let expiringItems = [];
for (let i = 0; i < data.length; i++) {
let name = data[i][NAME_COLUMN - 1]; // Name from Column A
let expDate = data[i][EXPIRATION_COLUMN - 1]; // Expiration date from Column C
if (expDate instanceof Date) { // Ensure it's a valid date
if (expDate >= today && expDate <= futureDate) {
expiringItems.push(`- ${name} (Expiring on: ${expDate.toDateString()})`);
}
}
}
if (expiringItems.length > 0) {
let subject = "Expiration Alert - Google Sheets";
let body = `The following items are expiring within the next 3 months:\n\n${expiringItems.join("\n")}`;
// Send email with multiple recipients and CC
MailApp.sendEmail({
to: RECIPIENTS,
cc: CC_RECIPIENTS,
subject: subject,
body: body
});
}
}
Then you can simply save and run the script to test.
Next step is to setup the time trigger, to do that
Click the clock icon in the left pane, in your script project,
Then click triggers, add trigger (Bottom right corner button)
You have to update the script to add your emails, You can also pick email from a column too.
When you are running this for the first time, it will warn you saying this is not approved app etc, you will have to trust the project and go ahed.
Also the email sender would be the one who owns the google sheet, You may create a new google account for this automation.
Keep in mind, there are some limitations,
Script is configurable, you can simply configure the column which has your expiration date.
