I am using googlescript and would like to know the code on how I can highlight all rows if the date on a cell is within the specific duration.

example: if cell A2 is within Today() 5 and Today()-3 if yes, then it highlight all row in Gray if not, then it moves to the next row

Existing code: var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('O:O').activate(); var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules(); conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule() .setRanges([spreadsheet.getRange('O:O')]) .whenDateAfter(new Date()).setBackground('#808080')); .whenDateAfter(new Date(new Date().getTime()-10*(1000606024))) .whenDateBefore(new Date(new Date().getTime()-10(10006060*24))) .setBackground('#808080') .build());

CodePudding user response：

High light line if in range

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("SheetName");
  const dt = new Date()
  const hi = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate()   5).valueOf();
  const lo = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate() - 2).valueOf();
  const vs = sh.getDataRange().getValues();
  vs.forEach((r,i) => {
    let v = new Date(r[14]).valueOf()
    if(v < lo && v < hi) {
      sh.getRange(i   1,1,1,sh.getLastColumn()).setBackground("#ffff00");//highlites line to yellow
    }
  })
}