How to avoid 'exec maxSimultaneous' limit in Google Spreadsheet?

Question

I'm using Google Spreadsheet for my tax administration. All financial transactions that I have done in a year are in the spreadsheet. Because the tax rules in my country are quite complex, I've developed a number of JavaScript functions to help me with my calculations. There are many rows in my spreadsheet, about 1000. Each row has multiple references to those JavaScript functions.

This system worked beautifully before, but today I've found that Google installed some kind of runtime limiting system into Google Spreadsheet, causing many of my columns to abort with the following error:

Service invoked too many times in a short time: exec maxSimultaneous.
Try Utilities.sleep(1000) between calls.

After some investigation, it would appear that this time limit is there to protect against scripts that take too long time to run. My case is different: my scripts are all short, O(1) algorithms that do nothing but calculating some numbers. A typical script looks like this:

// Calculates the total amount excluding Value Added Tax, given
// an amount including Value Added Tax, and other sorts of information.
function ex_btw(inc_btw, commentaar, soort, btw_verlegd, btw_pct) {
  Utilities.sleep(1000);
  var soort = soort.toLowerCase();
  if (soort == 'eten en drinken'
   || commentaar.match(/treinkaartje/i)
   || commentaar.match(/treinticket/i)
   || commentaar.match(/taxi/i)
   || commentaar.match(/ boek /i))
  {
    return inc_btw / 1.06;
  } else if (soort == 'priveonttrekking'
   || soort == 'boete'
   || soort == 'belasting'
   || commentaar.match(/postzegel/i)
   || btw_verlegd == 'Ja')
  {
    return inc_btw;
  } else {
    return inc_btw / (1 + btw_pct);
  }
}

The script is then invoked like this from a cell:

=IF(B6<>""; ex_btw(B6;D6;E6;J6;S6); "")

Maybe my problem is that I have too many script calls. Every single row calls about 6 of such scripts, so with 1000 rows I call 6000 times per spreadsheet.

How do I solve this problem? Is there a way to increase the execution limit, or is there a way to make the scripts run slower so that they don't hit the execution limit? As you can see in the example code I've already tried inserting Utilities.sleep(1000), but that doesn't seem to solve the problem. I don't care whether the scripts run slowly, I just just care that they finish without errors.

Can I pay to have the limit increased? I need to hand in my taxes in a few days.

Other alternatives that I've considered, but that are not feasible.

Using non-JavaScript functions. Not feasible because: they don't support collaboration like Google Spreadsheet does. I regularly go over the spreadsheet with a colleague to check whether we've made any mistakes. It helps that the both of us can immediately see any changes the other makes.
Have one huge-ass JavaScript function that iterates over rows and populates cells. Not feasible because:
1. Too error prone, it's very easy to make mistakes compared to my current method.
2. Does not update cells automatically until I re-run the script. I want to see any calculations immediately after I update other cells, just like a spreadsheet is supposed to do.
Using other spreadsheets like Excel and OpenOffice Calc. Not feasible because: they don't appear to offer the same scripting capabilities.
Writing my own financing app. Not feasible because: it takes too much take, it's not my core business, and tax rules change almost every year so I will have to constantly update the app. I can update a spreadsheet very quickly, but writing a financing app takes too much time.

Hongli Hongli · Accepted Answer · 2013-01-30T17:08:48

I solved it by making every function sleep for a random period, like this:

Utilities.sleep(Math.random() * 5000);

It is important that the sleeping time is random, not constant. Apparently Google limits the maximum number of functions that may simultaneously be using CPU.

How to avoid 'exec maxSimultaneous' limit in Google Spreadsheet?

2 Answers