Google表格自定义函数内部执行错误的解决方案咨询

看起来你已经做了不少优化（缓存、批量传范围），但自定义函数本身的限制确实挺头疼的——30秒时限、无法主动重试、多实例并发容易触发内部错误。我给你几个可行的方向，从代码优化到彻底换执行方式都有：

一、先优化现有自定义函数的性能（立竿见影） #

你的代码里有两个可以大幅提升性能的点：

1. 批量获取缓存，减少API调用 #

现在你每次调用cache.get()一次拿一个键，改成一次获取所有需要的缓存键，能减少很多不必要的API请求耗时：

// 替换原来多次cache.get的代码
var cacheKeys = ["TeachersL", "Teachers1", "Teachers2", "Teachers3", "Teachers4", "Dates", "Schools", "NumberScholars"];
var cacheValues = cache.getAll(cacheKeys);

// 然后逐个解析
var teachersL = JSON.parse(cacheValues["TeachersL"]);
var teachers1 = JSON.parse(cacheValues["Teachers1"]);
// ... 其他变量同理

2. 把数据预处理成查找表，替换O(n)循环 #

现在你每次process都要遍历整个Schools数组，数据多了之后这会非常慢。可以把Properties里的数据预处理成一个以日期+学校为键的对象，这样查找时直接用键取值，时间复杂度从O(n)降到O(1)：

比如在第一次加载缓存的时候，就构建这个查找表：

// 当缓存不存在时，加载并构建查找表
if (!number) {
  // 先加载所有Properties数据
  var props = PropertiesService.getScriptProperties();
  var data = {
    TeachersL: JSON.parse(props.getProperty('TeachersL')),
    Teachers1: JSON.parse(props.getProperty('Teachers1')),
    Teachers2: JSON.parse(props.getProperty('Teachers2')),
    Teachers3: JSON.parse(props.getProperty('Teachers3')),
    Teachers4: JSON.parse(props.getProperty('Teachers4')),
    Dates: JSON.parse(props.getProperty('Dates')),
    Schools: JSON.parse(props.getProperty('Schools')),
    NumberScholars: JSON.parse(props.getProperty('NumberScholars'))
  };

  // 构建查找表：key是`日期+学校`，value是包含教师列表和人数的对象
  var lookupTable = {};
  for (var y = 0; y < data.Schools.length; y++) {
    var key = `${data.Dates[y]}_${data.Schools[y]}`;
    lookupTable[key] = {
      teachers: [data.TeachersL[y], data.Teachers1[y], data.Teachers2[y], data.Teachers3[y], data.Teachers4[y]],
      count: data.NumberScholars[y]
    };
  }

  // 把原数据和查找表都存入缓存
  cache.putAll({
    TeachersL: JSON.stringify(data.TeachersL),
    Teachers1: JSON.stringify(data.Teachers1),
    Teachers2: JSON.stringify(data.Teachers2),
    Teachers3: JSON.stringify(data.Teachers3),
    Teachers4: JSON.stringify(data.Teachers4),
    Dates: JSON.stringify(data.Dates),
    Schools: JSON.stringify(data.Schools),
    NumberScholars: JSON.stringify(data.NumberScholars),
    LookupTable: JSON.stringify(lookupTable)
  });

  // 同时把lookupTable赋值给变量，供后续使用
  var lookupTable = lookupTable;
} else {
  // 从缓存获取预处理好的查找表
  var lookupTable = JSON.parse(cacheValues["LookupTable"]);
}

然后process函数里的查找就变成：

function process(teacher, Vdate, school) {
  if (!Vdate || !teacher) return null;
  
  var dateStr = Vdate.toJSON();
  var key = `${dateStr}_${school}`;
  var entry = lookupTable[key];
  
  if (entry && entry.teachers.includes(teacher)) {
    return entry.count;
  }
  return null; // 如果没匹配到返回null
}

这个优化对大数据量的提升非常明显，循环次数直接从几百几千次降到0。

二、彻底避开自定义函数的限制（解决超时和重试问题） #

自定义函数的30秒时限和无法主动重试是硬伤，如果你数据还在增长，迟早会遇到瓶颈。更好的办法是用自定义菜单+批量更新或者触发器来替代：

方案1：自定义菜单触发批量计算 #

写一个脚本，手动触发后一次性计算所有需要的单元格，这样不受自定义函数的30秒限制（脚本最长可以跑6分钟），还能自己实现重试逻辑。

示例代码：

// 创建自定义菜单
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('批量计算')
    .addItem('更新VSApmokyti结果', 'calculateAll')
    .addToUi();
}

