Too many simultaneous invocations 50 different scripts and triggers

0
votes

I'm setting up an inventory management system to track stock. I have created a spread sheet with 50 columns adding the various different stock we carry. I have added a formula to subtract as the stock becomes used. In a particular cell i have a script and trigger to send me an email once a threshold has been reached.

The problem I'm having is that as I have 50 x script files and 50 x triggers I'm getting the error Too many simultaneous invocations:

below is an example of the scripts I have written, they are all the same but have different cells and message.

function SendEmail1() {
  // Fetch the monthly sales
 var monthSalesRange = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("edwards van 
stock").getRange("F5"); 
var monthSales = monthSalesRange.getValue();
var ui = SpreadsheetApp.getUi(); 
// Check totals sales
if (monthSales < 2){
// Fetch the email address
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Set 
Up (do NOT delete)").getRange("B3");
var emailAddress = emailRange.getValues();

// Send Alert Email.
var message = 'FP-C5E-001 ' + monthSales; // Second column
var subject = 'low on stock - place order';
MailApp.sendEmail(emailAddress, subject, message);
}
}

below are my triggers - again all of them the same but different function number.

Trigger

2
google-apps-scriptgoogle-sheetstriggersgoogle-workspace
You should combine all of the onEdit() function into one. Are all of your scripts like the one shown in your question? – Cooper
That doesn't seem like your complete function. Can you provide the rest? – Cooper
Function sendEmail1 is the first of 50, The second being SendEmail 2 this has a different stock item to the first, sendEmail3 again has a different stock item and so on and so on. – Edwards Sound
Cooper, i have updated my code screen shot, didnt realise it was missing some of it. thanks – Edwards Sound

2 Answers

1
votes

I think you could use a function with sections similar to this:

function onEdit(e) {   
  var sh=e.range.getSheet();
  if(sh.getName()=="edwards van stock" && e.range.columnStart==6 & e.range.rowStart==5) {
    if(e.value<2) { 
      var emailAddress=e.source.getSheetByName("Set Up (do NOT delete)").getRange("B3").getValue();
      var message="edward van stock < 2."
      var logsh=ss.getSheetByName("Email Alert Sheet");
      var ts=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "E MMM dd,yyyy HH:mm:ss");
      logsh.appendRow([ts,emailAddress,message,'Send Email']);
    }
  }
  if(sh.getName()=="debras van stock" && e.range.columnStart==6 & e.range.rowStart==6) {
    if(e.value<2) { 
      var emailAddress=e.source.getSheetByName("Set Up (do NOT delete)").getRange("B3").getValue();
      var message="debras van stock < 2."
      var logsh=ss.getSheetByName("Email Alert Sheet");
      var ts=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "E MMM dd,yyyy HH:mm:ss");
      logsh.appendRow([ts,emailAddress,message,'Send Email']);
    }
  }
}

They would append a message to a log sheet. The you would write another function that you could call periodically using a time based trigger and it could read the log and assemble all of the messages for the same recipient and send just one message to each recipient.

This function probably needs a little more work but I need to know more about you specific situation.

I just did a similar sort of function for another question: https://stackoverflow.com/a/59449214/7215091

1
votes

Answer:

You are getting too many invocation because you are hitting your quota. You should implement some form of exponential backoff to limit the number of simultaneous executions and/or triggers.

More Information:

According to the Quotas for Google Services documentation, you can have 30 simultaneous executions for scripts, with a limit of 20 triggers, per user, per script:

The table below lists hard limitations as of August 2018. The limits shown below are provided solely to assist you in testing scripts. All limits are subject to elimination, reduction, or change at any time, without notice.

enter image description here

Implementing exponential backoff:

Google Cloud documentation suggests using exponential backoff as a way of ensuring IoT devices do not generate excessive load, but the algorithms can be applied to all areas of computing where execution limitations are an issue.

From the documentation:

An exponential backoff algorithm retries requests exponentially, increasing the waiting time between retries up to a maximum backoff time. For example:

  1. Make a request.
  2. If the request fails, wait 1 + random_number_milliseconds seconds and retry the request.
  3. If the request fails, wait 2 + random_number_milliseconds seconds and retry the request.
  4. If the request fails, wait 4 + random_number_milliseconds seconds and retry the request.
  5. And so on, up to a maximum_backoff time.
  6. Continue waiting and retrying up to some maximum number of retries, but do not increase the wait period between retries.

Further information of an algorithm can be seen here.

I hope this is helpful to you!

References: