Conditionally formatting cells based on date

Question

I want to have script based formatting to color code date columns.

Row 1 is labels. Cols C-E contain dates;these are the cells I want to color format per date. I'm starting with Yellow in the conditional and will add more colors once I'm paste this hurdle.

The hope is to check each cell and color it appropriately. This current version is coloring blocks of cells without reason, apparently by row. All three columns for the range (C2:E9) and (C13:E13) are turning yellow, while C10:E12 and C14:E14 remain normal. Many of the dates across all these ranges are the same.

my code:

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
  var columnO = sheet.getRange(2, 3, sheet.getLastRow() - 1, 3);//the range starts at column 3.  
  var oValues = columnO.getValues();
  
  for (var i = 0; i < oValues.length; i++) {
     for(var j = 2; j <= 4; j++) {//j=2 is actually column 5
       //convert dates from GMT to local
       var thisDay = new Date();
       var todayLocal = thisDay.toLocaleString();
       var bullDate = new Date(oValues[i][0]);
       var bullLocal = bullDate.toLocaleString();
  
       if (bullLocal < todayLocal) {
         sheet.getRange(i + 1, j + 1, 1, 1).setBackgroundColor('yellow');
      }
    }
  }
}

There are other ways to do this, I think, but trying to be efficient in my code. Ideas?

You're comparing dates as strings - why? Compare them directly, i.e. `date1 < date2` or `date1.getTime() < date2.getTime()` — tehhowch, Apr 12 '18 at 18:55
I had some trouble previously with dates in GMT format, so changing to local I though would prevent the issue. I suspect that wasn't the problem at the time, thinking back on it, so you may be right. — Rob Campbell, Apr 12 '18 at 22:08
@tehhowch the help you gave me early on in this project has carried me a long way. — Rob Campbell, Apr 12 '18 at 22:08
Read this post https://stackoverflow.com/questions/49759420/date-comparisons-with-formatdate-in-google-apps-script-are-1-day-behind. and use date objects in your comparison — Serge insas, Apr 13 '18 at 01:11

Cooper · Answer 1 · 2018-04-13T15:05:36.610

In your function the range started in column 3 and so oValues[0][0] was actually found at row 1 column3.

try this:

function formatting() {
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
  var rg=sheet.getDataRange();//this range starts at column 1
  var vA=rg.getValues();
  var today = new Date();
  for(var i=0;i<vA.length; i++){
    for(var j=2;j<=4;j++){//j=2 is the third column
      var bullDate = new Date(vA[i][j]);  
      if (bullDate < today){
        sheet.getRange(i+1,j+1).setBackgroundColor('yellow');
      }
    }
  }
}

Conditionally formatting cells based on date

1 Answers1

Linked