Google Sheets JSON错误:“一天调用的服务次数过多:" [英] Google Sheets JSON Error: "Service invoked too many times for one day:"

问题描述

我必须将经度和纬度转换为有用的地址(反向地理编码)，我在[Geo Scipt] [1]找到了一个不错的脚本

I have to convert longitude and latitude, into a useful address (reverse geocode), I found a good script at [Geo Scipt][1]

我每天遇到太多请求，我的问题是，如何才能只运行一次(从ifttt导入数据)，而不是每次打开此Google工作表?

I am having an issue with too many requests per day, my question is how can I have this run only once (when the data is imported from ifttt) and not every time I open this google sheet?

function getLat(address) {
  if (address == '') {
    Logger.log("Must provide an address");
    return;
  }
  var geocoder = Maps.newGeocoder();
  var location;
    // Geocode the address and plug the lat, lng pair into the
    // 2nd and 3rd elements of the current range row.
    location = geocoder.geocode(address);
    // Only change cells if geocoder seems to have gotten a
    // valid response.
    if (location.status == 'OK') {
      lat = location["results"][0]["geometry"]["location"]["lat"];
 return lat;
    }
};

function getLon(address) {
  if (address == '') {
    Logger.log("Must provide an address");
    return;
  }
  var geocoder = Maps.newGeocoder();
  var location;
    // Geocode the address and plug the lat, lng pair into the
    // 2nd and 3rd elements of the current range row.
    location = geocoder.geocode(address);
    // Only change cells if geocoder seems to have gotten a
    // valid response.
    if (location.status == 'OK') {
      lng = location["results"][0]["geometry"]["location"]["lng"];
 return lng;
  }
};

function getAdd(lat, lng) {
// Return Address by taking the coordinates and reverse geocoding.
if (lat == "") {
return "You have to provide latitudinal coordinates to the place"
} if (lng == ""){
return "You have to provide longitudinal coordinates to the place"
}

var response = Maps.newGeocoder().reverseGeocode(lat, lng); //Call the reverse Geocode service
for (var i = 0; i < response.results.length; i++) {
var result = response.results[i];

return result.formatted_address; //Output full address in cell
Utilities.sleep(Math.random() * 500);
  }
};

推荐答案

服务在一天中被调用太多次表示给定的服务超过了一天的总允许执行时间.最常见的情况是在触发器上运行的脚本，其每日限制低于手动执行的脚本.尝试 使用Fusion Tables

The Service invoked too many times for one day indicates that the given service exceed the total allowable execution time for one day. It most commonly occurs for scripts that run on a trigger, which have a lower daily limit than scripts executed manually. Instead of Google Sheet, try to use Fusion Tables

每次在电子表格中使用自定义功能时，Google表格都会分别调用Apps脚本服务器.如果您的电子表格包含数十个(或数百个或数千个！)自定义函数调用，则此过程可能会很慢.使用 Maps API和Fusion Table

Each time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your spreadsheet contains dozens (or hundreds, or thousands!) of custom function calls, this process can be quite slow. It could be helpful to read Map your data with the Maps API and Fusion Table

如果脚本达到配额或限制，它将引发异常并显示一条消息，有关此的更多信息，请访问以下链接:

If a script reaches a quota or limitation, it will throw an exception with a message, for more information regarding this, follow this link: https://developers.google.com/apps-script/guides/services/quotas#current_limitations

这篇关于Google Sheets JSON错误:“一天调用的服务次数过多:"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！