Google Sheet onEdit脚本编辑第2、3行单元格触发#NUM!错误排查

一、错误原因分析 #

1. TypeError: Cannot read properties of undefined (reading 'getA1Notation') #

• 脚本通过setValue修改单元格时会再次触发onEdit事件，此时的事件对象e可能不包含完整的range属性，直接调用e.range.getA1Notation()会抛出异常。
• 未对事件对象e的完整性做前置校验，直接访问其属性。

2. 第2、3行编辑时下方出现#NUM!错误 #

• 编辑第2、3行时，doSecondThing_中e.range.rowStart - 4会得到负数索引，导致访问originalQuantities数组时取到undefined，计算ratio = 新值 / undefined产生#NUM!错误。

二、修复后的完整代码 #

主函数（添加防重复触发） #

var isProcessing = false;

function onEdit(e) {
  if (isProcessing) return;
  isProcessing = true;
  
  try {
    doFirstThing_(e);
    doSecondThing_(e);
  } finally {
    isProcessing = false;
  }
}

doFirstThing_函数（修复TypeError并增强校验） #

var originalQuantitiesFirst = [1, 1, 2, 2, 1, 1];  // Couscous, Teff, Fig, Date, Hamburg
    
function doFirstThing_(e) {
  // 前置校验：确保事件对象完整有效
  if (!e || !e.range || !e.source || !e.value) return;
  
  var spreadsheet = e.source;
  var sheet = spreadsheet.getSheetByName('Valencia');
  if (!sheet) return;

  var targetA1 = e.range.getA1Notation();
  var targetCells = ["B3", "D3", "F3", "H3", "J3", "F2"];
  
  if (targetCells.includes(targetA1)) {
    var newQuantity = Number(e.value);
    // 校验输入为正有效数字
    if (isNaN(newQuantity) || newQuantity <= 0) return;

    console.log('New quantity in doFirstThing_: ' + newQuantity);
     
    var ingredientIndex = targetCells.indexOf(targetA1);
    var originalQty = originalQuantitiesFirst[ingredientIndex];
    // 避免除以0
    if (originalQty === 0) return;
    
    var ratio = newQuantity / originalQty;
        
    for (var i = 0; i < targetCells.length; i++) {
      var updatedQty = originalQuantitiesFirst[i] * ratio;
      sheet.getRange(targetCells[i]).setValue(updatedQty);
    }
  }
}

doSecondThing_函数（修复#NUM!错误并增强校验） #

var originalQuantities = [[1, 3, 6, 4, 1], [1, 1, 3, 1, 1], [5, 3, 2, 3, 1], [4, 5, 1, 2, 1], [3, 2, 4, 4, 1]];
    
function doSecondThing_(e) {
  // 前置校验：确保事件对象完整有效
  if (!e || !e.range || !e.source || !e.value) return;
  
  var sheet = e.source.getActiveSheet();
  if (!sheet || sheet.getName() !== 'Valencia') return;
  
  // 仅处理第4行及以下的单元格编辑
  if (e.range.rowStart < 4) return;

  var targetColumn = e.range.columnStart;
  if (![2, 4, 6, 8, 10].includes(targetColumn)) return;

  var newQuantity = Number(e.value);
  if (isNaN(newQuantity) || newQuantity <= 0) return;

  console.log('New quantity in doSecondThing_: ' + newQuantity);
       
  var recipeIndex = Math.floor(targetColumn / 2) - 1;
  // 校验recipeIndex在数组合法范围内
  if (recipeIndex < 0 || recipeIndex >= originalQuantities.length) return;
  
  var rowOffset = e.range.rowStart - 4;
  // 校验rowOffset在当前配方的数组范围内
  if (rowOffset < 0 || rowOffset >= originalQuantities[recipeIndex].length) return;
  
  var originalQty = originalQuantities[recipeIndex][rowOffset];
  if (originalQty === 0) return;
  
  var ratio = newQuantity / originalQty;
        
  var startColumn = String.fromCharCode(66 + recipeIndex * 2);
  var quantityRange = sheet.getRange(startColumn + "4:" + startColumn + "8");
  var quantities = quantityRange.getValues();
        
  for (var i = 0; i < quantities.length; i++) {
    quantities[i][0] = originalQuantities[recipeIndex][i] * ratio;
  }
        
  quantityRange.setValues(quantities);
}

三、关键修复点总结 #

• 前置校验：所有函数开头先校验事件对象、单元格值、工作表的有效性，避免访问undefined属性。
• 防循环触发：通过全局标志位isProcessing避免脚本修改单元格时重复触发onEdit。
• 索引合法性校验：确保数组访问的索引在合法范围内，避免出现负数或越界索引。
• 数字有效性校验：确保输入为正有效数字，避免NaN或除以0的情况。

内容的提问来源于stack exchange，提问作者Chase To Be