如何避免Google Apps Script自定义函数重复调用外部API？

核心思路 #

要让自定义函数仅在用户显式操作时刷新，核心是给函数添加手动触发条件，并通过缓存机制复用之前的API返回结果，避免自动重新计算时重复调用API。以下是两种适配你使用场景的可行方案：

方案1：添加手动刷新标记参数 #

给函数增加一个额外的“刷新触发”参数，只有当用户手动修改该参数值时，函数才会重新调用API，否则直接返回缓存结果。缓存使用PropertiesService存储，确保不同单元格的结果互不干扰。

修改后的代码：

function FETCH_DATA(input, refreshTrigger) {
  // 获取脚本属性用于缓存数据
  const props = PropertiesService.getScriptProperties();
  // 生成唯一缓存键：结合单元格位置+输入值，避免不同单元格冲突
  const cellKey = SpreadsheetApp.getActiveRange().getA1Notation() + "_" + input;

  // 无刷新触发且缓存存在时，直接返回缓存
  if (!refreshTrigger && props.getProperty(cellKey)) {
    return JSON.parse(props.getProperty(cellKey));
  }

  // 调用外部API
  const url = "https://example.com/test-endpoint";
  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    payload: { input: input }
  }).getContentText();

  const data = JSON.parse(response)["data"];

  // 更新缓存
  props.setProperty(cellKey, JSON.stringify(data));
  return data;
}

使用方式：
在单元格中输入 =FETCH_DATA(A1, B1)，其中B1作为刷新标记（初始可填0）。需要刷新时，手动修改B1的值（比如改成1、2，甚至随便输入一个字符），函数就会重新调用API；之后可以改回原值，下次修改又会触发刷新。

方案2：利用单元格备注作为刷新触发 #

如果不想额外添加列，可以通过读取单元格的备注内容作为触发条件——用户编辑备注时，函数重新调用API，否则返回缓存结果。

代码示例：

function FETCH_DATA(input) {
  const props = PropertiesService.getScriptProperties();
  const activeRange = SpreadsheetApp.getActiveRange();
  const cellKey = activeRange.getA1Notation() + "_" + input;
  const currentNote = activeRange.getNote() || "";

  // 检查备注是否未变化且缓存存在，是则返回缓存
  const cachedNote = props.getProperty(cellKey + "_note");
  if (cachedNote === currentNote && props.getProperty(cellKey)) {
    return JSON.parse(props.getProperty(cellKey));
  }

  // 调用外部API
  const url = "https://example.com/test-endpoint";
  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    payload: { input: input }
  }).getContentText();

  const data = JSON.parse(response)["data"];

  // 更新缓存及备注记录
  props.setProperty(cellKey, JSON.stringify(data));
  props.setProperty(cellKey + "_note", currentNote);
  return data;
}

使用方式：
在单元格输入 =FETCH_DATA(A1)，需要刷新时右键单元格→编辑备注，随便修改备注内容（比如加个空格再删掉），保存后函数就会重新调用API。

辅助工具：清除缓存函数 #

如果需要批量清除缓存，可以添加以下辅助函数：

function CLEAR_FETCH_CACHE() {
  PropertiesService.getScriptProperties().deleteAllProperties();
  SpreadsheetApp.getUi().alert("缓存已清除");
}

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