Google Spreadsheet 如何仅在展开行组范围内执行自定义函数

可以实现，核心逻辑是放弃单元格内直接调用自定义函数的模式，改为由脚本按需触发批量计算、写入静态结果，从根源避免逐单元格反复触发全表扫描。

第一步：清理原有自定义函数调用 #

现有逐单元格调用Daily(name,week,day)的方式天然会触发4745次（13*365）独立执行，首先要把所有单元格内的自定义函数公式清空，后续所有计算结果由脚本批量写入静态值，完全避免自动触发执行。

第二步：配置触发规则实现按需计算 #

基于你已经做好的月份行分组、命名范围，用Apps Script的onSelectionChange简单触发器即可实现仅展开对应月份时才执行计算：

• 提前在脚本内预存每个月份命名范围对应的起止行、年月参数，和你已经创建的命名范围一一对应
• 当用户选中的单元格落在某个月份的命名范围内时，脚本先判断该月份分组是否处于展开状态（校验分组首行是否被隐藏）
• 仅分组处于展开状态时，才触发该月份的所有数据计算，未展开的月份完全不执行任何计算逻辑

第三步：添加缓存进一步优化性能 #

• 已经计算过的月份结果可以存在CacheService或者单独的隐藏缓存工作表中，用户重复展开同一个月份时直接读取缓存，不需要重复扫描表单响应数据
• 表单提交新数据时，仅清空对应月份的缓存即可，不需要全量重算所有月份数据

示例代码参考 #

// 预配置你已经创建的月份命名范围和对应参数，按需替换成实际值
const MONTH_CONFIG = {
  "2024_01_range": {year: 2024, month: 1, startRow: 2, endRow: 33},
  "2024_02_range": {year: 2024, month: 2, startRow: 34, endRow: 62}
  // 依次补充剩余10个月份的配置即可
}
const EMPLOYEE_LIST = ["员工1","员工2","员工3"] // 替换为你的13名员工名单
const RESULT_SHEET_NAME = "销售统计" // 替换为你放统计结果的工作表名
const FORM_SHEET_NAME = "表单响应" // 替换为表单数据所在工作表名

function onSelectionChange(e) {
  const activeRange = e.range;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const resultSheet = ss.getSheetByName(RESULT_SHEET_NAME);
  // 判断当前选中区域是否落在已配置的月份命名范围内
  for (let rangeName in MONTH_CONFIG) {
    const monthRange = ss.getRangeByName(rangeName);
    if (checkRangeIntersect(activeRange, monthRange)) {
      const config = MONTH_CONFIG[rangeName];
      // 检查当前月份分组是否为展开状态
      if (!resultSheet.isRowHiddenByUser(config.startRow)) {
        batchCalcMonthData(config, resultSheet);
        break;
      }
    }
  }
}

// 辅助函数：判断两个范围是否相交
function checkRangeIntersect(r1, r2) {
  return r1.getSheet().getSheetId() === r2.getSheet().getSheetId()
    && r1.getLastRow() >= r2.getRow()
    && r1.getRow() <= r2.getLastRow()
    && r1.getLastColumn() >= r2.getColumn()
    && r1.getColumn() <= r2.getLastColumn();
}

// 批量计算单月所有员工数据，替换为你自己的Daily逻辑即可
function batchCalcMonthData(config, resultSheet) {
  const cache = CacheService.getUserCache();
  const cacheKey = `calc_${config.year}_${config.month}`;
  const cacheData = cache.get(cacheKey);
  // 优先读缓存
  if (cacheData) {
    const result = JSON.parse(cacheData);
    resultSheet.getRange(config.startRow, 2, EMPLOYEE_LIST.length, result[0].length).setValues(result);
    return;
  }
  // 仅单次读取全量表单数据，不用重复扫描
  const formData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(FORM_SHEET_NAME).getDataRange().getValues();
  const daysInMonth = new Date(config.year, config.month, 0).getDate();
  const result = [];
  // 批量计算所有员工当月所有天的结果
  for (let emp of EMPLOYEE_LIST) {
    const rowRes = [];
    for (let d = 1; d <= daysInMonth; d++) {
      // 调用你原来的Daily逻辑，直接用已经读入内存的formData计算即可
      rowRes.push(calculateDaily(emp, config.week, d, formData));
    }
    result.push(rowRes);
  }
  // 一次性写入整月结果，性能远高于逐单元格写入
  resultSheet.getRange(config.startRow, 2, EMPLOYEE_LIST.length, daysInMonth).setValues(result);
  // 写入缓存，有效期6小时可自行调整
  cache.put(cacheKey, JSON.stringify(result), 21600);
}

补充优化建议 #

• 如果需要实时响应表单新提交的数据，可以添加表单提交触发器，新数据提交后仅清空对应月份的缓存，下次展开时自动重算即可
• 所有读写操作尽量用getValues、setValues批量处理，比逐单元格操作性能高10倍以上

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