Send email notifications based on the expiry dates entered in a column of Google Sheets

[jghmf]

We use Sheets for assets that have expiry dates. Is it possible to send email notifications to a user "x" days before the expiry date of each asset? All the expiry dates will in under column D in the test sheet below. 

https://docs.google.com/spreadsheets/d/1ea-cTC16C4UBdGTCchwEG9iY1qM48kv2kfY0XJ7_iqQ/edit?usp=sharing

[jghmf · Accepted solution]

This is resolved. Gemini can create a script by linking Google Sheets with Apps Script.  A sample below.
Sheets > Extensions > Apps Script and copy the below lines or Gemini's suggested script. Then set Triggers in the Apps Script, for e.g. first day of every month to check the sheet and trigger reminder emails based on the dates on column B.

function sendReminders() {

  const sheet = SpreadsheetApp.getActiveSheet();

  const range = sheet.getRange("A2:B"); // Adjust the range to your needs

  const values = range.getValues();

  for (let i = 0; i < values.length; i++) {

    const dateCell = values[i][1]; // Assuming date is in column B

    const date = new Date(dateCell);

    if (date.getTime() <= Date.now()) {

      const emailAddress = "email@gmail.com"; // Replace with your email

      const subject = "Reminder " + values[i][0]; // Assuming task is in column A

      const body = "This is a reminder for an item expiring soon in the Google Sheets file name  for " + values[i][0];

      GmailApp.sendEmail(emailAddress, subject, body);

    }

  }

}

[Sandeep_mehta]

To send email notifications for asset expiry dates in Google Sheets:

1. Open Google Sheets: Navigate to your sheet with expiry dates in column D.

2. Go to Apps Script:

Click Extensions > Apps Script.

3. Paste Script:

function sendReminders() {

    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

    const range = sheet.getRange("D2:D"); // Adjust range if needed

    const values = range.getValues();

    const today = new Date();

    for (let i = 0; i < values.length; i++) {

        const expiryDate = new Date(values[i][0]);

        const email = "email@gmail.com"; // Replace with recipient's email

        if (!isNaN(expiryDate) && (expiryDate - today) / (1000 * 60 * 60 * 24) <= 7) { // 7 days before expiry

            const subject = `Reminder: Asset Expiring Soon`;

            const body = `An asset is expiring on ${expiryDate.toDateString()}. Please take action.`;

            GmailApp.sendEmail(email, subject, body);

        }

    }

}

4. Set a Trigger:

In Apps Script, go to Triggers > Add Trigger.

Select sendReminders to run daily or as per your schedule.

This script checks for expiry dates and sends an email reminder 7 days before. Adjust the e

mail address, range, or days as needed.