Google Sheets中DRIVEDIST函数origin参数报错及自动调用问题

错误原因分析 #

1. Origin参数无效：

   • 未对输入参数做校验，空值或格式不合法的地址直接传入Maps服务，导致识别失败；
   • Maps服务配额耗尽或未授权，无法处理地理编码/路线请求；
   • 传入的地址格式不符合Maps服务要求（比如不完整地址、无效经纬度）。

2. 函数无法自动调用：

   • 函数存在未捕获的异常，导致Google Sheets无法正常触发计算；
   • 参数类型不匹配（比如传入单元格区域而非单个单元格值）；
   • 脚本未正确保存或授权，导致函数未被注册到Sheets中。

修复方案 #

1. 增加参数校验与异常处理 #

在函数开头添加参数合法性检查，明确提示错误信息，避免无效请求发送到Maps服务：

• 检查origin、destination是否为空；
• 检查unit参数是否为支持的类型；
• 优化错误返回，让用户能直接看到问题所在（而非返回null）。

2. 确保Maps服务权限与配额 #

• 首次运行函数时，确认已授权脚本访问Maps服务；
• 查看Google Cloud Console的Maps API配额，确保未超出限制（免费配额有限，高频使用需升级）。

3. 优化函数触发自动计算 #

• 确保函数参数为单个值（而非数组），如果需要处理区域，可添加数组遍历逻辑；
• 避免函数命名与Google Sheets内置函数冲突（当前命名无冲突，但需确认）。

修复后的完整代码 #

// Distance functions modified from originals by github.com/rvbautista
// DRIVEDIST modified from bpwebs.com
// GEODIST modified from labnol.org
// Please make attribution to the original authors when using this code.

function DRIVEDIST(origin, destination, unit) {
  try {
    // 参数校验
    if (!origin || !destination) {
      throw new Error("Origin或Destination参数不能为空");
    }
    if (!unit) {
      throw new Error("必须指定单位参数：km, m, mi, ft, nm");
    }

    var directions = Maps.newDirectionFinder()
      .setOrigin(origin)
      .setDestination(destination)
      .setMode(Maps.DirectionFinder.Mode.DRIVING)
      .getDirections();

    // 检查路线是否存在
    if (!directions.routes || directions.routes.length === 0 || !directions.routes[0].legs || directions.routes[0].legs.length === 0) {
      throw new Error("无法找到两地之间的驾车路线");
    }

    var distance = directions.routes[0].legs[0].distance.value;
    switch (unit.toLowerCase()) { // 兼容大小写
      case "km":
        distance = distance / 1000;
        return Number(distance.toFixed(2));
      case "m": 
        return Number(distance.toFixed(2));
      case "mi":
        distance = distance / 1609.34;
        return Number(distance.toFixed(2));
      case "ft":
        distance = distance * 3.28084;
        return Number(distance.toFixed(2));
      case "nm":
        distance = distance / 1852;
        return Number(distance.toFixed(2));
      default:
        throw new Error("无效单位，支持的单位：km, m, mi, ft, nm");
    }

  } catch (error) {
    Logger.log("DRIVEDIST错误：" + error.message);
    return "#错误：" + error.message; // 返回明确错误提示
  }
}

function GEODIST(origin, destination, unit) {
  try {
    // 参数校验
    if (!origin || !destination) {
      throw new Error("Origin或Destination参数不能为空");
    }
    if (!unit) {
      throw new Error("必须指定单位参数：km, m, mi, ft, nm");
    }

    var geocoder = Maps.newGeocoder();
    var start = geocoder.geocode(origin);
    if (start.status !== 'OK' || start.results.length === 0) {
      throw new Error("无法解析起点地址：" + origin);
    }
    var coords1 = [start.results[0].geometry.location.lat, start.results[0].geometry.location.lng];

    var lat1 = (coords1[0] * Math.PI) / 180;
    var lng1 = (coords1[1] * Math.PI) / 180;

    var endd = geocoder.geocode(destination);
    if (endd.status !== 'OK' || endd.results.length === 0) {
      throw new Error("无法解析终点地址：" + destination);
    }
    var coords2 = [endd.results[0].geometry.location.lat, endd.results[0].geometry.location.lng];

    var lat2 = (coords2[0] * Math.PI) / 180;
    var lng2 = (coords2[1] * Math.PI) / 180;

    var dLng = lng2 - lng1;
    var dLat = lat2 - lat1;

    var a = Math.sin(dLat / 2) * Math.sin(dLat / 2) + Math.sin(dLng / 2) * Math.sin(dLng / 2) * Math.cos(lat1) * Math.cos(lat2);
    var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));

    var radius = 6371; // 地球半径（公里）
    switch (unit.toLowerCase()) { // 兼容大小写
      case "km":
        return Number((radius * c).toFixed(2));
      case "m": 
        return Number((radius * c * 1000).toFixed(2));
      case "mi":
        return Number((radius * c / 1.60934).toFixed(2));
      case "ft":
        return Number((radius * c * 3280.84).toFixed(2));
      case "nm":
        return Number((radius * c / 1.852).toFixed(2));
      default:
        throw new Error("无效单位，支持的单位：km, m, mi, ft, nm");
    }

  } catch (error) {
    Logger.log("GEODIST错误：" + error.message);
    return "#错误：" + error.message; // 返回明确错误提示
  }
}

验证步骤 #

1. 打开Google Sheets的脚本编辑器，替换原有代码为修复后的版本，保存并授权；
2. 在单元格中输入=DRIVEDIST("北京天安门", "上海外滩", "km")或=GEODIST(39.9042, 116.4074, 31.2304, 121.4737, "km")（经纬度格式）；
3. 查看返回结果，如果有错误，会显示明确提示，可通过脚本编辑器的日志查看详细错误信息；
4. 确认函数能自动计算：修改单元格中的地址或单位，检查结果是否自动更新。

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