0

Been using the following function to generate the sum of same-colored cells:

function sumColoredCells(sumRange,colorRef,test) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var sumValues = activeSht.getRange(countRangeAddress).getValues(); 
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var totalValue = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        if ((typeof sumValues[i][k]) == 'number')
          totalValue = totalValue + (sumValues[i][k]);
  return totalValue;
};

As of sometime in the past few weeks, this no longer works and results in a permanent loading notificaiton. I was wondering what broke it and if it's fixable.

Nick
  • 17
  • 6

2 Answers2

1

The failure of that function is due to the regex expressions, which in the script are suitable for countries where the comma is used as a separator. If you're using another country's settings instead, which use semicolons, you'll need to make two small changes to the script:

row 5 from: activeformula.match(/\((.*)\,/) to activeformula.match(/\((.*)\;/)

row 8 from: activeformula.match(/\.(.*)\)/) to activeformula.match(/\;(.*)\)/)

or by changing the regex to work in all countries by accepting alternatively both the comma and the semicolon

Daniele
  • 2,432
  • 2
  • 6
  • 14
0

Thank you to those confirming that formula functions. This was apparently related to an issue with Google Sheets that was resolved yesterday. Marking as answered.

Nick
  • 17
  • 6