CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Rus Paul Adrian
Rus Paul Adrian

Reputation: 53

How do I compare Times in Google Apps Script

I am trying to create a filter on a list based on a given time interval (2 cells in my spreadsheet which will input a timestamp C4 and C5). I have scoured the internet for a while and found out that the Javascript code used in Google Apps Script is different from the usual Javascript, so I haven't found a usable code snippet for my case. The code is something like this:

var beginningTimeValue = new Date('2020-01-01 ' + ss.getRange("C4").getValue());
var endingTimeValue = new Date('2020-01-01 ' + ss.getRange("C5").getValue());
if(!beginningTimeValue == ""){
    Logger.log(beginningDateValue);
    unique = unique.filter(function(row)
                              {
                                const bTime = Date.parse(row[4]);
                                Logger.log(bTime);
                                Logger.log(beginningTimeValue);
                                return bTime.getTime() >= beginningTimeValue.getTime();
                              }
                             );
  }

The value in row[4] is of DateTime value ("12/01/2021 00:03:35" for example). How do I filter this row out if I want the time to be between 08:00:00 and 13:00:00?

Upvotes: 0

Views: 1212

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

Three points:

  • To filter by two conditions instead of one, simply combine the two conditions with an && operator.

So:

return bTime.getTime() >= beginningTimeValue.getTime(); && bTime.getTime() <= endingTimeValue.getTime()

instead of

return bTime.getTime() >= beginningTimeValue.getTime();

const bTime = Date.parse(row[4]);

already returns you a timestamp in ms, if you try to apply

bTime.getTime()

on it - this will result in an error.

  • Be careful with

Logger.log(beginningDateValue);

given that your variable is called beginningTimeValue and not beginningDateValue.

Sample

Provided that beginningTimeValue, endingTimeValue and unique look like the harcoded values below, the following code snippet will work correctly for you:

function myFunction() {
  var beginningTimeValue = new Date('2020-01-01 08:00:00');
  var endingTimeValue = new Date('2020-01-01 13:00:00');
  var unique = [["value","value","value","value","12/01/2021 00:03:35","value"],["value","value","value","value","01/01/2020 00:03:35","value"], ["value","value","value","value","01/01/2020 08:03:35","value"], ["value","value","value","value","01/01/2020 13:03:35","value"]]
  if(!beginningTimeValue == ""){
    Logger.log(beginningTimeValue);
    unique = unique.filter(function(row)
                           {
                             const bTime = Date.parse(row[4]);
                             Logger.log(bTime);
                             Logger.log(beginningTimeValue);
                             return bTime >= beginningTimeValue.getTime() && bTime <= endingTimeValue.getTime();
                           }
                          );
    console.log("unique: " + unique)
  }
}

UPDATE

  • If you want to compare the times only (not the dates), you need to hardcode the date of row[4] to the same value like in beginningTimeValue and endingTimeValue. For this you can use the methods setDate(), setMonth and setYear.

  • Also, if your code should only work base don either vlaues are provided by a user in the cells C4 and C5 - you should adapt your code accordingly.

  • Be careful with your conditional statements: Even if ss.getRange("C4").getValue() is an empty string or an invalid input - beginningTimeValue will still not be an empty string, but rather the beginning of Unix time.

Sample:

function myFunction() {
var beginningTimeValue = new Date('2020-01-01 ' + ss.getRange("C4").getValue());
console.log("beginningTimeValue: " + beginningTimeValue)
var endingTimeValue = new Date('2020-01-01 ' + ss.getRange("C5").getValue());
console.log("endingTimeValue: " + endingTimeValue)
  var unique = [["value","value","value","value","12/01/2021 00:03:35","value"],["value","value","value","value","01/01/2020 00:03:35","value"], ["value","value","value","value","01/01/2020 08:03:35","value"], ["value","value","value","value","01/01/2020 13:03:35","value"]]
  if(!ss.getRange("C4").getValue() == ""){
    Logger.log("beginningTimeValue: " + beginningTimeValue);
    unique = unique.filter(function(row)
                           {
                             const bTime = new Date(row[4]);
                             bTime.setYear(2020);
                             // Be careful - months start in Javascript with 0!
                             bTime.setMonth(0);
                             bTime.setDate(1);
                             Logger.log(bTime);
                             if(ss.getRange("C5").getValue() != ""){
                               return bTime >= beginningTimeValue.getTime() && bTime <= endingTimeValue.getTime();
                             }
                             else{
                               return bTime >= beginningTimeValue.getTime();
                             }
                           }
                          );
    console.log("unique: " + unique)
  }
}
  • Keep im mind that Stackoverflow is not there to provide you a complete solution, but rather to help you troubleshooting and provide you references and samples that will guide you in the right direction. This will allow to incorporate the necessary modification into your code. You still need to have some basic understanding about coding to apply the sample to your script correctly.

Upvotes: 1

Related Questions