Comparing 2 date values and checking difference between the 2 in minutes-CodePudding

I have a sheet with a time trigger set to run every 30 minutes. When the trigger happens a function is executed which will add a new row at the bottom of the sheet with some data. On column A I have dates.

Now the problem is sometimes the Google's trigger tool by error will execute like 3 times in a row or more with less then a minute in between each execution. This happens more often than I'd like and I need a way to fix this.

I wrote some code which supposedly will delete the new recorded row if the difference between this last row and the second last row, or previous row, is less than 30 minutes. This way all the rows will always be 30 minutes apart from each other.

I'm stuck at this point where I can't figure out a way of making Google Script to compare 2 dates and return TRUE or FALSE based on my condition, which is to check if the difference between 2 dates is more/equal or less than 30 minutes, and if it is less to delete the row, otherwise do nothing. Actually I gave the condition a margin of 1 minutes because the triggers are not 100% exact and don't always happen at the same second.

The variable timerDifference returns NaN. I suspect it might be because of the date format?

This is my code ATM:

function deleteTriggerError() {
    let logSheetName = "LOG";
    let logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(logSheetName);

    let lastRowTimer = logSheet.getRange(logSheet.getLastRow(), 1).getValue();
    let secondLastRowTimer = logSheet.getRange(logSheet.getLastRow() - 1, 1).getValue();
    
    console.log(lastRowTimer);
    console.log(secondLastRowTimer);
    
    let dysLast = Utilities.formatDate(lastRowTimer, timeZone, 'dd/mm/yyyy hh:mm:ss');
    let dysSecondLast = Utilities.formatDate(secondLastRowTimer, timeZone, 'dd/mm/yyyy hh:mm:ss');
    
    console.log(dysSecondLast);
    console.log(dysLast);

    let timerDifference = dysLast - dysSecondLast;
    console.log(timerDifference);

    let timerDifLimitRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(logSheetName).getRange("H3");
    let timerDifLimitValueTXT = timerDifLimitRange.getValue();
    let timerDifLimitValue;

    //console.log(timerDifference);

    timerDifLimitValue = timerDifLimitValueTXT.replace("3 0  M I N U T E S", 30 - 1);

    logSheet.appendRow([""]);
    if (timerDifference < timerDifLimitValue) {
        logSheet.deleteRow(logSheet.getLastRow());
        // console.log("TRUE");
    } else {
        // console.log("FALSE");
    }
}

I tried the solution I saw here: Time difference between two time showing wrong values in js

var diff = Math.abs(new Date('01/23/2020 06:30 PM') - new Date('01/23/2020 05:00 AM'));
var minutes = Math.floor((diff/1000)/60);
alert(minutes);

This solution will only work with en_US date format. But I'm using en_GB date format:21/09/2021 14:44:38. Any reason why? You can check:

var diff = Math.abs(new Date('01/23/2020 06:30 PM') - new Date('01/23/2020 05:00 AM'));
console.log(diff);
var minutes = Math.floor((diff/1000)/60);
console.log(minutes);

var diff = Math.abs(new Date('21/09/2021 14:44:38') - new Date('21/09/2021 14:04:38'));
console.log(diff);
var minutes = Math.floor((diff/1000)/60);
console.log(minutes);

var diff = Math.abs(new Date('09/21/2021 14:44:38') - new Date('09/21/2021 14:04:38'));
console.log(diff);
var minutes = Math.floor((diff/1000)/60);
console.log(minutes);

Thank you for your time.

My file: https://docs.google.com/spreadsheets/d/1ExXtmQ8nyuV1o_UtabVJ-TifIbORItFMWjtN6ZlruWc/edit?usp=sharing

CodePudding user response：

Date() doesn't support dd/mm/yyyy. This prevents ambiguity for cases like 1/2/2014 that yields into 2 possible dates, Jan 2 and Feb 1. So it only supports the mm/dd/yyyy as its standard format.

One way to converting it properly is to split the date.

function myFunction() {
  startDate = '21/09/2021 14:44:38';
  endDate = '21/09/2021 14:04:38';
  
  var diff = Math.abs(convertGBDatetoDate(startDate) - convertGBDatetoDate(endDate));
  console.log(diff);
  var minutes = Math.floor((diff/1000)/60);
  console.log(minutes);
}

function convertGBDatetoDate(string){
  var [sD, sM, sY] = string.split(' ')[0].split('/').map(i=>Number(i));
  var [sh, sm, ss] = string.split(' ')[1].split(':').map(i=>Number(i));
  return new Date(sY,sM - 1,sD,sh,sm,ss);
}

CodePudding user response：

Someone commented above, but then deleted, to use getTime(). Read here.

So I think this works. I don't even need to worry about date formats. I can just get the range with getRange() and getValue(). Then I can use this simple code:

var end, start;
start = new Date("Tue Sep 21 2021 20:00:00 GMT 0100 (British Summer Time)");
end = new Date("Tue Sep 21 2021 20:33:17 GMT 0100 (British Summer Time)");
console.log('Operation took '   (((end.getTime() - start.getTime())/1000)/60)   ' min');