How to set background color for a row based on cur

Question

I have a Google Docs SpreadSheet, where in the column A are dates (A1: 2013-11-22, A2: 2013-11-23, A3: 2013-11-24 etc). I would like to automatically highlight - set a background color for a row, where in the column A is today's date. To have every day a different row highlighted.

I expect that I will need a script, IMHO this is not possible to be done with conditional formating in Google Docs SpreadSheet.

Any idea how to do it? Thanks a lot!

Answer 1

Where A houses dates, try this.

• Right click
• Conditional Formatting
• change to "Custom Formula is" in drop down box on the left

• Enter this custom formula:

  =(A=TODAY())
  

• Choose your background and text color

• Range: A1:1

Answer 2

You could also just have a new column in the spreadsheet do the date matching and return a flag if it's today...

=ARRAYFORMULA(IF(A1:A =TODAY(), 1, "")) - formula in cell D1

then try..

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');

function onOpen() {
  var values = ss.getRange('D1:D').getValues();  // column of date flag

  for ( var row = 0; row < values.length; row++ ) {
    if ( values[row][0] ) {
      break; // assuming only 1 row has today's date
    }
  }

  s.getRange('A1:D').setBackground(null);  // range to clear
  s.getRange(row + ":" + row).offset(1, 0).setBackground('yellow');

}

Answer 3

If you want it to be automatic on spreadsheet open you will have to install an installable onOpen that will call the below function (from script editor goto ressources > this script triggers > add a new trigger > sreadsheet / on Open)

And here is the code for columns: (see below for rows)

function customOnOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var headers = sh.getRange(1,1,1,sh.getLastColumn()).getValues();
  var today = new Date().setHours(0,0,0,0);
  for(var n=0;n<headers[0].length;++n){
    var date = new Date(headers[0][n]).setHours(0,0,0,0);
    Logger.log(today+' =? '+date)
    if(date==today){
      n++
      Logger.log('match on column '+n)
      if(n>=2){sh.getRange(1,n-1,sh.getMaxRows(),1).setBackground(null);};// resets the backGround for yesterday if not the first column
      sh.getRange(1,n,sh.getMaxRows(),1).setBackground('yellow');
      break;
    }
  }
}

this version to colorize rows

function customOnOpen2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var headers = sh.getRange(1,1,sh.getLastRow()).getValues();
  var today = new Date().setHours(0,0,0,0);
  for(var n=0;n<headers.length;++n){
    var date = new Date(headers[n][0]).setHours(0,0,0,0);
    Logger.log(today+' =? '+date)
    if(date==today){
      n++
      Logger.log('match on column '+n)
      if(n>=2){sh.getRange(n-1,1,1,sh.getMaxColumns()).setBackground(null);}
      sh.getRange(n,1,1,sh.getMaxColumns()).setBackground('yellow');
      break;
    }
  }
}

Note : if you want it to run fully automatically based on a timer it's perfectly doable, just change the ss and sh variable using openById and getSheetByName (see doc here)and set up a timer to make it run every day around 1 AM.

Answer 4

I have modified an example from Serge (thanks, Serge!), dates are in the A column. Rows with a date have background color cleared, other rows are intact. Bonus: a custom menu to run a script on active sheet.

/* check for a cell format */
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" )
    return false;
  return !isNaN(d.getTime());
}

/* check for a cell format */
function colorRow()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  customOnOpen(sh);
}

/* set the background - main function, sh is a sheet */
function customOnOpen(sh) {
  var headers = sh.getRange(1,1,sh.getLastRow()).getValues();
  var today = new Date().setHours(0,0,0,0);
  for(var n=0;n<headers.length;++n){
    var date = new Date(headers[n][0]).setHours(0,0,0,0);
    Logger.log('Test row '+n);
    if(date==today){
      Logger.log('Set bg at '+n);
      sh.getRange(n+1,1,1,sh.getMaxColumns()).setBackground('yellow');
    }
    else
    {
      if (isValidDate(headers[n][0])){
        Logger.log('Clear bg at'+n);
        sh.getRange(n+1,1,1,sh.getMaxColumns()).setBackground(null);
      }
      else{
        Logger.log('Not a date at'+n);
      }
    }
  }
}


function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  /* prepare the custom menu */
  var entries = [{
    name : "Set background",
    functionName : "colorRow"
  }];
  sheet.addMenu("My menu", entries);

  /* run the function for two specific sheets */
  customOnOpen(sheet.getSheetByName('Sheet1'));
  customOnOpen(sheet.getSheetByName('Sheet2'));  
};

Answer 5

This is how I did it. I did conditional formatting and picked my range, then in "format cells if..." I chose custom and used this formula:

=$B$2:$B$92 = today()

I have my dates in the B column and this highlights the entire row, within my range, for today's date.

Answer 6

The simple answer is like Clinet.

Format -> Conditional Formatting...-> (Format cells if...) Custom formula is

Then fill in =cells-range=today()

For example: =$I$2:$BC$2=TODAY()