Google Script - function comparing if date is older than today

Question

there are dates in one column, which is automatically updated in case of changing status of some other column. In case of changed from "Open" to "In progress" it sets today`s date.

I need a function which is automatically doing "something" in case that the date is older than today.

So far have this code, but it doesnt work and I don`t know why. Thanks

function backgroundformate() {
  var odpovediSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Odpovědi formuláře");
  var lastRow = odpovediSheet.getLastRow();
  var columnH = odpovediSheet.getRange(2, 8, odpovediSheet.getLastRow(), 1).getValues();
  var day = 24*3600*1000
  var today = parseInt((new Date().setHours(0,0,0,0))/day);

  for (var i=1; i < columnH.length; i++) {
    var dataday = parseInt(new Date(columnH[i][8]).getTime()/day);
    if (dataday < today) {odpovediSheet.getRange(i+1, 8, lastRow, 1).setBackground("red")}
    else {odpovediSheet.getRange(i+1, 8, lastRow, 1).setBackground("yellow")}
  }
}

Answer 1

You actually don't need to parse dates to compare them, JavaScript can do that natively. here is your working code :

function backgroundformate() {
  var odpovediSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Odpovědi formuláře");
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var lastRow = odpovediSheet.getLastRow();
  var columnH = odpovediSheet.getRange(2, 8, odpovediSheet.getLastRow(), 1).getValues();
  var today = new Date(new Date().setHours(23,59,59,0,0));// I set the hour to near midnight so you can get the right result today until 23:59:59 

  for (var i=1; i < columnH.length; i++) {
    var dataday =columnH[i][0];
    if (dataday < today) {odpovediSheet.getRange(i+1, 8, lastRow, 1).setBackground("red")}
    else {odpovediSheet.getRange(i+1, 8, lastRow, 1).setBackground("yellow")}
  }
}

Also, you did make a mistake in this code :

var dataday = parseInt(new Date(columnH[i][8]).getTime()/day);

the value you are looking for is at index 0 since you only get data from this column when you defined the range. The code should have been been

var dataday = parseInt(new Date(columnH[i][0]).getTime()/day);

but anyway, the parsing is not necessary.

That said, is is not good practice to call ss service in a loop, you could do that much more efficiently using arrays and single SS calls. Code goes like this, please try and compare execution speeds.

  function backgroundformate() {
      var odpovediSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Odpovědi formuláře");
      SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
      var columnH = odpovediSheet.getRange(2, 8, odpovediSheet.getLastRow()-1, 1).getValues();
      var columnHBG = odpovediSheet.getRange(2, 8, odpovediSheet.getLastRow()-1, 1).getBackgrounds();
      var today = new Date(new Date().setHours(23,59,59,0,0));

      for (var i=0; i < columnH.length; i++) {
        var dataday =columnH[i][0];
        if (dataday < today) {
          columnHBG[i][0]='#F00';
        }else{
          columnHBG[i][0]='#FF0';
        }
      }
      odpovediSheet.getRange(2, 8, odpovediSheet.getLastRow()-1, 1).setBackgrounds(columnHBG);
    }