-2

public sheet: https://docs.google.com/spreadsheets/d/1pXrUkA-fY1hw3bie4Bv1mamkmzics9dfX59B12spuPI/edit?usp=sharing

Issue: I have two dates that look equivalent but apps script is saying they aren't. I have a sheet with dates. I cycle through the dates to see if one date is equivalent to the previous date. The script (starting on the second row) should return true, false, true. But instead it returns false, false, false. Any thoughts about why this happens this way?

9/8/2020
9/8/2020 // should be true, returns false
9/15/2020 // should be false, ok
9/15/2020 // should be true, returns false

Code snippets

  //test code
  for (var r=10;r<=14;r++){
  for (var c=2;c<=4;c++){
    Logger.log(sh.getRange(r,c).getValue());
    Logger.log(sh.getRange(r-1,c).getValue());
    Logger.log(sh.getRange(r,c).getValue() == sh.getRange(r-1,c).getValue());
  }}

Logger response (shows dates are objects but equivalent dates aren't...):
[20-10-25 17:05:30:975 EDT] Tue Sep 08 00:00:00 GMT-04:00 2020
[20-10-25 17:05:30:976 EDT] object
[20-10-25 17:05:30:977 EDT] Tue Sep 15 00:00:00 GMT-04:00 2020
[20-10-25 17:05:30:979 EDT] object
[20-10-25 17:05:30:980 EDT] false
[20-10-25 17:05:30:981 EDT] false
[20-10-25 17:05:30:982 EDT] Tue Sep 15 00:00:00 GMT-04:00 2020
[20-10-25 17:05:30:984 EDT] object
[20-10-25 17:05:30:986 EDT] Tue Sep 15 00:00:00 GMT-04:00 2020
[20-10-25 17:05:30:987 EDT] object
[20-10-25 17:05:30:989 EDT] false
[20-10-25 17:05:30:990 EDT] false
Jason Torpy
  • 39
  • 2
  • 9
  • 1
    You can numerically compare Date() values from getTime() or valueOf() methods. [ref](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date) – Cooper Oct 25 '20 at 21:07
  • that's a good idea, but I can't afford to ping the range every time. I have to manipulate from the array. – Jason Torpy Oct 25 '20 at 21:28
  • 1
    Instead of using getValue() use `const vA= getValues()` and then you can do something like `const dt=new Date();if(Date(vA[r][c]).valueOf()==dt.value())` – Cooper Oct 25 '20 at 21:35
  • 2
    and if you're only interested in dates and not times then you'll want to do something like `const date=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate())` which is like getting the value for midnight – Cooper Oct 25 '20 at 21:37
  • 1
    @JasonTorpy why don't you simply change the format of column B to have two matching formats and then use getDisplayValues as I mention in my answer? – Marios Oct 25 '20 at 21:44
  • 1
    We already have several similar questions that were already answered, like [Compare two dates Google apps script](https://stackoverflow.com/q/11174385/1595451) – Rubén Oct 25 '20 at 21:50
  • @Marios I do appreciate that answer, I think you deleted it. I wasn't aware of .getDisplayValue. That's nice. But I'm doing array processing so I really need this to work in an array. it sounds like data[r-1][c] would work with .getDisplayValue but I don't understand how if it's in an array and not displaying anywhere... Could you explain more like why it's not working with getValue when the actual values are the same, and how this would work in an array? – Jason Torpy Oct 25 '20 at 22:09
  • @Rubén thanks for that. there are lot of datetime answers, but I couldn't narrow down which one might be helpful. your reference is useful. I'll dig into that to see if I can help. the issue is also that I can't really spend processing time on calling the sheet (Range class) for each operation. – Jason Torpy Oct 25 '20 at 22:11
  • it looks like adding .toISOString() to the array node value (array[0][0].toISOString()) will work. It's just frustrating because I have a large array, only some of which are dates, so I'll have to check and do additional processing if it's a date. It's a bit frustrating... – Jason Torpy Oct 25 '20 at 22:27

1 Answers1

2

@Ruben referenced a good answer about datetime processing date pulls in an object, so it doesn't necessarily compare the values but rather the whole object. I'm getting correct results by pulling out the isostring for comparison:

array[0][1].toISOString()
Jason Torpy
  • 39
  • 2
  • 9
  • Again I would use display values to have a better feeling what you are actually comparing. See something in the screen and you are sure you are fetching the exact same string. – Marios Oct 25 '20 at 22:59