Count the cells with same color in google spreadsheet

Question

I m trying count the number of cells with the same background color and put the result in other cell with a script in google apps script  but I can t do it  I have the next script but not work and I don t know which is the problem   function countbackgrounds      var book   SpreadsheetApp getActiveSpreadsheet     var range input   book getRange  B3 B4     var range output   book getRange  B6     var cell colors   range input getBackgroundColors   0    var color     58FA58    var count   0    for  var i in cell colors     if  cell colors i     color        range output setValue   count         else       return count

User · Answer

You can use this working script          param  range  countRange Range to be evaluated    param  range  colorRef Cell with background color to be searched for in countRange    return  number     customfunction     function countColoredCells countRange colorRef      var activeRange   SpreadsheetApp getActiveRange      var activeSheet   activeRange getSheet      var formula   activeRange getFormula       var rangeA1Notation   formula match             pop      var range   activeSheet getRange rangeA1Notation     var bg   range getBackgrounds      var values   range getValues       var colorCellA1Notation   formula match             pop      var colorCell   activeSheet getRange colorCellA1Notation     var color   colorCell getBackground       var count   0     for var i 0 i lt bg length i        for var j 0 j lt bg 0  length j          if  bg i  j     color           count count 1    return count       Then call this function in your google sheets    countColoredCells D5 D123 Z11

User · Answer

The previous functions didn t work for me  so I ve made another function that use the same logic of one of the answers above  parse the formula in the cell to find the referenced range of cells to examine and than look for the coloured cells  You can find a detailed description here  Google Script count coloured with reference  but the code is below   function countColoured reference      var sheet   SpreadsheetApp getActiveSheet      var formula   SpreadsheetApp getActiveRange   getFormula      var args   formula match    w          i  1  split         try       if  args length    1          var range   sheet getRange args 0              else         sheet   ss getSheetByName args 0  replace    g              range   sheet getRange args 1                catch e        throw new Error args join          is not a valid range          var c   0    var numRows   range getNumRows      var numCols   range getNumColumns      for  var i   1  i  lt   numRows  i          for  var j   1  j  lt   numCols  j            c   c     range getCell i j  getBackground        ffffff    0   1                return c  gt  0   c

User · Answer

here is a working version    function countbackgrounds      var book   SpreadsheetApp getActiveSpreadsheet     var range input   book getRange  B3 B4     var range output   book getRange  B6     var cell colors   range input getBackgroundColors     var color     58FA58    var count   0    for  var i in cell colors     Logger log cell colors i  0     if  cell colors i  0     color      count       range output setValue count

User · Answer

function countbackgrounds      var book   SpreadsheetApp getActiveSpreadsheet     var sheet   book getActiveSheet     var range input   sheet getRange  B3 B4     var range output   sheet getRange  B6     var cell colors   range input getBackgroundColors     var color     58FA58    var count   0    for var r   0  r  lt  cell colors length  r         for var c   0  c  lt  cell colors 0  length  c           if cell colors r  c     color           count   count   1                     range output setValue count

User · Answer

Easy solution if you don t want to code manually using Google Sheets Power Tools    Install Power Tools through the Add-ons panel  Add-ons -  Get add-ons  From the Power Tools sidebar click on the S button and within that menu click on the  Sum by Color  menu item Select the  Pattern cell  with the color markup you want to search for Select the  Source range  for the cells you want to count Use function should be set to  COUNTA  Press  Insert function  and you re done

[javascript] Count the cells with same color in google spreadsheet

Examples related to javascript

Examples related to google-apps-script

Examples related to google-sheets