CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
onit
onit

Reputation: 2372

GmailApp error: Invoked Too Many Times in a certain context, but not in another

When I try it plain and simple, it goes through, proving my I haven't exceeded my daily quota:

function email() {
var now = new Date();
GmailApp.sendEmail("[email protected]", "current time", "The time is: " + now.toString());
}

However, when the user is ticks a checkbox in a row, the script gets some of that row's data to compose an email to be sent. This runs on edit basis (Installable Trigger) and it throws the am error:

function sendEmail() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getActiveSheet();
    //Escapes the script if not in the right tab
    if (sheet.getName() != 'Todays Tests V2') {
      return;
    }
    //Defines ranges/criteria
    var testSheet = ss.getSheetByName("Todays Tests V2");
    var row = sheet.getActiveCell().getRow();
    var col = sheet.getActiveCell().getColumn();
    var sendResults = testSheet.getRange(row, 15, 1, 1).getValue();
    //Gets data to compare against row getting a checkbox ticked/script fired
    var dbSheet = ss.getSheetByName('Database');
    var dbDataRng = dbSheet.getRange(2, 13, dbSheet.getLastRow() - 1, 2);
    var dbData = dbDataRng.getValues();
    
    if (ss.getActiveSheet().getSheetName() == testSheet.getSheetName() &&
      row > 5 &&
      col == 15 &&
      sendResults == true) {

      var lock = LockService.getScriptLock();
      try {
        lock.tryLock(4000); // wait 04 seconds for others' use of the code section and lock to stop and then proceed
      } catch (e) {
        Logger.log('Could not obtain lock after 04 seconds.');
        return HtmlService.createHtmlOutput("<b> Server Busy. Please try after some time <p>");
      }
      //Defines variables from the row being edited
      var email = testSheet.getRange(row, 5).getValue();
      var fullName = testSheet.getRange(row, 3).getValue() + ' ' + testSheet.getRange(row, 4).getValue();
      var testNo = testSheet.getRange(row, 2).getValue();
      //Sends the email
      GmailApp.sendEmail(email, "SUBJECT", fullName + " blah blah blah.", { name: 'YOUR SERVICE PROVIDER' });
      
      for (var n = 0; n < dbData.length; n++) {
        if (dbData[n][0] == testNo) {
          dbSheet.getRange('N' + (2 + n)).setValue('Yes');
        }
      }
      lock.releaseLock();
    }
  } catch (err) {
    Logger.log('Erro: ' + err)
    testSheet.getRange(row, 15).setValue('FALSE');
    ss.toast('Error: ' + err);
  }
}

The log shows email and fullName correctly. Appreciate any light shed to the possibility causing this.

Upvotes: 0

Views: 91

Answers (1)

Wicket
Wicket

Reputation: 38130

Use getRemainingDailyQuota() to check the effective user remaining daily email quota.

if(MailApp.getRemainingDailyQuota() > 1){
  // put here your send email statements
} else {
  // put here what you want to do when there is no remaining daily quota
}

Also you might try to something similar to track the total runtime of your installable triggers.

From https://developers.google.com/apps-script/guides/services/quotas

Feature Consumer (e.g., gmail.com)
and G Suite free edition (legacy)		 Google Workspace
accounts
Triggers total runtime 90 min / day 6 hr / day

To be clear, script quotas depend on the effective user, not on the script owner.

If the script is ran from a custom menu or simple trigger the active user and the effective user are the same.

If the script is triggered by an installable trigger, the effective user is the user who installed the trigger, not the script owner either the user who is editing / making changes to the spreadsheet i.e. by clicking a checkbox.

Scripts ran from the editor, from a custom menu, a button (image with a script assigned) or a dialog / sidebar haven't a total runtime quota.

By the other hand when using checkboxes or cell edits avoid doing changes very fast, i.e. avoid clicking checkboxes one after the other immediately, after clicking on a checkbox wait few seconds before clicking the next one.

Related

Upvotes: 1

Related Questions