IMPORTJSON重复调用报错，如何缓存快递追踪状态实现复用？

1. 原函数存在语法错误：IMPORTJSON函数定义的第一个参数是json，但函数内部却调用了未定义的url变量，这是导致报错的直接原因之一。
2. API调用频率限制：Google Apps Script的UrlFetchApp有每日调用次数和频率限制，1000行单独调用公式会频繁触发限制，导致Error getting data报错。

先修正原函数的参数错误，确保单个调用能正常工作：

function IMPORTJSON(url, xpath) {
  try {
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);

    var patharray = xpath.split("/");
    for(var i = 0; i < patharray.length; i++){
      if(patharray[i] === "") continue; // 处理开头/结尾的空字符串
      json = json[patharray[i]];
      if(typeof json === "undefined") break;
    }

    if(typeof(json) === "undefined") {
      return "Node Not Available";
    } else if(typeof(json) === "object") {
      var tempArr = [];
      for(var obj in json){
        tempArr.push([obj, json[obj]]);
      }
      return tempArr;
    } else {
      return json;
    }
  } catch(err){
    return `Error getting data: ${err.message}`; // 增加错误详情便于排查
  }
}

方案1：批量追踪函数+缓存机制 #

创建一个批量处理函数，一次性处理所有追踪请求，并利用CacheService缓存已查询过的单号结果，避免重复请求：

function BATCH_IMPORT_TRACKING(range) {
  const cache = CacheService.getScriptCache();
  const cacheExpiration = 3600; // 缓存1小时，可调整
  const apiBase = "http://shipit-api.herokuapp.com/api/carriers/";
  const xpath = "activities/0/details";
  
  const values = range.getValues();
  const results = [];

  for (let row of values) {
    const carrier = row[0]; // 对应X列（快递商）
    const trackingNum = row[1]; // 对应W列（追踪号）
    if (!carrier || !trackingNum) {
      results.push([""]);
      continue;
    }

    // 生成缓存键
    const cacheKey = `${carrier}_${trackingNum}`;
    const cachedResult = cache.get(cacheKey);
    
    if (cachedResult) {
      results.push([cachedResult]);
      continue;
    }

    // 未命中缓存，调用API
    try {
      const url = `${apiBase}${carrier}/${trackingNum}`;
      const res = UrlFetchApp.fetch(url);
      const json = JSON.parse(res.getContentText());
      
      // 解析xpath
      let data = json;
      const pathParts = xpath.split("/").filter(p => p);
      for (let part of pathParts) {
        data = data[part];
        if (typeof data === "undefined") break;
      }

      const result = typeof data === "undefined" ? "Node Not Available" : String(data);
      cache.put(cacheKey, result, cacheExpiration);
      results.push([result]);
    } catch (err) {
      results.push([`Error: ${err.message}`]);
    }
  }

  return results;
}

使用方法： #

1. 在Google Sheets中，选中需要填充结果的列（比如Y列），输入公式：

   =BATCH_IMPORT_TRACKING(W4:X1003)
   

   这里W4:X1003是包含追踪号（W列）和快递商（X列）的范围，根据实际行数调整。
2. 公式会一次性返回所有结果，且重复的单号会直接使用缓存，减少API调用次数。

方案2：脚本触发一次性写入（无公式依赖） #

如果希望完全避免公式自动刷新的问题，可以创建一个脚本，手动或定时触发，直接将结果写入单元格：

function WRITE_TRACKING_RESULTS() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const startRow = 4; // 数据起始行
  const lastRow = sheet.getLastRow();
  const dataRange = sheet.getRange(startRow, 23, lastRow - startRow + 1, 2); // W列是第23列，X列是24列
  const values = dataRange.getValues();
  const resultRange = sheet.getRange(startRow, 25, lastRow - startRow + 1, 1); // Y列是第25列，用于写入结果
  const results = [];

  const apiBase = "http://shipit-api.herokuapp.com/api/carriers/";
  const xpath = "activities/0/details";

  for (let row of values) {
    const carrier = row[1]; // X列（快递商）
    const trackingNum = row[0]; // W列（追踪号）
    if (!carrier || !trackingNum) {
      results.push([""]);
      continue;
    }

    try {
      const url = `${apiBase}${carrier}/${trackingNum}`;
      const res = UrlFetchApp.fetch(url);
      const json = JSON.parse(res.getContentText());
      
      let data = json;
      const pathParts = xpath.split("/").filter(p => p);
      for (let part of pathParts) {
        data = data[part];
        if (typeof data === "undefined") break;
      }

      results.push([typeof data === "undefined" ? "Node Not Available" : String(data)]);
    } catch (err) {
      results.push([`Error: ${err.message}`]);
    }
  }

  resultRange.setValues(results);
  SpreadsheetApp.getUi().alert("追踪结果已更新完成");
}

使用方法： #

1. 打开脚本编辑器，粘贴上述代码。
2. 点击运行按钮，授权脚本权限后，即可一次性将所有追踪结果写入指定列。
3. 可设置定时触发器（比如每天更新一次），自动同步最新状态。

内容的提问来源于stack exchange，提问作者HOANG TRUNG LE