Google Sheets中提取Google Maps路线距离、时长纯数值的实现方法

Google Maps Directions API 本身就返回不带单位的原始数值字段，无需解析带单位的文本，修改逻辑如下：

• 距离的distance.value字段单位为米，除以1000即可得到公里数值
• 时长的duration.value字段单位为秒，除以60取整即可得到总分钟数

具体修改操作 #

直接替换原有脚本中的GOOGLEMAPS_DURATION和GOOGLEMAPS_DISTANCE两个函数即可，修改后的代码如下：

/**
 * Calculate the travel time between two locations
 * on Google Maps.
 *
 * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {Number} The time in minutes
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  if (origin.map) {
    return origin.map(DISTANCE);
  }
  const key = ["duration", origin, destination, mode].join(",");
  const value = getCache(key);
  if (value !== null) return Number(value);
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("XNo route found!");
  }
  const { legs: [{ duration: { value: timeSeconds } } = {}] = [] } = data;
  // 秒转分钟，四舍五入取整
  const totalMinutes = Math.round(timeSeconds / 60);
  setCache(key, totalMinutes.toString());
  return totalMinutes; 
};

/**
 * Calculate the distance between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {Number} The distance in kilometers
 * @customFunction
 */
const GOOGLEMAPS_DISTANCE = (origin, destination, mode = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  if (origin.map) {
    return origin.map(DISTANCE);
  }
  const key = ["distance", origin, destination, mode].join(",");
  const value = getCache(key);
  if (value !== null) return Number(value);

  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("YNo route found!");
  }
  const { legs: [{ distance: { value: distanceMeters } } = {}] = [] } = data;
  // 米转公里，保留1位小数，可根据需要调整toFixed的参数
  const kmValue = Number((distanceMeters / 1000).toFixed(1));
  setCache(key, kmValue.toString());

  return kmValue;
};

注意事项 #

如果之前调用过函数生成过带单位的缓存结果，首次修改后可更换测试地址验证，或手动清空文档缓存避免返回旧数据。

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