Google Maps脚本调用超限问题及谷歌表格计算值留存咨询

针对你遇到的Service Invoked Too Many Times错误，以及需要阻止自动重算、保留已计算数值的需求，提供以下几种解决方案：

1. 给自定义函数添加缓存机制 #

通过Google Apps Script的CacheService缓存已计算的结果，相同参数的调用会直接读取缓存，避免重复请求Google Maps API，从根源减少调用次数。

修改后的GOOGLEMAPS函数如下：

/**
* Get Distance between 2 different addresses.
* @param start_address Address as string Ex. "300 N LaSalles St, Chicago, IL"
* @param end_address Address as string Ex. "900 N LaSalles St, Chicago, IL"
* @param return_type Return type as string Ex. "miles" or "kilometers" or "minutes" or "hours"
* @customfunction
*/
function GOOGLEMAPS(start_address, end_address, return_type) {
  // 生成唯一缓存键，确保相同参数对应同一缓存
  const cacheKey = `GOOGLEMAPS_${start_address}_${end_address}_${return_type}`;
  const cache = CacheService.getScriptCache();
  
  // 先尝试读取缓存
  const cachedResult = cache.get(cacheKey);
  if (cachedResult !== null) {
    return parseFloat(cachedResult);
  }

  var mapObj = Maps.newDirectionFinder();
  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  var directions = mapObj.getDirections();

  var getTheLeg = directions["routes"][0]["legs"][0];
  var result;

  switch(return_type){
    case "miles":
      result = getTheLeg["distance"]["value"] * 0.000621371;
      break;
    case "minutes":
      result = getTheLeg["duration"]["value"] / 60;
      break;
    case "hours":
      result = getTheLeg["duration"]["value"] / 60 / 60;
      break;      
    case "kilometers":
      result = getTheLeg["distance"]["value"] / 1000;
      break;
    default:
      result = "Error: Wrong Unit Type";
   }

  // 将结果存入缓存，有效期设为1天（86400秒），可根据需求调整
  cache.put(cacheKey, result.toString(), 86400);
  return result;
}

• 优势：无需手动操作，自动复用历史计算结果，大幅减少API调用次数
• 注意：如果需要更新路线数据，可手动修改单元格参数（比如加个空格再删除）触发重新计算，或调整缓存有效期

2. 手动触发计算，将公式替换为静态数值 #

如果你的路线数据不需要频繁更新，可以通过脚本批量将GOOGLEMAPS公式替换为计算后的数值，彻底避免自动重算。

添加以下脚本到你的Google Sheets脚本编辑器：

// 添加自定义菜单，方便手动触发
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('工具')
    .addItem('更新路线数值', 'replaceFormulasWithValues')
    .addToUi();
}

// 批量替换GOOGLEMAPS公式为数值
function replaceFormulasWithValues() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const formulas = range.getFormulas();
  const values = range.getValues();

  for (let row = 0; row < formulas.length; row++) {
    for (let col = 0; col < formulas[row].length; col++) {
      // 只处理包含GOOGLEMAPS的公式
      if (formulas[row][col].includes('GOOGLEMAPS')) {
        sheet.getRange(row+1, col+1).setValue(values[row][col]);
      }
    }
  }
  SpreadsheetApp.getUi().alert('已完成公式替换！');
}

使用方法：

• 保存脚本后刷新表格，顶部会出现「工具」菜单
• 当你需要更新路线数据时，先确保单元格是GOOGLEMAPS公式，执行「更新路线数值」，所有公式会被替换为当前计算的静态数值
• 之后打开表格不会再触发API调用，彻底解决超限问题

3. 补充优化建议 #

• 合并多站点计算：如果是计算多站点的总时长，可修改函数支持批量传入地址数组，一次API请求获取完整路线的总时长，减少单次调用次数
• 错误处理增强：原函数未处理API返回异常（比如地址无效、无路线），可添加try-catch块避免整个函数崩溃

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