Link to home
Create AccountLog in
Avatar of Zac123
Zac123Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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?
Avatar of Norie
Norie

Zac

The code works fine for me.

Have you checked the sheet name?

Are you sure you have 'real' date values in column A?
Avatar of Zac123

ASKER

Hi, the sheet name is definitley called Active Jobs

the date in column A looks like this:

20/02/2020
ASKER CERTIFIED SOLUTION
Avatar of Zac123
Zac123
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account