Link to home
Start Free TrialLog in
Avatar of Zac123
Zac123Flag for United Kingdom of Great Britain and Northern Ireland

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.
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Just add more events for your sheet :

User generated image
function myFunction() {
  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");
  }
}

Open in new window

Avatar of Zac123

ASKER

hi leakim,

this seems to work if i take the these two lines out:
 d.setHours(0,0,0,0);
    today.setHours(0,0,0,0);


but the problem is that it make also all the blank cells red as well.

User generated image
Avatar of Zac123

ASKER

so my code looks like this:

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");
  }
}

Open in new window



and i have setup two triggers. one for onOpen and one for onEdit.

is that correct?
ASKER CERTIFIED SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zac123

ASKER

yep spot on thanks.

did i actually need to setup the triggers for onOpen and onEdit?
Yes, set the triggers