Google sheets- email based on date comparison for range of dates-CodePudding

I have two columns on my Google Sheet [Reminder] and [Reminder Date]. Reminder contains an email address to send a reminder to and the email should send out if Reminder Date is Today.

I've figured out the part of the code to send an email, that is not the problem. Here is what I have so far

var ss = SpreadsheetApp.getActive();
ss.setActiveSheet(spreadsheet.getSheetByName('Projects'), true);
var datesrg=ss.getRangeByName("Reminder Date");
var emailrg=ss.getRangeByName("Reminder");
var rdates=datesrg.getValues();
var remail=emailrg.getValues();

//in the for loop
  var message = {
    to: //Email,
    subject: "Project Reminder",
    body: "Hello, \n\nYou are receiving this email because you set a reminder for this project today.\n\nThank you,\n",
    name: "Bot",
  MailApp.sendEmail(message);

What I have in mind is a for loop that compares each date in rdates to today, and if it matches, gets the index value from rdates, fetches the corresponding index value for remail and then sets that as the email address for the email to be sent out to.

I'm struggling to use forEach and how to set the date to today for comparison. Help would be much appreciated.

CodePudding user response：

Assuming that datesrg has two columns, try this:

var dates = datesrg.getValues();
dates.forEach( row => {
   if(row[0].getTime() === row[1].getTime()){
     // put here what should be done when the condition is true
   }
});

Compare two dates with JavaScript

CodePudding user response：

It wasn't clear to me what you were attempting to do with the name. But here's the loop for comparing dates with todays date

function myfunction() {
  var ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Projects")
  var datesrg = ss.getRangeByName("Reminder Date");
  var emailrg = ss.getRangeByName("Reminder");
  var rdates = datesrg.getValues().flat();
  var remail = emailrg.getValues().flat();
  const dt = new Date();
  const tdv = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate()).valueOf();
  rdates.forEach((d, i) => {
    if (new Date(e).valueOf() >= tdv) {
      MailApp.sendEmail(remail[i], "Project Reminder", "Hello, \n\nYou are receiving this email because you set a reminder for this project today.\n\nThank you,\n")
    };
  });
}