Google Sheets - Sum Colored Cells within an If Statement

Question

I am using the following script to get the sum of a range of cells that have a certain colored background. The script works fine to create a custom function and use it within the spreadsheet. Since Google does not recognize a color change as a "change" and make the function recalculate, I have created another script tied to a button, that makes an arbitrary change in all of the rows and then deletes that change, therefore activating the function to recalculate, essentially a refresh button. The problem is that I am applying this function to a column in a spreadsheet with ~300 rows. It takes a long time to calculate and I really only need it to be recalculating on probably 10 of the 300 but they change daily.

Here is the script:

function sumColoredCells(sumRange,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 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;
};

My thought is that I could put the custom function, sumColoredCells, within an If statement, therefore it would only calculate if there were values within the range and it would just stay at 0 and not calculate if there were no values.

=if(sum(L7:AP7)>0,sumColoredCells(L7:AP7,AP7),0)

This isn't working and I am getting an error that it can not find the range in line 14.

Any advice?

Answer 1

The reason you get an error is that this function is parsing the formula from which it's called, and does so with the assumption that it's the only part of the formula. But since it's not, the regular expressions match wrong bits, e.g., /\((.*)\,/ greedily matches (sum(L7:AP7)>0,sumColoredCells(L7:AP7,AP7),

Here's one way to make the regexes more specific, so they don't pick up extra stuff:

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

and also

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