Google-sheets – google sheets sum of colored cells TypeError: Cannot read property ‘pop’ of null

google sheetsgoogle-apps-script

I am using this gentlemans script:

http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-sum-of-colored-cells.html

To sum up the value of only colored cells but I get an error message saying "Error
TypeError: Cannot read property 'pop' of null (line 15)."

Here is the script he created that I am trying to use:

    /**
    * @param {range} sumRange Range to be evaluated
    * @param {range} colorRef Cell with background color to be searched for in sumRange
    * @return {number}
    * @customfunction
    */

    function sumColoredCells(sumRange,colorRef) {
      var activeRange = SpreadsheetApp.getActiveRange();
      var activeSheet = activeRange.getSheet();
      var formula = activeRange.getFormula().toString();
      formula = formula.replace(new RegExp(';','g'),',');


      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 total = 0;

      for(var i=0;i<bg.length;i++)
        for(var j=0;j<bg[0].length;j++)
          if( bg[i][j] == color )
            total=total+(values[i][j]*1);
      return total;
    };

Perhaps Google changed something in the environment that completely breaks this script? I see that it was written a few years back…

I also came across someone with a similar issue and their solution was to replace lines 15 and 20 respectivly thus:

var rangeA1Notation = formula.match(/sumColoredCells\(([^,]*),/).pop();

and

var colorCellA1Notation = formula.match(/,(\w{2,})\)/).pop();

But makes no difference to the error.

Best Answer

I had the same issue recently using the code you provided two weeks ago, so I eventually have rewritten it from scratch because I only needed a way to count the colored cells whose colors does not match the specified criterion color. This is what I've ended up with:

// based on https://stackoverflow.com/questions/14365436/count-the-cells-with-same-color-in-google-spreadsheet/58798353#58798353 by @iamcessssy
// ranges seem to be always passed as values so need to pass the "specs" as strings instead
function countBackgroundColoredCells(rangeSpec, colorCellSpec) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const colorsToCheck = sheet.getRange(rangeSpec).getBackgrounds();
  const doneColor = sheet.getRange(colorCellSpec).getBackgrounds()[0][0];
  let count = 0;
  for ( let i = 0; i < colorsToCheck.length; i++ ) {
    for ( let j = 0; j < colorsToCheck[i].length; j++ ) {
      const color = colorsToCheck[i][j];
      if ( color === doneColor ) {
        count++;
      }
    }
  }
  return count;
};

I don't believe it's idiomatic since I don't really work with spreadsheets, and I'm just a casual user. Example of usage:

=countBackgroundColoredCells("schedule!$B$5:$K$14"; "schedule!$A$1")

Unfortunately, since the parameters/arguments are strings, this approach does not work nice with the dynamic nature of spreadsheest and the ranges must be hard-coded in strings. If I'm correct, the ranges cannot be directly passed to functions as objects since values are only passed, so there is no way to get colors of their cells.

I've just noticed that sumColoredCells from the original code ignores its first parameter sumRange. What does it do if the function first statement is replaced from var activeRange = SpreadsheetApp.getActiveRange() to var activeRange = sumRange?

Related Topic