javascript - 如何修复 "Service invoked too many times in a short time: exec qps."

Question

我有一个相当大的学校设置来记录 session 时间和链接到单独工作表的学生信息。

• 校长用来输入信息的 10 个学校电子表格，包括每个学生个人日历的超链接，以及查看从日历返回的出勤统计数据。
• 43 个导师电子表格，根据导师姓名从学校电子表格中提取学生数据。
• ~109 个学生日历电子表格，其中 9 个选项卡用于 9 个月。用于导师录入考勤信息。

使所有内容链接的大部分内容是一个自定义函数，它从超链接函数中提取 URL。我找到了代码 here和 here .此函数在学校电子表格中用作每个学生的唯一标识符。

我已经尝试在 Utilities.sleep 中放入各种数字，包括 3000，但错误最终返回(再次偶尔)。看来这可能是服务受到每日配额限制的问题，但我没有看到具体解决 exec qps。

/** 
 * 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
}

我遇到的一个错误是“TypeError:无法从 null 中读取属性“1”。 (第 16 行，文件“代码”)'，这似乎不一定会影响函数执行。我很想知道为什么会发生此错误。

但是，我确实在不同学校的电子表格中偶尔会遇到错误，这些电子表格显示为“服务在短时间内调用了太多次:exec qps。在调用之间尝试 Utilities.sleep(1000)。(第 0 行)。”这会在整个电子表格中显示#Error 并导致信息不显示。

看来这可能是服务受到每日配额限制的问题，但我没有看到具体解决的 exec qps。在这么大的项目中有解决这个问题的方法吗？学校系统是否可以升级到可以使错误消失的东西？

Answer 1

在 for 循环中放置一个 sleep 方法不会阻止配额错误，因为该循环没有使用任何服务。

服务是:

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

服务应放入 for 循环中，如下所示:

/** 
 * 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?

}