CODEWITHSUNDEEP
google-sheetsgoogle-apps-script
Srinivas
Srinivas

Reputation: 21

How to count cell values based on the background color in Spreadsheet

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;
}

Upvotes: 2

Views: 7805

Answers (1)

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

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;
}

Upvotes: 2

Related Questions