How to fix "Service invoked too many times in a short time: exec qps."

Question

I have a pretty large school setup to log meeting times and information on students that links to separate sheets.

• 10 School spreadsheets that principals use to input information, including a hyperlink to each student's individual calendar, and see attendance statistics returned from the calendars.
• 43 Tutor Spreadsheets that pull student data from the school spreadsheet based on tutor name.
• ~109 Student calendar spreadsheets with 9 tabs for the 9 months. Used to enter attendance info by the tutor.

Most of what is making everything link is a custom function that pulls out the URL from the hyperlink function. I found the code here and here. This function is used in the school spreadsheets as a unique identifier for each student.

I have tried putting in Utilities.sleep with various number up to and including 3000, but the errors eventually return (again sporadically). It appears that this could be an issue with the service being limited based on daily quotas, but I do not see exec qps specifically addressed.

/** 
 * Returns the URL of a hyperlinked cell, if it's entered with hyperlink command. 
 * Supports ranges
 * @param {A1}  reference Cell reference
 * @customfunction
 */
function linkURL(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  var formulas = range.getFormulas();
  var output = [];
  for (var i = 0; i < formulas.length; i++) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      var url = formulas[i][j].match(/=hyperlink\("([^"]+)"/i);
      row.push(url ? url[1] : '');
    }
    output.push(row);
    Utilities.sleep(2000);
  }
  return output
}

One error I am getting is 'TypeError: Cannot read property "1" from null. (line 16, file "Code")' which does not appear to necessarily affect the function execution. I am curious to know why this error is happening.

I do, however, get errors sporadically through different school spreadsheets that reads, "Service invoked too many times in a short time: exec qps. Try Utilities.sleep(1000) between calls. (line 0)." This diplays #Error throughout the spreadsheet and causes information to not display.

It appears that this could be an issue with the service being limited based on daily quotas, but I do not see exec qps specifically addressed. Is there a fix for this in such a large project? Is there something that the school system can upgrade to that would make the errors disappear?

Answer 1

Putting a sleep method in your for loop won't do anything to stop the quota error because that loop isn't using any services.

The services are:

sheet = SpreadsheetApp.getActiveSheet();
formula = SpreadsheetApp.getActiveRange().getFormula();

The services should be put into a for loop as shown below:

/** 
 * Returns the URL of a hyperlinked cell, if it's entered with hyperlink command. 
 * Supports ranges
 * @param {A1}  reference Cell reference
 * @customfunction
 */
function linkURL(reference) {
  var formula,i,sheet;

  for (i=1;i<4;i++) {//Retry up to 3 times - must begin at 1 for sleep calculation
    try{
      sheet = SpreadsheetApp.getActiveSheet();
      formula = SpreadsheetApp.getActiveRange().getFormula();
      break;
    }catch(e){
      if (i!==3){Utilities.sleep(i*1500);}//Wait an increasingly longer time on each iteration
      if (i>=3) {
        errorHandling_(e);//Call central error handling
      }
    };

  }

  if (!sheet) {//If there is no sheet then there is no point in running more code
    //An error has already been handled above
    return;
  }

  var args = formula.match(/=\w+\((.*)\)/i);

  try {
    var range = sheet.getRange(args[1]);
  }catch(e) {
    errorHandling_(e);
  }

  if (!range) {//If there is no range then there is no point in running the code below -
    //An error message has already been handled above
    return;
  }

  var formulas = range.getFormulas();
  var output = [];

  for (var i = 0; i < formulas.length; i++) {
    var row = [];
    for (var j = 0; j < formulas[0].length; j++) {
      var url = formulas[i][j].match(/=hyperlink\("([^"]+)"/i);
      row.push(url ? url[1] : '');
    }
    output.push(row);

  }
  return output
}

function errorHandling_(e) {
  var errorMessage,stack;
  //Handle all errors in one central place

  errorMessage = e.message;
  stack = e.stack;

  //Email the user?


  //Email the developer?

}