CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgs-vlookup
user1806804
user1806804

Reputation: 11

maxSimultaneous

I am suddenly getting the following error across my google spreadsheet:

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

Most of the errors are appearing in cells that have vlookups in them. Some errors are appearing in cells that have my own custom function in, though most cells with the custom function work fine.

Any idea what exec maxSimultaneous is, how to avoid the error and what change happened about 2 days ago to google's policies 5/11/2012, for this to suddenly start causing problems?

Upvotes: 1

Views: 818

Answers (2)

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

Since the errors are appearing on the cells that have standard functions in them. This is not really an Apps Script issue, but a spreadsheet one.

But I'm pretty sure the problem is with your custom functions. Because unlike the built-in functions, the custom functions are not evaluated immediately and all at once. The spreadsheet triggers each one to run and when each returns the result seperately. Then, on each return the spreadsheet application has to recalculate all functions that depend on that cell, i.e. your vlookups. And because of this behavior, you're triggering too many vlookups recalculations concurrently and is hitting this maxSimultaneous limit.

My recommendation for you is the same one I give everybody when I'm explaining the problems with Apps Script custom functions (which go far beyond this issue). Don't use custom functions!

I know they look beautiful, simple to develop and easy to use. But they have lots of issues. Your spreadsheet will be much, much faster if you change your approach to a "standard" script. Where you trigger the calculations manually via a menu or image button, or automatically via an onEdit trigger.

Upvotes: 1

eddyparkinson
eddyparkinson

Reputation: 3700

The suggestion: use Utilities.sleep(1000) - tends to come up when you make many calls to GAS functions. I get this when I try and get the row height of 30 or so rows. I suspect the GAS functions that make a network call cause this.

A common cause would be using range.getValue rather than range.getValues

Also, I understand vlookups are slow, and database functions are faster.

Upvotes: 0

Related Questions