We help IT Professionals succeed at work.

Colour background if date is 6 days + old

hello i have this script which i would like to use on my google sheet so that if a data in column A is over 6 days then it will colour the cell as red.
Unfortunately its not throwing any errors so i don't know what it wrong with it but its not working.

function checkDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Active Jobs");
  var dates = sheet.getRange("A:A").getValues();
  for(var i=0;i<dates.length;i++) {
    var d = dates[i][0];
    var isNotString = typeof d != "string";
    if(isNotString) {
      var today = new Date();
      d.setHours(0,0,0,0);
      today.setHours(0,0,0,0);
      var diff = (today - d)/(24*60*60*1000);
      var cell = sheet.getRange("A" + (i+1) + ":A" + (i+1));
      if(diff>=6)
        cell.setBackgroundRGB(255,0,0);
      else
        cell.setBackground("white");
    }
  }
}

Open in new window


can anyone see what might be wrong here please?
Comment
Watch Question

NorieAnalyst Assistant
BRONZE EXPERT

Commented:
Zac

The code works fine for me.

Have you checked the sheet name?

Are you sure you have 'real' date values in column A?
Zac

Author

Commented:
Hi, the sheet name is definitley called Active Jobs

the date in column A looks like this:

20/02/2020
Commented:
ok i have fixed this. i did not have a trigger setup correctly to run the check date function