如何改进App Script实现Google Sheets未删除线列自动求和？

原脚本实现了对指定范围未加删除线单元格的求和，但存在两个核心问题：一是自定义函数默认仅在参数值变化时刷新，修改单元格删除线格式不会触发重算；二是循环中频繁调用getRange()，API调用开销大，性能低下。以下是优化后的实现方案和相关技巧：

优化后的自动刷新自定义函数 #

通过将函数设置为易失性函数，强制每次编辑时重新计算，同时一次性获取所有数据和格式，大幅提升性能：

function SumIfNotStrikeThrough(rangeA1Notation) {
  // 触发易失性刷新，确保每次编辑都重新计算
  SpreadsheetApp.getActiveSpreadsheet();
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const targetRange = sheet.getRange(rangeA1Notation);
  
  // 一次性获取所有单元格值和字体删除线状态，减少API调用
  const values = targetRange.getValues();
  const fontLines = targetRange.getFontLines();
  
  let total = 0;
  
  // 遍历所有单元格
  for (let i = 0; i < values.length; i++) {
    const cellValue = values[i][0];
    const hasStrikeThrough = fontLines[i][0] === "line-through";
    
    // 非空且无删除线时累加数值（自动过滤非数值内容）
    if (cellValue !== "" && !hasStrikeThrough) {
      total += Number(cellValue) || 0;
    }
  }
  
  return total;
}

使用方式和原脚本一致，比如在单元格输入=SumIfNotStrikeThrough("A1:A20")，修改内容或删除线格式后会自动刷新求和结果。

固定范围求和的触发器方案 #

如果求和范围固定（比如始终计算A列），可以用onEdit触发器自动更新结果，避免手动输入函数：

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const targetColumn = 1; // 目标列：A列（列索引从1开始）
  const resultCell = sheet.getRange("B1"); // 求和结果放在B1单元格
  
  // 仅在目标列编辑或格式修改时触发计算
  if (e.range.getColumn() === targetColumn || e.changeType === "FORMAT") {
    const lastRow = sheet.getLastRow();
    const targetRange = sheet.getRange(1, targetColumn, lastRow, 1);
    
    const values = targetRange.getValues();
    const fontLines = targetRange.getFontLines();
    
    let total = 0;
    for (let i = 0; i < values.length; i++) {
      const cellValue = values[i][0];
      const hasStrikeThrough = fontLines[i][0] === "line-through";
      
      if (cellValue !== "" && !hasStrikeThrough) {
        total += Number(cellValue) || 0;
      }
    }
    
    resultCell.setValue(total);
  }
}

注意事项： #

• 这是简单触发器，无需授权，但仅响应手动编辑操作；若需响应脚本/API修改，需创建可安装触发器。
• 可根据需求修改targetColumn（目标列索引）和resultCell（结果单元格位置）。

Google Sheets实用技巧 #

• 自定义函数性能优化：尽量一次性获取范围的批量数据（如getValues()、getFontLines()），避免循环中多次调用getRange()——每次API调用都有开销，频繁调用会导致脚本变慢甚至触发配额限制。
• 易失性函数的合理使用：通过调用SpreadsheetApp.getActiveSpreadsheet()等易失性服务，可让自定义函数在每次编辑时自动刷新，但注意不要滥用，否则会增加整体计算负担。
• 触发器类型选择：简单触发器（onEdit/onOpen）无需授权，适合基础场景；可安装触发器支持更多事件（如时间驱动、表单提交），但需要用户授权。
• 无脚本替代方案：若不想使用脚本，可手动给未加删除线的单元格添加标记（如旁边列打勾），再用SUMIF函数求和，但需手动维护标记，灵活性较差。

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