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"); } }}
The code works fine for me.
Have you checked the sheet name?
Are you sure you have 'real' date values in column A?