1

I'm trying to write a script where the dates in a column are compared against todays date, and if the dates in the column match, an email is sent.

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rotDate = sheet.getRange("F6").getValue();
  var today = sheet.getRange("F1").getValue();
  //var today = new Date().toLocaleDateString();  // Today's date, without time
  var dumpCell = sheet.getRange("J3");
  var dumpCell2 = sheet.getRange("J4");
  var dumpCell3 = sheet.getRange("J5");
  if(rotDate==today) {
    //dumpCell is there to dump a value in a cell if the IF statement is true
    dumpCell.setValue(rotDate);
    MailApp.sendEmail("this is where my email would go", "subject", "body");
    }
  //these dump the compared vars into cells so they can be checked against one another manually
  dumpCell2.setValue(rotDate)
  dumpCell3.setValue(today)
}

This is as far as I've gotten. The Values in F6 and F1 are identical, I've typed them out, retyped them, copied and pasted, etc. But for some reason, my if statement just won't run. It behaves as if the two values are different, and I can't work out why.

If I change var rotDate and var today to matching strings, eg "123" then it seems to work as expected.

enter image description here

This is a screenshot of my test data sheet. There are other columns there with other data which were meant to be used for more testing, but I didn't get that far.

Does anyone know what I might be doing wrong?

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Agent
  • 123
  • 2
  • 9
  • 4
    Possible duplicate of [Compare two dates with JavaScript](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) – JJJ Apr 15 '18 at 10:15
  • 1
    Assuming the code gets as far as the condition (i.e. there's no error before then), then, one way or another, the derived values are not the same. Possibly they're coming out in different formats due to different cell formats (e.g. text vs. date or something). First interrogate both of them in the console, and also check their `typeof`. Also ask the console if `rotDate == today` and see if it goes true or false. – Mitya Apr 15 '18 at 10:17
  • 1
    @Utkanos it should be apparent (especially after viewing the linked question) that performing an object equality test between two different objects will always be false. (Also, Google Apps Script has no interactive `console` like the browser) – tehhowch Apr 15 '18 at 20:05
  • I'd read the question in haste (and commented similarly) and didn't realise this was object comparison - seemed to be strings. – Mitya Apr 16 '18 at 09:53

1 Answers1

0

After trying a variety of approaches, I cracked it using a code snippet from Jon Lin's answer here: Compare two dates Google apps script

After realizing that the fault was with trying to compare two dates (either a date in an adjacent cell, or a procedurally generated date whenever the function is run, I knew I had to do some better formatting with the data I was intending to compare. This is my repaired code that now works as expected:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rotDate = sheet.getRange("F6").getValues();
  var today = new Date();
  //sample values
  var sYyyy = Utilities.formatDate(new Date(rotDate), "GMT+8","yyyy");
  var sMm = Utilities.formatDate(new Date(rotDate), "GMT+8","MM");
  var sDd = Utilities.formatDate(new Date(rotDate), "GMT+8","dd");
  //Test Values
  var tYyyy = Utilities.formatDate(new Date(today), "GMT+8","yyyy");
  var tMm = Utilities.formatDate(new Date(today), "GMT+8","MM");
  var tDd = Utilities.formatDate(new Date(today), "GMT+8","dd");  
  //var rotDate = sheet.getRange("F6").getValue();
  //var today = sheet.getRange("F1").getValue();
  //var today = new Date().toLocaleDateString();  // Today's date, without time
  var dumpCell = sheet.getRange("J3");
  var dumpCell2 = sheet.getRange("J4");
  var dumpCell3 = sheet.getRange("J5");
  if (sYyyy + sMm + sDd == tYyyy + tMm + tDd) {
  //if(rotDate===today) {
    //dumpCell is there to dump a value in a cell if the IF statement is true
    dumpCell.setValue(rotDate);
    MailApp.sendEmail("tv18766@gmail.com", "subject", "body");
    }
  //these dump the compared vars into cells so they can be checked against one another manually
  dumpCell2.setValue(rotDate)
  dumpCell3.setValue(today)
}
Agent
  • 123
  • 2
  • 9