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?

up vote 0 down vote accepted

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();
  • @zaqthanks for taking a look at it. It seems to still be giving me the same error, that it can't find the range specified in line 14, which is: var range = activeSheet.getRange(rangeA1Notation); – jnohl Dec 1 '16 at 14:51
  • that returns the following: [16-12-01 09:23:25:277 CST] L7:AP7,AP7) So is this trying to include the AP7 at the end when it should just be looking at L7:AP7 at this point? – jnohl Dec 1 '16 at 15:24
  • Thanks! That worked. I have only been scripting for a little while, I am not sure I follow all of the information within the match statement. Is there a good reference out there for understanding this for when I inevitably encounter another issue? – jnohl Dec 1 '16 at 19:51
  • 1
    regular-expressions.info for general information about regexes, regex101.com for testing them. – user135384 Dec 1 '16 at 19:55

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.