问题描述 #

我在Google Sheets里有个包含963条客户地址的数据库，想通过地理编码生成经纬度，之后导入Fusion Tables做地图可视化。但运行一次下面的脚本时，刚处理到第761条就触发了Service invoked too many times for one day: geocode错误。我知道Geocoding API的每日限额是2500次，而且之前完全没使用过这个API，代码检查了好几遍也没发现单次循环里多次调用API的情况，实在搞不懂为什么会提前触顶，想找到原因并解决这个问题。

我的代码 #

function geocodeFirstAddressFromCell(){ 
  var sss = SpreadsheetApp.openById('WORKBOOK ID HERE'); 
  var ss = sss.getSheetByName('Sheet1'); 
  var lastRow = ss.getLastRow(); 
  for (var startRow = 2; startRow < lastRow; startRow++) { 
    var addressCellData = ss.getRange(startRow, 13).getValue(); 
    var results = Maps.newGeocoder().geocode(addressCellData); 
    if (results.status == 'OK') { 
      var bestResult = results.results[0]; 
      var lat = bestResult.geometry.location.lat; 
      var lng = bestResult.geometry.location.lng; 
      var latLng = lat + ',' + lng; 
      var addressTargetCellData = ss.getRange(startRow, 16).setValue(latLng); 
      ss.getRange(startRow, 17).setValue(1); 
    } else { 
      Logger.log(results.status); 
      return '0,0'; 
    } 
  } 
}

问题分析与解决方案 #

别着急，这个问题其实挺常见的，我帮你拆解下可能的原因和解决办法：

1. 核心原因：你混淆了两种不同的API配额！ #

你提到的2500次/天是公开Geocoding REST API的限额，但你用的Maps.newGeocoder()是Google Apps Script内置的封装服务，它走的是另一个配额池：

• 对于免费消费者账号，Apps Script地理编码服务的每日限额是750次/天，刚好和你触发错误的761次接近（可能还有几次测试调用的消耗）。
• 这个细节很容易被忽略，Google文档里经常没把这两个配额明确区分开，导致很多人踩坑。

2. 代码里的小瑕疵：提前终止循环 #

看你的代码，else分支里有个return '0,0';——这意味着只要遇到任何一个地址解析失败（比如地址无效、返回ZERO_RESULTS），整个脚本就会立刻停止运行。虽然这不是触发限额的原因，但会导致你处理不完所有数据，建议改成continue;跳过当前行，继续处理下一条：

else { 
  Logger.log(results.status); 
  // 把return改成continue，避免提前终止循环
  continue; 
}

3. 避免触发限额的解决办法 #

方法一：拆分任务，分多天处理

如果是免费账号，每天最多跑750次，那你可以把963条数据分成两天处理：

• 第一天修改循环为for (var startRow = 2; startRow <= 751; startRow++)（从第2行开始，共750条数据）
• 第二天从752行开始处理剩余的213条

方法二：改用批量地理编码（更高效）

公开Geocoding API支持批量请求（最多一次10个地址），这样963条数据只需要97次调用，远低于2500的限额。不过需要你先申请API密钥并启用Geocoding API（免费额度足够，只需绑定信用卡验证），示例代码如下：

function batchGeocode() {
  var sss = SpreadsheetApp.openById('WORKBOOK ID HERE');
  var ss = sss.getSheetByName('Sheet1');
  var lastRow = ss.getLastRow();
  var apiKey = '你的Geocoding API密钥';
  var batchSize = 10;
  
  for (var i = 2; i <= lastRow; i += batchSize) {
    var endRow = Math.min(i + batchSize - 1, lastRow);
    var addresses = [];
    // 收集当前批次的地址
    for (var j = i; j <= endRow; j++) {
      addresses.push(ss.getRange(j, 13).getValue());
    }
    // 构建批量请求URL
    var encodedAddresses = addresses.map(addr => encodeURIComponent(addr)).join('|');
    var url = `https://maps.googleapis.com/maps/api/geocode/json?address=${encodedAddresses}&key=${apiKey}`;
    var response = UrlFetchApp.fetch(url);
    var results = JSON.parse(response.getContentText());
    
    // 处理返回结果
    for (var k = 0; k < results.results.length; k++) {
      var row = i + k;
      if (results.results[k].status == 'OK') {
        var lat = results.results[k].geometry.location.lat;
        var lng = results.results[k].geometry.location.lng;
        ss.getRange(row, 16).setValue(`${lat},${lng}`);
        ss.getRange(row, 17).setValue(1);
      } else {
        Logger.log(`行${row}解析失败: ${results.results[k].status}`);
      }
    }
    // 避免请求太频繁，加1秒延迟
    Utilities.sleep(1000);
  }
}

方法三：升级到Google Workspace账号

如果是团队使用，升级到Workspace账号后，Apps Script地理编码的限额会提升到10000次/天，一次性处理963条数据完全没问题。

额外优化：提升脚本性能 #

你的原脚本每次循环都调用getRange()，会消耗大量Spreadsheet服务配额，还会变慢。建议一次性读取所有地址，处理完后再批量写入结果：

function optimizedGeocode() {
  var sss = SpreadsheetApp.openById('WORKBOOK ID HERE');
  var ss = sss.getSheetByName('Sheet1');
  var lastRow = ss.getLastRow();
  // 一次性读取所有地址和要写入的列
  var addressRange = ss.getRange(2, 13, lastRow - 1, 1);
  var addresses = addressRange.getValues();
  var latLngResults = [];
  var statusResults = [];
  
  for (var i = 0; i < addresses.length; i++) {
    var addr = addresses[i][0];
    var results = Maps.newGeocoder().geocode(addr);
    if (results.status == 'OK') {
      var lat = results.results[0].geometry.location.lat;
      var lng = results.results[0].geometry.location.lng;
      latLngResults.push([`${lat},${lng}`]);
      statusResults.push([1]);
    } else {
      Logger.log(`行${i+2}解析失败: ${results.status}`);
      latLngResults.push(['0,0']);
      statusResults.push([0]);
    }
    // 每处理50条加1秒延迟，避免触发速率限制
    if (i % 50 == 0) {
      Utilities.sleep(1000);
    }
  }
  // 批量写入结果
  ss.getRange(2, 16, lastRow - 1, 1).setValues(latLngResults);
  ss.getRange(2, 17, lastRow - 1, 1).setValues(statusResults);
}

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