CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggersgoogle-workspace
Edwards Sound
Edwards Sound

Reputation: 1

Too many simultaneous invocations 50 different scripts and triggers

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:
Trigger

Upvotes: 0

Views: 762

Answers (2)

Rafa Guillermo
Rafa Guillermo

Reputation: 15375

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.

References:

Upvotes: 1

Cooper
Cooper

Reputation: 64120

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

Upvotes: 1

Related Questions