2

I need to create a script to based on the date in column B to change the background color of the row from column B to column d based on if the date in column b is one day, two days, three days, etc. ahead of today.

I have found some code from another post however it is evaluating multiple rows with dates

function formatting() {
  var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Schedule Fields');
  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=1;j<=1;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');
      }
    }
  }
}

I was able to change this code enough to get the background color of a date in column B. I'm not sure how to change this to set background color of ColB-ColD and then add onto the if condition to equate for 1 day 2 day 3 day etc. in the future of today.

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

This is the complete original snippet I used from conditionally formatting cells based on date

VikingScript
  • 109
  • 9
  • 1
    Please share the complete snippet (HTML/JS ) you have used – nircraft Sep 26 '19 at 19:30
  • I added the complete snippet to the post. – VikingScript Sep 26 '19 at 19:41
  • Could you share a sanitized version of your sheet to be able to better take a look at the way the data is ordered? as for changing the colors it's better to make a batch change at once using `setBackgroundColors()` instead, but once I see the sheet it'll be easier to show you how. – AMolina Sep 27 '19 at 06:49

1 Answers1

1

try this:

function colors(){
  var ss = SpreadsheetApp.getActive().getSheetByName("allGroups");
  var data = ss.getDataRange().getValues();
  var colors = ss.getDataRange().getBackgrounds();
  var today = new Date();
  var date = new Date(data[2][1])
  var diffTime, diffDays

  for (var i = 0; i < data.length; i++){
    date = new Date(data[i][1]); // Current date to evaluate in the sheet.
 // diffTime = today - date;
    diffTime = date - today;  
    diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
    if (diffDays == 1){
      colors[i][1] = "red";
      colors[i][2] = "red";
      colors[i][3] = "red";
    }
    else if (diffDays == 2){
      colors[i][1] = "yellow";
      colors[i][2] = "yellow";
      colors[i][3] = "yellow";
    }
  }
  ss.getDataRange().setBackgrounds(colors);
}

This code will compare the date in column B to today and if it's 2 days away it will set the date cell to yellow, if it's one day away it will set it to red.

AMolina
  • 1,355
  • 1
  • 7
  • 17