CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsscriptingcountingadd-on
Mark
Mark

Reputation: 11

Count any colored cells in range

I am trying to create a Gantt Chart in Google Sheets, therefore I'll need to count all the colored cells, of any color, within a specific range, in order to get the duration of such task.

However, from what I've gathered, there's no built-in function.

https://www.youtube.com/watch?v=IuMSytD9t38 is the closest thing that I've found online, especially since it solves the "auto-refresh" problem. But it's not quite it.

Can anyone point me in the right direction?

Upvotes: 1

Views: 922

Answers (2)

Tom Woodward
Tom Woodward

Reputation: 1713

If you want to use Google Scripts, you'll use the getBackgrounds() function for the cell range.

The following code added to a sheet via Google Scripts allows you to put =colors("a1:a5") and get all the non-white cells counted. You do have to put the cell array in quotes for it to work.

You can see it working on this sheet.

function COLORS(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();//get this doc
  var sheet = ss.getActiveSheet();//get the active sheet
  var counter = 0;//no colors yet
  var range = sheet.getRange(input);//get range of cells from the function
  var bgColors = range.getBackgrounds();//get the array of background colors
  bgColors.forEach(function(element){
    var cleanColors = arrayRemove(element,'#ffffff');//kick out the white backgrounds
    counter = counter + cleanColors.length;//count them up
  })
 return counter;//return the total count
}


//kick things out of arrays from https://love2dev.com/blog/javascript-remove-from-array/#create-remove-method
function arrayRemove(arr, value) {

   return arr.filter(function(ele){
       return ele != value;
   });

}

Upvotes: 3

player0
player0

Reputation: 1

to count background color you can use this addon:

https://chrome.google.com/webstore/detail/custom-count-and-sum/njiklelndjpdbdngfkdgeijcpfabfgkb

and then you can simply use this formula:

=COUNTBACKGROUNDCOLOR("A2:A20", "A3")

A2:A20 being the range and A3 being the example of color to count

0

Upvotes: 0

Related Questions