Google Sheets自定义城市距离公式适配ARRAYFORMULA方案咨询

方案1：适配ARRAYFORMULA调用（带缓存优化） #

如果要保留你期望的=ARRAYFORMULA(IF(A2:A<>"", MILEAGE(A2:A, B2:B), ""))调用形式，可以改造自定义函数，增加数组判断逻辑和缓存机制，大幅降低全量重算的开销：

// 缓存实例，缓存有效期默认1小时，可自行调整
const cache = CacheService.getScriptCache();

function MILEAGE(origin, destination) {
  // 判断输入是否为数组
  if (Array.isArray(origin)) {
    // 逐行处理数组
    return origin.map((row, i) => {
      const ori = row[0];
      const dest = destination[i][0];
      // 空值直接返回空
      if (!ori || !dest) return "";
      // 生成缓存键
      const cacheKey = `mileage_${ori}_${dest}`.replace(/\s/g, "_");
      const cached = cache.get(cacheKey);
      if (cached) return Number(cached);
      // 无缓存则调用接口计算
      const distance = calcDistance(ori, dest);
      // 结果存入缓存，有效期3600秒=1小时
      cache.put(cacheKey, String(distance), 3600);
      return distance;
    })
  }
  // 单值调用逻辑
  const cacheKey = `mileage_${origin}_${destination}`.replace(/\s/g, "_");
  const cached = cache.get(cacheKey);
  if (cached) return Number(cached);
  const distance = calcDistance(origin, destination);
  cache.put(cacheKey, String(distance), 3600);
  return distance;
}

// 抽离公共的距离计算逻辑
function calcDistance(origin, destination) {
  Utilities.sleep(0);
  const directions = Maps.newDirectionFinder()
    .setRegion('US')
    .setOrigin(origin)
    .setDestination(destination)
    .getDirections();
  const route = directions.routes[0].legs[0];
  return (route.distance.value) * 0.000621371;
}

说明：该方案即使触发全范围重算，已经计算过的起点终点对会直接读缓存返回，不需要重复调用地图接口，实际开销非常低。如果需要修改缓存有效期，调整cache.put的第三个参数即可，单位为秒。

方案2：onEdit触发器自动计算（完全避免全量重算，更推荐） #

如果完全不需要全范围重算，该方案性能最优：修改/新增某一行的城市时，只会计算当前行的距离，不会触动其他行的逻辑。
无需在单元格输入任何公式，直接在原有脚本基础上新增以下代码即可：

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  // 可自行调整监听的列、结果输出列、生效的工作表名称
  const WATCH_SHEET = "Sheet1"; // 修改为你自己的工作表名
  const ORIGIN_COL = 1; // A列为起点列
  const DEST_COL = 2; // B列为终点列
  const RESULT_COL = 3; // C列为结果输出列
  // 非目标工作表、非AB列修改直接退出
  if (sheet.getName() !== WATCH_SHEET || ![ORIGIN_COL, DEST_COL].includes(range.columnStart)) return;
  const row = range.rowStart;
  // 表头行跳过，可根据实际情况调整起始行
  if (row < 2) return;
  const origin = sheet.getRange(row, ORIGIN_COL).getValue();
  const dest = sheet.getRange(row, DEST_COL).getValue();
  // 起点终点任意为空则清空结果
  if (!origin || !dest) {
    sheet.getRange(row, RESULT_COL).clearContent();
    return;
  }
  // 计算并写入结果
  const distance = MILEAGE(origin, dest);
  sheet.getRange(row, RESULT_COL).setValue(distance);
}

说明：保存脚本后刷新表格即可生效，所有A、B列的修改都会自动触发当前行的距离计算，结果会自动写入C列。如果需要调整工作表、列的对应关系，修改代码前的常量配置即可。

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