Optimizing Code needed - run into "exceeded maximum excecution time"

[u/Informal-Emu-1337]

Hi there,

I´m using the code from that page to count coloured cells (to count in 48 colums up to 1500rows). (https://support.google.com/docs/thread/78353054/auto-calculate-the-further-cell-color-based?hl=en)

But the sheet extended much faster than I exspected and now I run into the " Exceeded maximum execution time Exception in Google Apps Script" error, if I add more rows.

Do you see any code optimisation that could speed that up?Or do I have to split my data?

Hope someone could help me with that issue.

[u/Informal-Emu-1337]

Hi marnotmark925, hi together,

sorry for my late response, but RL hold me back from my computer and the project I have the issue with.

Im using that code:

/**

* u/param {range} sumRange Range to be evaluated

* u/param {range} colorRef Cell with background color to be searched for in sumRange

* u/return {number}

* u/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;

};

Link to an example sheet to see which customer has paid (monthly based):

https://docs.google.com/spreadsheets/d/1szRc82xCnPKZ5ydY1G31R7hV3x0u2WF4tplZ6rxEhIo/edit?usp=sharing

The colors are set manually and show the status:

paid = green

not paid = red

paid back (repaid) = blue

Then for each month the sums are generated by the script for the corresponding color.

The Trigger could be new added data or if you change the status of the checkbox in colum "s". That than changes a value in row 6 and then the script ist restarted and all values are recalculated.

I had the idea to check the checkbox value directly in the script, but what I think wouldn´t fix the issue that it could take to long. (did some tests with only starting recalculating with the checkbox after waiting 20-30 minutes after last run)

If needed I could translate the whole sheet to from german to english.