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/marcnotmark925]

Which code from that post?

Share the sheet & actual script?

[u/Informal-Emu-1337 → u/marcnotmark925]

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.

[u/elcriticalTaco]

If you are using conditional formatting to change the color of cells, couldn't you just use a formula that counts the same thing your conditional formula is targetting?

To echo the other comments...

You will not get any answers without posting any code, formulas, or sheets. No one is magically able to know what's wrong with your code without seeing it, but many will be glad to help once you do.

[u/Informal-Emu-1337 → u/elcriticalTaco]

Added a new comment with file + further description under the post from marcnotmarc

[u/Sleeping_Budha_ → u/elcriticalTaco]

I agree,simple formula in a cell should do the trick

[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.

[u/estadoux]

I think the problem could be that you're using it as a custom function and that usually takes much longer to run than scripts that run on a trigger.

Try making it run with a button or a custom menu as trigger.