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?