服务在短时间内被调用太多次：exec qps。 -Google表格 [英] Service invoked too many times in a short time: exec qps. - Google Sheets

问题描述

我一直在使用自定义函数将简单的数学分解为可读的JavaScript，但是出现以下错误：

I have been using custom functions to break out simple mathematics into readable JavaScript, but am getting the following error:

也调用了服务在短时间内多次：exec qps。在两次调用之间尝试 Utilities.sleep（1000）。 （第0行）。

我尝试了随机睡眠，但这无济于事。我的函数看起来像这样：

I have tried sleeping for a random time, but that doesn't help. My functions look like this:

function conversationGrowthRate(clientCount, initialGrowthRate, conversationCount) {  
  //Utilities.sleep(Math.random() * 30000);
  for (var i = clientCount; i > 10; i--) {
    if (initialGrowthRate > 0) {
      conversationCount += initialGrowthRate
      initialGrowthRate -= 0.000003
    }
  }
  return conversationCount;
}

function conversionGrowth(clientCount, conversationCount, initialConversionRate, conversionGrowthRate, maxConversionRate, coversPerBooking, initialConversationGrowthRate, initialConversationCount) {
  //Utilities.sleep(Math.random() * 30000);
  if (clientCount <= 50) {
    return coversPerBooking * conversationCount * initialConversionRate;
  }
  else {
    var firstFiftyClientConversations = conversationGrowthRate(50,initialConversationGrowthRate, initialConversationCount)*30*50; //~30
    var additionalConversionGrowthRate = (clientCount-50) * conversionGrowthRate;
    var totalConversionRate = initialConversionRate + additionalConversionGrowthRate;
    var additionalClientConversations = conversationGrowthRate(clientCount-50, initialConversationGrowthRate, initialConversationCount) * 30 * (clientCount-50);
    if (totalConversionRate < maxConversionRate) {
      return coversPerBooking * ((firstFiftyClientConversations * initialConversionRate) + (additionalClientConversations * totalConversionRate));
    }
    else {
      return coversPerBooking * (conversationCount * maxConversionRate); 
    }
  }
}

function salesProductivity(currentExecs, prevMonthExecs, prevTwoMonthExecs, prevThreeMonthExecs, prevFourMonthExecs, salesPerExecPerMonth) {
  //Utilities.sleep(Math.random() * 30000);
  var firstMonthHires = currentExecs - prevMonthExecs;
  var secondMonthHires = prevMonthExecs - prevTwoMonthExecs;
  var thirdMonthHires = prevTwoMonthExecs - prevThreeMonthExecs;
  var fourthMonthHires = prevThreeMonthExecs - prevFourMonthExecs;
  var longerHires = prevFourMonthExecs;

  return (secondMonthHires * (0.33 * salesPerExecPerMonth)) + (thirdMonthHires * (0.66 * salesPerExecPerMonth)) + (fourthMonthHires * (1 * salesPerExecPerMonth)) + (longerHires * salesPerExecPerMonth);
}

在开始工作之前，我什么都没做。

I changed nothing before it started working.

推荐答案

Google表格通过名为的服务远程运行自定义功能 exec 。如果对任何自定义函数的调用过多（根据一些未记录的配额，或者至少我没有找到任何此类文档），将发出此错误。

Google Sheets runs custom functions remotely through a service called exec. If there are "too many" calls (according to some undocumented quota, or, at least, I haven't found any such documentation) to any custom functions, this error will be emitted.

如果由于许多单元正在调用自定义函数而导致调用发生，我不明白 Utilities.sleep（milliseconds）有何帮助，因为 exec 将在 sleep 之前调用（实际上，它将是 exec 将直接或间接调用 sleep ）。

If the calls are happening because many cells are calling the custom functions, I don't see how Utilities.sleep(milliseconds) can help since exec would be called before sleep is (indeed, it'll be exec that'll be calling sleep whether directly or indirectly).

如某些评论所述，转换函数接受范围和返回数组（并更改工作表以容纳所述功能）是一种方法（直到Google添加更智能的调节）。

As some comments have mentioned, converting the function to accept ranges and return arrays (and changing the sheet to accommodate said function) is the way to go (until Google add smarter throttling).

这篇关于服务在短时间内被调用太多次：exec qps。 -Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！