How to compare dates with day, month and year only-CodePudding

I'm trying to go over a column that has some random dates and find todays date then save that row in a variable. I honestly wrote the code to do that, but for some reason its not working with which I don't know why.

This is my code.

 const ss = SpreadsheetApp.openByUrl(THIS).getSheetByName("Daily PO Dashboard");
  let birthday = new Date(2022, 1, 21,3,00,00)    
  for(var row=0; row<data.length; row  ){
  if(data[row][16]==birthday){
    Logger.log(" I'm in!")
      ss.getRange('B3:AA').setValue(data[row]);
  }
  }
  var checkBox = ss.getRange('A3:A').insertCheckboxes();

I also tried to set the birthday as

var birthday = new Date();

but nothing is working with me.

This is what I'm comparing with.

[ this is column 16 and each row is row

Note: it never goes in the if statement

this is data[row][16] when I logger.log it.

Note: there are more but they are all either dates or null

CodePudding user response：

Using strings and Utilities.formatDate() seems kind of brute force to me. If I have to compare dates I remove the time and then use valueOf() to compare dates.

function dummy() {
  try {
    var d1 = new Date();
    Utilities.sleep(100);
    var d2 = new Date();
    console.log(d1);
    console.log(d2);  // Notice in console log they look alike but are different by 100 msec.
    console.log( d1.valueOf() === d2.valueOf() );
    function removeTime(date) {
      return new Date(date.getFullYear(),date.getMonth(),date.getDate());
    }
    d1 = removeTime(d1);
    d2 = removeTime(d2);
    console.log(d1);
    console.log(d2);
    console.log( d1.valueOf() === d2.valueOf() );
  }
  catch(err) {
    console.log(err);
  }
}

2:46:07 PM  Notice  Execution started
2:46:07 PM  Info    Mon Feb 21 2022 17:46:07 GMT-0500 (Eastern Standard Time)
2:46:07 PM  Info    Mon Feb 21 2022 17:46:07 GMT-0500 (Eastern Standard Time)
2:46:07 PM  Info    false
2:46:07 PM  Info    Mon Feb 21 2022 00:00:00 GMT-0500 (Eastern Standard Time)
2:46:07 PM  Info    Mon Feb 21 2022 00:00:00 GMT-0500 (Eastern Standard Time)
2:46:07 PM  Info    true
2:46:07 PM  Notice  Execution completed

CodePudding user response：

Explanation:

Just transform everything into string so you can be sure that the comparison is correct, otherwise you are comparing exact date objects which can be very tricky.

Transform both the birthday date object and data[row][16] to a type of string with the exact same format:

birthday = Utilities.formatDate(new Date(),
                                SpreadsheetApp.getActive().getSpreadsheetTimeZone(), 
                                "m/dd/yyyy");
console.log(birthday) // birthday is a string

and

data_temp = Utilities.formatDate(data[row][16],
                              SpreadsheetApp.getActive().getSpreadsheetTimeZone(),
                              "m/dd/yyyy");

and now you can compare them as strings:

if(data_temp==birthday) {
  // rest of the code here
}

Solution:

const ss = SpreadsheetApp.openByUrl(THIS).getSheetByName("Daily PO Dashboard");
const ssTime = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
const birthday = Utilities.formatDate(new Date(),ssTime,"m/dd/yyyy");
console.log(birthday) // birthday is a string

for(var row=0; row<data.length; row  ){
    let data_temp = Utilities.formatDate(data[row][16],ssTime,"m/dd/yyyy");
    if(data_temp==birthday){
        Logger.log(" I'm in!")
        ss.getRange('B3:AA').setValue(data[row]);
  }
  }
var checkBox = ss.getRange('A3:A').insertCheckboxes();

This approach assumes that the value you get from the spreadsheet file is a date object which it should be if you use the date picker functionality.