如何让Google表格反向地理编码脚本支持ArrayFormula数组公式调用

原有代码问题 #

你现在用的单值版函数只支持单个经纬度传入，直接套ArrayFormula会出现两个问题：一是无法正确遍历整列数据批量返回结果，二是会对所有空行发起无效地理编码请求，白白消耗接口配额、拖慢表格运行速度。

修改后可直接使用的完整代码 #

把Apps Script里的原有代码全部替换为下面的内容即可：

function reverse_geocode(lat, lng) {
  // 识别数组公式传入的整列数据场景
  if (Array.isArray(lat)) {
    const output = [];
    for (let i = 0; i < lat.length; i++) {
      const rowLat = lat[i][0];
      const rowLng = Array.isArray(lng) ? lng[i][0] : lng;
      // 空行/非数值坐标直接返回空值，不触发接口请求
      if (!rowLat || !rowLng || isNaN(rowLat) || isNaN(rowLng)) {
        output.push([""]);
        continue;
      }
      // 有效坐标才执行反向地理编码
      Utilities.sleep(1500);
      const res = Maps.newGeocoder().reverseGeocode(rowLat, rowLng);
      if (res.results && res.results.length > 0) {
        output.push([res.results[0].formatted_address]);
        Logger.log('%s: %s, %s', res.results[0].formatted_address, rowLat, rowLng);
      } else {
        output.push([""]);
      }
    }
    return output;
  }

  // 兼容单个单元格调用的场景
  if (!lat || !lng || isNaN(lat) || isNaN(lng)) return "";
  Utilities.sleep(1500);
  const singleRes = Maps.newGeocoder().reverseGeocode(lat, lng);
  for (let i = 0; i < singleRes.results.length; i++) {
    const addr = singleRes.results[i];
    Logger.log('%s: %s, %s', addr.formatted_address, addr.geometry.location.lat, addr.geometry.location.lng);
    return addr.formatted_address;
  }
  return "";
}

使用方式 #

• 替换代码后保存脚本，按照提示完成Google账号授权即可
• 回到表格，在需要输出地址的列首行直接输入你预期的数组公式，公式会自动逐行判断，仅对存在有效经纬度的行执行转换：

=ArrayFormula(reverse_geocode(A1:A,B1:B))

逻辑说明 #

• 完全兼容原有单单元格调用和数组公式批量调用两种用法，不需要修改已有使用习惯
• 内置空值/无效值判断，无有效经纬度的空行不会触发地理编码请求，不占用服务配额
• 保留了1500ms的请求间隔，避免短时间请求过多触发Google地理编码服务的频率限制
• 输出逻辑和原函数一致，优先返回匹配度最高的格式化地址

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