Zac123
asked on
onOpen. check dates in column, colour background
Hi All,
i'd like an onOpen and onEdit which checks down column A on one particular sheet which is called dataEntry. Cells which contain a date that is 6+ days old should have their background colour changed to red.
please note i have several other onOpen/onEdit functions so i'm aware they needs unique names and triggers setting up.
i've found a few examples on line but none of them are quite what i'm looking for and unfortunately my very basic knowledge means i'm struggling to edit them.
i'd like an onOpen and onEdit which checks down column A on one particular sheet which is called dataEntry. Cells which contain a date that is 6+ days old should have their background colour changed to red.
please note i have several other onOpen/onEdit functions so i'm aware they needs unique names and triggers setting up.
i've found a few examples on line but none of them are quite what i'm looking for and unfortunately my very basic knowledge means i'm struggling to edit them.
ASKER
ASKER
so my code looks like this:
and i have setup two triggers. one for onOpen and one for onEdit.
is that correct?
function checkDate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("dataEntry");
var dates = sheet.getRange("A:A").getValues();
for(var i=0;i<dates.length;i++) {
var d = dates[i][0];
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");
}
}
and i have setup two triggers. one for onOpen and one for onEdit.
is that correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yep spot on thanks.
did i actually need to setup the triggers for onOpen and onEdit?
did i actually need to setup the triggers for onOpen and onEdit?
Yes, set the triggers
Open in new window