Im trying to detect:

1. If it is a date
2. If it is before todays date (Regardless of the hour)
3. If point 1 and point 2 are ok, than mark in red.

Can somebody help me please? Here is my google sheet: https://docs.google.com/spreadsheets/d/1EPW4qbv1K55risE9hpiy5rdYWtmufJb4T3o-3Dzhp-g/edit?usp=sharing

function myFunction() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  for (var i=2; i<=8; i  ){
    var data = ss.getRange(i,1).getValue();
    var isDate = data instanceof Date;
  
    var today = now.getTime().getValue();

if(data = isDate && data < today){
    ss.getRange(i,1).setBackground("red");
    }
}
}

CodePudding user response：

I believe your goal is as follows.

• In the column "A" of the Spreadsheet, when the value is the date object and the date is before today, you want to set the background color of the cell to "red" using Google Apps Script.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A2:A"   sheet.getLastRow());
  var today = new Date().getTime();
  var colors = range.getValues().map(([a]) => [a instanceof Date && a.getTime() < today ? "red" : null]);
  range.setBackgrounds(colors);
}

• When this script is used, I thought that your goal might be able to be achieved.

• In your script, getValue and setBackground are used in a loop. In this case, the process cost will become high. Ref So, I proposed the above sample script.

• In the above script, the background color of cells is overwritten. For example, if you want to set the background color to only the searched cells, how about the following sample script?

    function myFunction() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var today = new Date().getTime();
      var ranges = sheet.getRange("A2:A"   sheet.getLastRow()).getValues().flatMap(([a], i) => a instanceof Date && a.getTime() < today ? [`A${i   2}`] : []);
      sheet.getRangeList(ranges).setBackground("red");
    }
  

References:

• getValues()
• map()
• setBackgrounds(color)

CodePudding user response：

Try it this way:

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const vs = sh.getRange(2, 1, 7).getValues().flat();
  const bs = sh.getRange(2, 1, 7).getBackgrounds();
  const dt = new Date();
  const td = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate());
  const tdv = td.valueOf();
  vs.forEach((e, i) => {
    if (Object.prototype.toString.call(e) === '[object Date]' && e.valueOf() < tdv) {
      bs[i][0] = '#ff0000';
    }
  })
  sh.getRange(2,1,bs.length,1).setBackgrounds(bs);
}