Too many simultaneous invocations: Spreadsheets

[Juan David Paladines]

I'm using this code to access to the information of a Spreadsheet with too much data. The error is shown in the "valores" variable, in getDataRange().

How can I optimize that function or which other function can I use to make the trigger work properly?

[Alan Wells]

There are a couple of things that you can try.

If your code is getting triggered often, and multiple times in rapid succession, then you should implement LockService.

https://developers.google.com/apps-script/reference/lock/lock-service?hl=en

Then there is the issue of services being used too often in a short period of time, and hitting the quota limit.

For that situation, you need to use a "backoff" technique in your code, where you try and then catch an error, wait and try again.

  var L = 4;//Try up to 3 times

  

  for (var i=1;i<L;i++) {

    try{

      //Your code here

      break;//If successful then the loop will break

    }catch(e){

      if (i!==L-1){Utilities.sleep(i*1500);}

      if (i>=L-1) {

        Logger.log('error: '  + e.error)

        //console.error('ERROR - : ' + e + ' Stack: ' + e.stack + "\n\n" + r);

      }

    };

  }

[Juan David Paladines]

Okay, in this case the code is running too often with an approximate of 10 excecutions... Would this code, the one you posted, still working?

[Juan David Paladines]

This is the actual amount of executions that the script has...

On Monday, May 24, 2021 at 11:12:02 AM UTC-5 aj.a...@gmail.com wrote:

[Alan Wells]

You should add the code that I gave you for the line those 3 lines of code that you posted.

Plus you should also add the LockService to your code.

You should do both.

If you want to try just the code that I posted, and see if that fixes the issue, you could do that.