How to set background color for a row based on current date in Google Docs Spreadsheet?

google-apps-script, highlight, row, spreadsheet

Solution

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

Problem

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!

Original source