2

I am trying to count the values in a cell based on the background color. Someone I got some help while online search I am not getting the complete solution. Does anyone help me out with this prob. I have sample code script which I got from the online search.

Also the script which am pasting here is grouping the values( For Example if i have a value A in three cells it should return the value as 3 instead it is returning AAA. Can someone help me out with the script to count the values and return it based on the background color

Thanks in Advance,

Here is the script:

function sumBackgroundColors(rangeString, color) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var sumRange = s.getRange(rangeString);
var sum = 0;
var sumRangeBackground = sumRange.getBackgroundColors();
var sumRangeValues = sumRange.getValues();

for(var row = 0; row < sumRangeBackground.length; row++ ) {
for(var col = 0; col < sumRangeBackground[0].length; col++ ) {
if( sumRangeValues[row][col]=="LG M"&& sumRangeBackground[row][col] == color ) {
sum = sum + parseFloat(sumRangeValues[row][col]);

} 
}
}
return sum;
}
Srinivas
  • 21
  • 1
  • 2

1 Answers1

2

I recommend you to not use a custom function to do this because of its caching feature (explanation here).

function countBackgroundColors() {
  var rangeString = "A1:A100";
  var color = "green";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var range = s.getRange(rangeString);
  var count = 0;
  var rangeColors = range.getBackgroundColors();

  for( var i in rangeColors )
    for( var j in rangeColors[i] )
      if( rangeColors[i][j] == color )
        ++count;
  return count;
}
Community
  • 1
  • 1
Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • Thanks for the answer.... It would be great if I can get the formula to use in the spreadsheet for the same so that I can check the above script. – Srinivas May 15 '12 at 11:48
  • That's what I told you in my 1st sentence. A custom function is not the way to go, please read the linked thread. And by the way, SO is a place to ask programming questions, not programs. This script is so similar to your original, you should be able to figure it out by yourself. – Henrique G. Abreu May 15 '12 at 12:12