Script Excel Google sheet - Count how many cells with certain background color exist

0

I have this code I found online:

/**
* @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<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};

This is giving this error when I run the debug

TypeError: Não é possível chamar o método "pop" de null. (linha 13)

I have tried to change match(/\((.*)\,/) to match(/\;(.*)\)/) but it still gives the same error.

I would like to be able to help and if there is no solution, I can use it to get the desired result.

    
asked by anonymous 24.09.2018 / 02:48

0 answers