Google Apps Script可安装onEdit触发器无法调用sum函数及sumif优化问题

问题1：onEdit触发时sum函数不生效的原因及修复 #

• 核心错误1：When函数中获取公司名称时仅获取了Range对象，没有读取单元格实际值，company === "Unisensor"的判断永远为假，sum函数从未被调用，需要补充getValue()方法读取值。
• 核心错误2：全局变量ss在脚本初始化时就通过getActiveSheet()获取了当前激活表，可安装触发器触发时激活表可能不符合预期，建议明确指定获取Stock表而非动态获取激活表。

问题2：sumif实现优化方案 #

当前的sumif逻辑是正确的，但性能极差，原因是循环中反复调用getRange().getValue()读写表格，Google Apps Script中表格IO操作是最大的性能瓶颈，单次调用耗时在毫秒级，循环数百次就会出现明显卡顿。优化方案如下：

• 一次性读取整个计算范围的所有单元格值到内存数组，在数组内完成遍历匹配计算，仅需1次读操作和1次写操作，性能可提升上百倍。
• 也可以直接调用电子表格内置的SUMIF公式，无需自行编写循环逻辑，执行效率最高。

修正后的完整代码 #

var stck = SpreadsheetApp.openById("1WGbEo1Xr99HwHY_4ZaeTRIgCOuNcjVfqCzZx4dcQX4I");
var unisensor = stck.getSheetByName("Unisensor");

var ssOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Product List");
var options1 = ssOptions.getRange(2,1,ssOptions.getLastRow() - 1,2).getValues();

function When(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var ssName = activeCell.getSheet().getName();
  var stockSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stock");
  
  if (ssName=="Stock" && r==2 && c==1){
    stockSheet.getRange(r,2).clearContent();
    stockSheet.getRange(r,2).clearDataValidations();
    var filteredOptions = options1.filter(function(o){return o[0] === val});
    var listToApply = filteredOptions.map(function(o){return o[1]});
    var cell = stockSheet.getRange(r,2);
    applyValidationToCell(listToApply,cell);
  }

  if (ssName=="Stock" && r==2 && c==2){
    // 补充getValue()读取单元格实际值
    var company = stockSheet.getRange(r,1).getValue();
    if(company ==="Unisensor"){
      sum(val); 
    }
  }
}

function applyValidationToCell(list,cell){
  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  cell.setDataValidation(rule);
}

function sum(val){
  var res = 0;
  // 一次性读取C列到I列第4行到最后一行的所有数据，仅调用1次读接口
  var lastRow = unisensor.getLastRow();
  var data = unisensor.getRange(4, 3, lastRow - 3, 7).getValues();
  // 内存中遍历数组计算，无额外IO开销
  for(var i = 0; i < data.length; i++){
    if(data[i][0] == val){
      res += data[i][6] || 0;
    }
  }
  // 仅调用1次写接口赋值
  var stockSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stock");
  stockSheet.getRange(2,3).clearContent();
  stockSheet.getRange(2,3).setValue(res);
  Logger.log(res);
}

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