Google Sheets – Change Cell Color in a Range

conditional formattinggoogle sheetsgoogle-apps-script

i have 4 columns of values in each cell, the cells are different colors :

Cell A1 = 12 (color – Green)
Cell B1 = 10 (color – Pink)
Cell C1 = 11 (color – Blue)
Cell D1 = 13 (color – Red)

Cell E1 = (will return a copy of the largest cell) – 13 (Color – 13)

I'm trying to create a function that would return the largest value of the 4 columns and that cells color

This is what I have but not getting any luck, I get the following error:

You do not have permission to call setBackground

=getLargeVal("A1:D1", LARGE(A1:D1,1), "E1")

function getLargeVal(_range, _val, _dest){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var range = sheet.getRange(_range);
var bgColors = range.getBackgrounds();
var color=0;
var cells= range.getValues();
var cell=0
var destination = sheet.getRange(_dest);

for (var i in bgColors) {
  for (var j in bgColors[i]) {
    if(cells[i][j] == _val){
      color = bgColors[i][j];

      cell = cells[i][j];
      destination.setBackground(color = bgColors[i][j]);
    }
  }
}

return cell;
}

Best Answer

Use the following code to accomplish your goal.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Custom Format')
    .addItem('Max & Color', 'getmaxColor').addToUi();
}

function getmaxColor() {
  var ss = SpreadsheetApp.getActive(), aRange = ss.getActiveRange(); 
  var data = aRange.getValues(), colors = aRange.getBackgrounds();
  var dest = aRange.offset(0, data[0].length, 1, 1);
  var index = getmaxIndices(data);
  var max = data[index.i][index.j], color = colors[index.i][index.j];
  dest.setValue(max).setBackground(color);  
}

function onEdit() {
  var ss = SpreadsheetApp.getActive(), col = ss.getActiveCell().getColumn();
  if(col < 7) {
    var aRange = ss.getRange("A1:F7"), data = aRange.getValues();
    var colors = aRange.getBackgrounds();
    var dest = aRange.offset(0, data[0].length, 1, 1)
    var index = getmaxIndices(data);
    var max = data[index.i][index.j], color = colors[index.i][index.j];
    dest.setValue(max).setBackground(color); 
  }
}

function getmaxIndices(data) {
  var max = 0, indexI = 0, indexJ = 0;
  for(var i=0, iLen=data.length; i<iLen; i++) {
    for(var j=0, jLen=data[0].length; j<jLen; j++) {
      if(data[i][j] > max) {
        max = data[i][j], indexI = i, indexJ = j;
      }
    }
  }
  return {"i":indexI, "j":indexJ};
}

Explained

The onOpen() adds a menu item. Select the range you want to evaluate and select the menu option Max & Color. The values and backgrounds are collected as is an offset range. Then the indices (2d-array) of the max value are obtained, through the getmaxIndices(). Afterwards the corresponding max value and color is set.

The onEdit() does almost the same, but it has a fixed range. It will only fire if a cell is edited and if the edited cell belongs to the A1:F7 range.

Note

The reason for getting the error message, can be read in this answer: custom function

Example

I've added the sample code into your example file.

Related Topic