Why would I get the error intermittently: Too many simultaneous invocations: Spreadsheets on an trigger using GAS

[Micah Kuiper]

The script hasn't changed except for trying to add delays to prevent "simultaneous invocaitions" and it used to run just fine, but now in the past week or so it's failing more and more often. There are only 3 triggers on this file onOpen, onEdit(e) and the installed trigger that calls the percentChange function once a day in the morning. It's super simple, it grabs 2 named ranges, one is a column of percentages and the other contains the length of that data and then copies the percentages to another column so that we can track the daily percentage difference.

function percentChange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Overview Status');

  var calcPercentRangeObject = ss.getRange('calcPerc');
  var calcPercentRow = calcPercentRangeObject.getRow()+2;
  var calcPercentColumn = calcPercentRangeObject.getColumn();

  var dataRangeObject = ss.getRange('numCabs');
  var dataRow = dataRangeObject.getRow()+2;
  var dataColumn = dataRangeObject.getColumn();
  var dataLength =  dataRangeObject.getValue();
  var currentPercent = ss.getRange(calcPercentRow, calcPercentColumn, dataLength, 1).getValues();

  ss.getRange(dataRow, dataColumn, dataLength, 1).setValues(currentPercent);

}

More details over on stack exchange: https://webapps.stackexchange.com/questions/130306/why-would-i-be-getting-the-error-too-many-simultaneous-invocations-spreadsheet

[Alan Wells]

You have an "onOpen" trigger that shows that it's currently running, and that it has been running for 5 days!

The duration is:

419474 seconds

Or about 5 days

Interestingly, the absolute most that a script should be allowed to run is 30 minutes for a G Suite account.  So, the Apps Script servers are failing to stop that instance of the function from running.  You can manually stop an instance of a function from running by clicking the icon of the 3 dots, and clicking "Terminate."

Why an execution would be allowed to keep running, I don't know, except that there is a bug at Google that is failing to correctly monitor the duration of the execution.

[Micah Kuiper]

I believe I killed that one, I think that happens occasionally if you run the debugger, and never stop it with a debug point set.

[Alan Wells]

Do you have a situation where multiple users open the spreadsheet at the same time?

[Micah Kuiper]

I mean technically, but the error is showing up on a once a day trigger event at a time when no one is viewing the spreadsheet.

[Alan Wells]

Unfortunately, without some information from Google, it seems like all we can do is guess.  If it's a bug at Google, then I don't know how we'd ever figure out what the real problem is.  I know what you're saying, if it's an issue with lots of users opening the spreadsheet at the same time, then you'd think that the error would happen when they were using it, not at some point later.  But, the "bottom line" is, that if a Google Sheet doesn't work for you in your situation, you may need to use something else.

You could try using a Web App, if this is for something like employees clocking in and out at a certain time.  Or you could use a Google Form linked to a Sheet, and then process the data later, after the usage load has stopped.  If users need immediate feedback of something, then you need something that can handle the usage.  I don't know if a firebase database would be better?