How to set background color for a row based on cur

问题:

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!

回答1:

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.

回答2:

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

回答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.

回答4:

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

回答5:

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

}

回答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()

回答7:

This one is pretty simple.

Select the area it have to effect and Right click on the spreadsheet and select 'conditional format' From this menu select 'custom formula' (it might named different, but it's the last choice in the menu)

When a row need to be formatted when a cell in the column > 1, use this in the formula box

=$A$1:$A$100 > 1

Select the area you want to have effect like

A1:G100

that's it