Multiple column conditional formatting

问题:

I have searched for an answer to this, but I cannot find one anywhere.

I am trying to set a background color of an individual cell based on the contents of that cell and another cell.

For example, I want A1 to have a background color of green if that specific cell is blank AND if B1 does not have an "x" in it. If either condition is not met, i.e. there is something in A1 or B1 does not have an "x", I don't want A1 to be highlighted.

I then want to apply this to the entire column A, and have it update each time columns A or B are edited.

回答1:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      s = ss.getSheetByName('Sheet1'),
      values = s.getRange('A1:B').getValues(),
      row, len, colors = [];

  // CHECK VALUES FOR CONDITIONS
  for (row = 0, len = values.length; row < len; row++) {
    if (values[row][0] == '' && values[row][1] != 'x')
      colors.push(['green']);
    else colors.push(['white']);}

  // SET NEW COLORS ALL AT ONCE
  s.getRange('A1').offset(0, 0, len).setBackgrounds(colors);
}