// 批量计算主函数
function calculateAll() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // 假设你的Names在A4:A100，date在B4:B100，place在C4:C100，结果要写到D4:D100
  var namesRange = sheet.getRange("A4:A100");
  var datesRange = sheet.getRange("B4:B100");
  var placesRange = sheet.getRange("C4:C100");
  var outputRange = sheet.getRange("D4:D100");
  
  var names = namesRange.getValues();
  var dates = datesRange.getValues();
  var places = placesRange.getValues();
  
  // 先加载缓存或Properties数据，构建查找表（和之前优化的逻辑一样）
  var cache = CacheService.getScriptCache();
  var lookupTable = JSON.parse(cache.get("LookupTable"));
  
  if (!lookupTable) {
    // 从Properties加载并构建查找表，存入缓存
    var props = PropertiesService.getScriptProperties();
    var data = {
      TeachersL: JSON.parse(props.getProperty('TeachersL')),
      Teachers1: JSON.parse(props.getProperty('Teachers1')),
      Teachers2: JSON.parse(props.getProperty('Teachers2')),
      Teachers3: JSON.parse(props.getProperty('Teachers3')),
      Teachers4: JSON.parse(props.getProperty('Teachers4')),
      Dates: JSON.parse(props.getProperty('Dates')),
      Schools: JSON.parse(props.getProperty('Schools')),
      NumberScholars: JSON.parse(props.getProperty('NumberScholars'))
    };
    
    lookupTable = {};
    for (var y = 0; y < data.Schools.length; y++) {
      var key = `${data.Dates[y]}_${data.Schools[y]}`;
      lookupTable[key] = {
        teachers: [data.TeachersL[y], data.Teachers1[y], data.Teachers2[y], data.Teachers3[y], data.Teachers4[y]],
        count: data.NumberScholars[y]
      };
    }
    cache.put("LookupTable", JSON.stringify(lookupTable), 3600); // 缓存1小时
  }
  
  // 批量计算结果
  var results = [];
  for (var i = 0; i < names.length; i++) {
    var teacher = names[i][0];
    var Vdate = dates[i][0];
    var school = places[i][0];
    
    if (!Vdate || !teacher) {
      results.push([null]);
      continue;
    }
    
    var dateStr = Vdate.toJSON();
    var key = `${dateStr}_${school}`;
    var entry = lookupTable[key];
    
    if (entry && entry.teachers.includes(teacher)) {
      results.push([entry.count]);
    } else {
      results.push([null]);
    }
  }
  
  // 一次性写入结果，减少Sheet交互次数
  outputRange.setValues(results);
  SpreadsheetApp.getUi().alert('计算完成！');
}

这个方法的好处是：

• 不受自定义函数的30秒限制，数据再多也能处理
• 可以自己加指数退避逻辑（比如加载Properties时如果失败，重试几次）
• 批量写入单元格，比自定义函数多次调用快得多

方案2：用触发器自动计算 #

如果需要数据更新时自动计算，可以加一个onEdit触发器：

function onEdit(e) {
  // 假设当A、B、C列的数据被编辑时触发计算
  var editedRange = e.range;
  var sheet = editedRange.getSheet();
  
  // 检查是否在目标范围内（A4:C100）
  if (sheet.getName() !== "你的工作表名") return;
  if (editedRange.getRow() <4 || editedRange.getRow() >100) return;
  if (editedRange.getColumn() <1 || editedRange.getColumn() >3) return;
  
  // 调用批量计算函数
  calculateAll();
}

注意：onEdit触发器的执行时间限制是30秒，但如果你的lookupTable已经缓存好，计算会非常快，应该没问题。如果还是慢，可以改成用可安装触发器（onChange），时间限制更长。

三、关于指数退避的实现 #

你说自定义函数里用不了指数退避，确实是这样——自定义函数是同步执行的，而且不能用Utilities.sleep()（会被Sheets限制）。但在菜单触发的脚本里就可以用了，比如加载Properties时如果出错：

function getWithBackoff(func, maxRetries = 3) {
  for (var i = 0; i < maxRetries; i++) {
    try {
      return func();
    } catch (e) {
      if (i === maxRetries -1) throw e;
      var delay = Math.pow(2, i) * 1000; // 1s, 2s, 4s...
      Utilities.sleep(delay);
    }
  }
}

// 使用示例：
var props = getWithBackoff(() => PropertiesService.getScriptProperties());

这样如果加载Properties时遇到临时错误，会自动重试几次。

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