2

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;
}
  • Have you validated your script (press bug button)? – Jacob Jan Tuinstra Mar 24 '14 at 6:48
  • Why don't you share the doc, so we can have a look. Your code looks a little bit odd to my. – Jacob Jan Tuinstra Mar 24 '14 at 6:55
  • thanks @JacobJanTuinstra when i validate i get "Range not found (line 5, file "Code")" heres the file: goo.gl/Q3WxUV – user616860 Mar 24 '14 at 15:20
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.