Prevent "Too many simultaneous invocations" on formSubmit

Question

Currently we have a Google Form that is automatically uploaded and batched in installments of ~50 forms. These come into a spreadsheet and need to be acted upon by a trigger that subscribes to the onFormSubmit event.

The problem is that since these are all uploaded simultaneously, the event fires rapidly back to back and come up with the either of the following warnings:

Too many simultaneous invocations: Spreadsheets (line 16, file "Code")
We're sorry, a server error occurred. Please wait a bit and try again. (line 12, file "Code")

According to the Daily Quotas Limit, there's a max of 20 scripted triggers per day.

That's a perfectly reasonable number, so long as I can find a way to run a single trigger for all 50 back to back form submissions at the very end.

It would be great if I could set a timeout for the execution to wait and see if any other events were fired in the next couple seconds, but I would need to be inside of a trigger handler by the time I'm executing the trigger.

Q: Is there anyway to batch Form Submission Triggers?

Answer 1

The problem is you seem to be creating a trigger to handle each form submit. If the handler is short and simple, do it right then onFormSubmit. If that would cause problems, instead have a single trigger that runs every 10 minutes and checks if there is work to do by remembering the last row processed and reading new rows directly from the spreadsheet. I say 10 minutes so there is no chance of overlapping one trigger instance with the next. If you use a shorter trigger make sure that parts that cannot run in parallel with a lock.

Answer 2

You could use the class FormTriggerBuilder to dynamically create and destroy triggers. You can use the PropertiesService to persist the id for the created trigger and then programmatically delete it later like this:

var formID = 'XXXXXXX';
var properties = PropertiesService.getScriptProperties()

function onInstall() {
  createSubmitHandler()
}

function createSubmitHandler() {
 var form = FormApp.openById(formID);
 var trigger = ScriptApp.newTrigger('onSubmit')
     .forForm(form)
     .onFormSubmit()
     .create();

  properties.setProperty('triggerID', trigger.getUniqueId())
}

function destroySubmitHandler() {
   var triggerID = properties.getProperty('triggerID')
   var form = FormApp.openById(formID);
   var allTriggers = ScriptApp.getUserTriggers(form)
   for (var i = 0; i < allTriggers.length; i++) {
     if (allTriggers[i].getUniqueId() == triggerID) {
       ScriptApp.deleteTrigger(allTriggers[i]);
       break;
     }
   }
}

function onSubmit() {
  // we've been called, remove trigger, set timeout, re-enable, and then run function
  destroySubmitHandler();
  Utilities.sleep(5 * 1000)
  createSubmitHandler();
  myFunction()
}

function myFunction() {
  // do stuff here
}

---

Alternatively, as ScampMichael pointed out, you could use the Lock Service. There's a great write up of it in this article on Concurrency and Google Apps Script. However, I'm not sure that's what I want here. The lock service will queue code waiting to be executed. Which solves the problem of concurrent executions, but not the problem of hitting daily quotas of only 20 triggers per script, depending on how the quotas are measures.

Here's an example of how a lock script could be used to prevent concurrent invocation:

function onSubmit() {
  // we've been called, get a public lock, wait for other job to finish, run the function, and release the cracken
  var lock = LockService.getScriptLock();
  lock.tryLock(30 * 1000);
  myFunction();
  lock.releaseLock();
}

function myFunction() {
  // do stuff here
}