如何在函数调用之间放置Utilities.sleep（）？ [英] How do I put Utilities.sleep() between function calls?

问题描述

我有一个计算两个坐标之间距离的函数，如下所示：

I have a function that is calculating the distance between two coordinates as follows:

function distance(origin, destination) {
  //Utilities.sleep(Math.random() * 60000);
  destination = destination + "";
  if (destination.split(",").length == 2) {
    var directions = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .getDirections();
    if (directions.routes.length > 0) {
      return directions.routes[0].legs[0].distance.value / 1000;
    }
    return "-";
  }
  return 0;
}

它用于电子表格中，如下所示：

It is used in the spreadsheet as follows:

=distance("83.342353,23.23353", V2)

该函数运行正常，但由于我的电子表格有超过200行，因此会触发url调用的速率限制。错误消息是：

The function runs fine but hits the rate limit for url calls since my spreadsheet has more than 200 rows. The error message is:

在短时间内调用服务的次数太多：urlfetch受保护的主机rateMax。尝试调用之间的Utilities.sleep（1000）。 （第5行）

Service invoked too many times in a short time: urlfetch protected host rateMax. Try Utilities.sleep(1000) between calls. (line 5)

建议的解决方法是将 Utitlies.sleep（）在代码中。在哪里我应该把这个？

The fix suggested is to put a Utitlies.sleep() in the code. Where exactly should I put this?

推荐答案

这里的问题是，即使你把 Utilities.sleep 在那里，函数仍然被同时调用几次。这正是Google工作表通常所做的。它并行运行功能来加快速度。
所以你需要强制它按顺序运行，你可以通过锁来实现。
eg

The problem here is that even if you put Utilities.sleep in there, the function is still being called several times concurrently. That's what Google sheets does normally. It runs functions in parallel to speed things up. So you need to force it to run in sequence which you can achieve with a lock. e.g.

function distance(origin, destination) {
  destination = destination + "";
  if (destination.split(",").length == 2) {
    var lock = LockService.getScriptLock();
    lock.waitLock(20000);
    Utilities.sleep(500);
    var directions = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .getDirections();
    lock.releaseLock();
    if (directions.routes.length > 0) {
      return directions.routes[0].legs[0].distance.value / 1000;
    }
    return "-";
  }
  return 0;
}

然而，测向器服务的配额相对较低。也许每天只有1000次使用。所以你可能仍然遇到问题。

However the quota on the direction finder service is relatively low. Maybe just 1000 uses per day. So you may still run into problems.

这篇关于如何在函数调用之间放置Utilities.sleep（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！