Reply to thread

Message: <blockquote data-quote="NEMISIS" data-source="post: 30572452" data-attributes="member: 494668">Create a google sheet with your data.Open the sheetthen go to Extensions > Apps ScriptThis will open your app script project, give it a name, and you need to edit the script, as per your logicHere is my sample sheet structure[ATTACH=full]247419[/ATTACH]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 = "recipient1@example.com, recipient2@example.com"; // Main recipients (separated by commas)  const CC_RECIPIENTS = "cc1@example.com, cc2@example.com"; // 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    });  }}[/CODE]Then you can simply save and run the script to test.Next step is to setup the time trigger, to do thatClick the clock icon in the left pane, in your script project,Then click triggers, add trigger (Bottom right corner button)[ATTACH=full]247420[/ATTACH]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,[ATTACH=full]247421[/ATTACH]Script is configurable, you can simply configure the column which has your expiration date.</blockquote>

Verification: Dawasata paya keeyak thibeda?

Top Bottom