Google Sheets脚本调用Maps API触发日调用限制问题求助

一、API调用超限的解决方案：添加缓存机制 #

你遇到的Service invoked too many times for one day: premium urlfetch错误，是因为Google对UrlFetch服务的每日调用量有限制，重复请求相同地址会快速耗尽配额。缓存机制可以复用已请求过的结果，避免无效请求。

实现逻辑： #

• 用Google Apps Script的CacheService存储地址查询结果，有效期设为1天（86400秒）
• 处理每个地址前先检查缓存，有数据直接读取；无数据再发起API请求，请求成功后将结果存入缓存

二、菜单调用的替代方案 #

onOpen函数不是必须的，你可以选择更便捷的运行方式：

• 直接在脚本编辑器运行：打开脚本编辑器，选中findAddress函数，点击顶部运行按钮即可
• 保留菜单（适合多用户场景）：如果需要给其他协作者使用，自定义菜单是友好的入口；若仅自己使用，直接运行更高效

三、修改后的完整脚本 #

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('自定义菜单')
      .addItem('获取地址信息', 'findAddress')
      .addToUi();
}

function findAddress() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var API_KEY = "INSERT API KEY HERE"; // 修正原拼写错误
  var cache = CacheService.getScriptCache(); // 初始化缓存服务

  for (var i = 1; i < values.length; i++) { // 跳过表头，从第二行开始处理
    var address = values[i][0];
    if (!address) continue; // 空地址直接跳过，避免无效请求

    var cacheKey = "addr_" + address.trim();
    var cachedResult = cache.get(cacheKey);

    if (cachedResult) {
      // 直接使用缓存数据
      var result = JSON.parse(cachedResult);
      values[i][1] = result.lat; // B列：纬度
      values[i][2] = result.lng; // C列：经度
      values[i][3] = result.name; // D列：公司名称
      values[i][4] = result.formattedAddress; // E列：格式化地址
      continue;
    }

    // 缓存不存在时发起API请求
    var searchUrl = "https://maps.googleapis.com/maps/api/place/textsearch/json?query=" 
              + encodeURIComponent(address) 
              + "&key=" + API_KEY;
    try {
      var searchResponse = UrlFetchApp.fetch(searchUrl);
      var searchData = JSON.parse(searchResponse.getContentText());

      if (searchData.status == "OK") {
        var location = searchData.results[0].geometry.location;
        var lat = location.lat;
        var lng = location.lng;
        var placeId = searchData.results[0].place_id;

        var detailsUrl = "https://maps.googleapis.com/maps/api/place/details/json?place_id=" 
                          + placeId 
                          + "&fields=name,formatted_address&key=" + API_KEY;
        var detailsResponse = UrlFetchApp.fetch(detailsUrl);
        var detailsData = JSON.parse(detailsResponse.getContentText());

        if (detailsData.status == "OK") {
          var name = detailsData.result.name;
          var formattedAddress = detailsData.result.formatted_address;

          // 写入结果到数组
          values[i][1] = lat;
          values[i][2] = lng;
          values[i][3] = name;
          values[i][4] = formattedAddress;

          // 将结果存入缓存，有效期1天
          var cacheValue = JSON.stringify({
            lat: lat,
            lng: lng,
            name: name,
            formattedAddress: formattedAddress
          });
          cache.put(cacheKey, cacheValue, 86400);
        } else {
          values[i][1] = "详情请求失败";
          Logger.log("详情请求失败：" + detailsData.status);
        }
      } else {
        values[i][1] = "地址未找到";
        Logger.log("地址搜索失败：" + searchData.status);
      }
    } catch (e) {
      values[i][1] = "请求错误：" + e.message;
      Logger.log("请求异常：" + e.message);
    }
  }
  
  range.setValues(values); // 将更新后的数据批量写回表格
}

脚本修改说明： #

1. 修复了原代码中的拼写错误（如INSER SPI KEY HERE改为INSERT API KEY HERE）
2. 添加缓存逻辑，避免重复请求相同地址，减少API调用量
3. 移除了会终止循环的return语句，改为在单元格标记错误，确保所有行都能处理
4. 修正了注释中的列名错误（原注释把C列误写为B列）
5. 增加空地址跳过处理，避免无效请求
6. 用try-catch捕获请求异常，防止单个请求失败导致整个脚本终止

内容的提问来源于stack exchange，提问作者Philip Suffren