Google-sheets – Determining whether specified time is within 15mins of target time using Google Sheets scripting

google sheetsjavascript

I previously built this sheet in Excel in 2016 without scripting. I'm posting a similar question as I did back then, but this time I'm reconstructing the sheet in Google Sheets and automation is provided by script.

The sheet contains a cell formatted as a time duration and contains the actual time that a backup is supposed to be initiated (eg. 22:00). Another cell holds a "threshold" value formatted as a decimal (eg 15). This is the permitted deviation in minutes from the scheduled start time of the backup.

For the above example, a backup is executed in compliance if it is run between 21:45 and 22:15.

My script accepts a raw export (pasted into sheet called "RAW") from our backup software and processes it in order to identify the non-compliant. My problem is how to compute compliance.

// Now the Tape Label is existing in Master we import the start and end times from RAW sheet into Master sheet.

      // Get the 'Timeliness Threshold' from the Master sheet
      var tThresh = sM.getRange(1,14).getValue(); (15 mins)

      // Find the correct date (Start Time Cell) relative to the transaction date
      // The sheet is for a month and each date has columns dedicated to it for start time, end time, compliance (and more). The "Transaction Date" determines which date columnset is used (rather than the "start time").
      var dateStr  = Date.parse(sRVals[i][0]); // Array of raw data from the RAW sheet. This is all run inside a loop hence 'i'
      var date     = new Date(dateStr); // Converts from string into recognized 'Date'
      var TransDoM = date.getDate(); // Extracts the date (1-31) from the new date set above

      // Extract the actual Start Time from array of raw data from RAW sheet.
      var dateStr  = Date.parse(sRVals[i][3]); // Current format '9/1/2017 23:00:00'
      var date     = new Date(dateStr); // Converts from string into recognized 'Date'
      var stime    = date.getTime(); // Extracts the start time

      // Compute the T-comp (Timelines Compliance) - THIS IS THE PART THAT'S NOT WORKING
      // get Schedule
      var tSched       = sM.getRange(searchResult+4, 7).getValue(); // Reads the "Scheduled Time" from Master sheet using the row stored in searchResult.
      var tSched       = new Date(tSched); // Converts from string into recognized 'Date'
      var tCompEarly   = tSched-(tThresh/1440); // Deducts allowable threshold value from schedule to determine earliest time allowed to execute.
      var tCompLate    = tSched+(tThresh/1440); // As above but gets latest allowable time.

      if (stime >= tCompEarly && stime <= tCompLate) {
        var tComp = 1; // Compliant
      } else {
        var tComp = 0; // Not Compliant
      }

      Logger.log('tThresh = '+tThresh);
      Logger.log('tSched = '+tSched);
      Logger.log('tCompEarly = '+tCompEarly);
      Logger.log('tCompLate = '+tCompLate);
      Logger.log('stime = '+stime);
      Logger.log(searchTL+' Compliance = '+tComp);

Below is the result of the Logger.

[17-10-29 10:38:40:342 HKT] tThresh = 15
[17-10-29 10:38:40:344 HKT] tSched = Sat Dec 30 1899 21:54:17 GMT+0800 (HKT)
[17-10-29 10:38:40:345 HKT] tCompEarly = -2209111543000.0103
[17-10-29 10:38:40:345 HKT] tCompLate = Sat Dec 30 1899 21:54:17 GMT+0800 (HKT)0.010416666666666666
[17-10-29 10:38:40:345 HKT] stime = 1504278000000 // which is Fri, 03 Sep 49638 00:00:00 +0000
[17-10-29 10:38:40:346 HKT] System 514 Full FS (Daily) Compliance = 0

Desired result in the logger (based on input data)!

[17-10-29 10:38:40:342 HKT] tThresh = 15
[17-10-29 10:38:40:344 HKT] tSched = Fri Sep 01 2017 22:00:00 GMT+0800 (HKT)
[17-10-29 10:38:40:345 HKT] tCompEarly = Fri Sep 01 2017 21:45:00 GMT+0800 (HKT)
[17-10-29 10:38:40:345 HKT] tCompLate = Fri Sep 01 2017 22:15:00 GMT+0800 (HKT)
[17-10-29 10:38:40:345 HKT] stime = Fri Sep 01 2017 23:00:00 GMT+0800 (HKT)
[17-10-29 10:38:40:346 HKT] System 514 Full FS (Daily) Compliance = 0

I'm ok with Unix timestamps if they are correct. I can compute those but the long date format is easier to check in the logger.

I'm basically asking for help to correct the issues seen in the log.

Best Answer

var dateStr  = Date.parse(sRVals[i][3]); // Current format '9/1/2017 23:00:00'

This isn't an acceptable date time format for Google Apps Script, which is much stricter in parsing dates than most other JavaScript engines. For a string to be correctly parsed into a Date object, it must be like 2017-12-31T12:34:56 with optional microseconds .123, and optional Z to indicate UTC timezone (otherwise it's interpreted in the script's own timezone).

Three ways to solve this:

  1. Make sure that the datetimes are formatted as datetimes (and not as text) in the source sheet. In this case getValues will retrieve them as Date objects, eliminating the parsing steps.
  2. If the above is impossible, change the format of input strings to be ISO 8601 compliant, as described above.
  3. Parse the strings manually by using .split. Example below.

var str = '9/1/2017 23:00:00';  
var parts = str.split(/[/ :]/);
var d = new Date();
d.setFullYear(parts[2], parts[0]-1, parts[1]);
d.setHours(parts[3], parts[4], parts[5]);
Related Topic