Google App Script for spreadsheet

Question

I need to put a background color to the range cells depending the value of other cell, but this range have a diferents values, for example:

1 X 2 1 X X | 2

The range cells must be independent colors when check the las cell that have value 2, namely, only the cell three must have a green background color because is the same value that the and the others in a red color.

I have this code:

function test() {
  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var range_input = libro.getRange("B3:E3");
  var target_cell = libro.getRange("G3");

  if (range_input.getValues()==target_cell.getValue()) 
  {
    range_input.setBackgroundColor('#58FA58');    
  }

  else
  {
    range_input.setBackgroundColor('#FA5858')
  }  
}

But the problem is that not work because the row only is green when all values cells have the same value that the last cell, this means that the entire row become red although the cell three have the correct value.

Thx in advance.

Fred Fred · Accepted Answer · 2013-01-13T20:43:06

range_input.getValues() returns an array of arrays of values for the 4 cells, while target_cell.getValue() returns 1 value.

function test() {
  var libro = SpreadsheetApp.getActiveSpreadsheet();
  var range_input = libro.getRange("B3:E3");
  var target_cell = libro.getRange("G3").getValue();//Save the value

  //Gets the values of the range ([0] to retrieve just the first row of values)
  var input_values = range_input.getValues()[0];
  //Gets the backgrounds of the range ([0] to retrieve just the first row of values)
  var backgrounds = range_input.getBackgroundColors()[0];

  //Loop through values in the row, checking against the cell.
  for(var i = 0; i < input_values.length; i += 1){
    if(input_values[i] === target_cell) {
      backgrounds[i] = '#58FA58';
    } else {
      backgrounds[i] = '#FA5858';
    }
  }
  //Put the row of backgrounds back in an array to make it 2d and set the backgrounds
  range_input.setBackgroundColors([backgrounds]);
}

Google App Script for spreadsheet

1 Answers