Comparing Dates on Google Sheets with Google Apps Scripts is Returning Inaccurate Boolean Values

Question

My Google Sheets spreadsheet has a list of appointments that I am trying to compare with tomorrow's date (so that I can send a reminder the day before). My current code is returning true on certain dates that aren't on the spreadsheet. We have no appointments on Sundays and I am currently testing this on a Saturday but still receiving some true values. I searched the spreadsheet thoroughly for a possible booking mistake but there are none. I did notice, however, that when it looks for the date 4:24:2016 (which is tomorrow) it will return true on 4:14:2016 dates. I am pretty much stumped at this point.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var apptDates = sheet.getRange(2, 32, sheet.getLastRow() - 1, 1).getValues();

  for (var i = 0; i <= apptDates.length; i++) {
    var apptDate = new Date(apptDates[i][0]);
    apptDate = apptDate.addHours(74);

    function dayAway(date, day) {
      return new Date(date.getTime() + day * (24 * 3600 * 1000));
    }
    Logger.log(apptDate >= dayAway(new Date(), 1));
  }
}

Try taking out the utilities.formatDate part. Creating a Date obj with the cell value should be sufficient enough to compare dates. — Gerneio
@Gerneio I removed the utilities.formatDate but now it is continuously returning false. When I Logged the results of both dates, each date has a contradicting timestamp so that was why I was trying to format it without times. Is there a way to compare dates without having to compare the times as well? — Taylor Page
When I log dayAway(): [16-04-23 10:18:09:841 PDT] Tue Apr 26 12:18:09 GMT-05:00 2016 When I log apptDate : [16-04-23 10:18:09:842 PDT] Tue Apr 26 00:00:00 GMT-05:00 2016 — Taylor Page
You changed it to: var apptDate = new Date(apptDates[i][0]); Right? — Gerneio

Serge insas Serge insas · Accepted Answer · 2016-04-23T22:22:34

When you use new Date() you get a full JS Date object with not only day, month and year but also hours minutes, seconds and milliseconds.

That's why your comparison could not give results. Simply set the unneeded values to 0 and it will work. I used getTime() to compare milliseconds but could have used toString() as well, only the date objects are not suitable to check equality.

The Logger is a great help to debug such things.

code :

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var apptDates = sheet.getRange(2,6, sheet.getLastRow() - 1, 1).getValues();
  for (var i = 0; i < apptDates.length; i++) {
    var apptDate = new Date(apptDates[i][0]);
    Logger.log(apptDate.getTime()+'  ==?  '+dayAway(new Date(new Date().setHours(0,0,0,0)), 1).getTime());
    Logger.log(apptDate.getTime() == dayAway(new Date(new Date().setHours(0,0,0,0)), 1).getTime());
  }
}

function dayAway(date, day) {
  return new Date(date.getTime() + day * 24 * 3600 * 1000);
}

Comparing Dates on Google Sheets with Google Apps Scripts is Returning Inaccurate Boolean Values

1 Answers