Google Apps Script重复调用含getValues的函数运行缓慢如何缓存静态数据？

可以实现和Excel完全一致的静态数据预加载、手动触发更新的能力，以下是可落地的实现方案：

核心实现思路 #

利用Google Apps Script的缓存能力+触发器机制，避免自定义函数重复调用getValues拉取静态表数据，核心逻辑和Excel的全局变量预加载逻辑对齐，改造后1000次自定义函数调用仅需要拉取1次静态表数据，性能可提升数十倍。

具体实现步骤 #

• 步骤1：配置静态数据更新函数，专门负责拉取所有查询表的全量数据并写入缓存，可绑定到表格自定义按钮触发手动更新
  示例代码：

  // 更新静态查询表缓存，可绑定到表格按钮调用
  function updateStaticCache() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    // 批量拉取所有静态查询表数据
    const staticData = {
      table1: ss.getRange("查询表1!A:Z").getValues(),
      table2: ss.getRange("查询表2!A:Z").getValues(),
      // 其他需要缓存的查询表按此格式添加
    };
    // 序列化后写入脚本属性持久化存储
    PropertiesService.getScriptProperties().setProperty("static_cache", JSON.stringify(staticData));
  }
  

• 步骤2：配置打开自动加载触发器，实现Excel文件打开时加载全局变量的效果
  直接添加onOpen简单触发器即可，文件每次被用户打开时会自动执行一次缓存更新：

  function onOpen(e) {
    // 打开文件时自动更新静态缓存
    updateStaticCache();
    // 可选：在顶部菜单栏添加更新按钮，方便手动触发
    const ui = SpreadsheetApp.getUi();
    ui.createMenu("自定义工具")
      .addItem("更新静态查询表缓存", "updateStaticCache")
      .addToUi();
  }
  

• 步骤3：改造现有自定义函数，优先从缓存读取静态数据
  示例改造后的自定义函数逻辑：

  function YOUR_CUSTOM_FUNC(param1, param2) {
    // 优先从缓存读静态数据，不存在才主动拉取兜底
    const cacheStr = PropertiesService.getScriptProperties().getProperty("static_cache");
    const staticData = cacheStr ? JSON.parse(cacheStr) : (() => {
      updateStaticCache();
      return JSON.parse(PropertiesService.getScriptProperties().getProperty("static_cache"));
    })();
    // 原有业务逻辑，直接用staticData里的表数据即可，不需要再调用getValues
    // ... 你的原有计算逻辑
  }
  

特殊场景适配 #

如果你的单张查询表数据量极大，序列化后超出PropertiesService单条属性500KB的存储限制，可以替换为CacheService存储，缓存最长有效期可设置为21600秒（6小时），需要更新时主动清空缓存即可。

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