Google Sheets脚本求助：按cc表Action选项填充对应表首个名称

我有一个Google表格，包含三个工作表：Product Details、Option Details和cc。在cc工作表的Action列（B列）中，当用户选择Add Product或Add Option时，希望从对应工作表提取首个有效名称填充到相邻的C列单元格；选择其他值时清空C列对应单元格。

目前通过Apps Script的enhancement.gs脚本实现（对应代码块注释为// PRODUCT NAME & OPTION NAME POPULATOR in cc sheet），选择并删除Add Product时功能正常，但选择Add Option时，所有C列的产品名称都会被替换成选项名称，请求解决该问题。

我尝试的代码 #

else if(sheet.getName() === "cc"){ //   PRODUCT NAME & OPTION NAME POPULATOR in cc sheet
  //  Getting the Object 
    const Filled_LAST_ROW_MPCC = sheet.getLastRow();
    var range = "B17:B"+Filled_LAST_ROW_MPCC;
    var values = sheet.getRange(range).getValues();
    flattenedValues = values.flat();
    var dictionary_mpcc = {};
    for (var i = 17; i <= Filled_LAST_ROW_MPCC; i++) {
        var cellValue = sheet.getRange("B" + i).getValue();
    
      if (!dictionary_mpcc["B" + i]) {
        if(cellValue === "Add Product"){
          dictionary_mpcc["B" + i] = cellValue;
        }
      }
    }
    console.log(dictionary_mpcc)


    //  Checking if the values contain "Add Product"
    if(flattenedValues.includes("Add Product")){
      // Getting the sheet and the values to fetch the Product name from
      prod_sheet = cpd.getSheetByName("Product Details");
      const Filled_LAST_ROW = prod_sheet.getLastRow();
      prod_sheet_values = prod_sheet.getRange("C13:C"+Filled_LAST_ROW).getValues();
      console.log("prod sheet values", prod_sheet_values);

      outputArray = [];
      //  Now, the since the product table can expand to make sure we opnly take the elements from table, the "Yes" values always comes afyer the table and hence breakig at that point to get the rale entries only and not the entries outside the table
      for (var i = 0; i < prod_sheet_values.length; i++) {
        var value = prod_sheet_values[i][0].trim();
        if ((value === 'Yes') || (value === 'No')) {
          break;
        }
        outputArray.push(value);
        // deleting empty values from the array
        outputArray = outputArray.filter(value => value.trim() !== "");
        
      }
      console.log("Product Names", outputArray)

      // Offset from the dictionary_mpcc and update that cell with the velue in outputArray
      var keys = Object.keys(dictionary_mpcc);
      if (outputArray.length === 1) {
        for (var i = 0; i < keys.length; i++) {
          sheet.getRange(keys[i]).offset(0, 1).setValue(outputArray[0]);
        }
      } else {
        for (var i = 0; i < Math.min(keys.length, outputArray.length); i++) {
          sheet.getRange(keys[i]).offset(0, 1).setValue(outputArray[i]);
        }
      }

    }
    
    var sheet = e.source.getActiveSheet();
    var range = e.range;
    var editedValue = e.value;
    if (sheet.getName() === "cc" &&
      range.getColumn() === 2 &&  // Assuming the dropdown is in column B
      editedValue !== "Add Product") {
    
      var adjacentCell = range.offset(0, 1);  // Offset to the adjacent cell
      adjacentCell.clearContent();  // Clear the adjacent cell value
    }
    

    if(flattenedValues.includes("Add Option")){
      // Getting the sheet and the values to fetch the Product name from
      option_sheet = cpd.getSheetByName("Option Details");
      const Filled_LAST_ROW_option = option_sheet.getLastRow();
      option_sheet_values = option_sheet.getRange("C13:C"+Filled_LAST_ROW_option).getValues();
      console.log("prod sheet values", prod_sheet_values);
      
      outputArrayoption = [];
      //  Now, the since the product table can expand to make sure we opnly take the elements from table, the "Yes" values always comes afyer the table and hence breakig at that point to get the rale entries only and not the entries outside the table
      for (var i = 0; i < option_sheet_values.length; i++) {
        var value = option_sheet_values[i][0].trim();
        if ((value === 'Yes') || (value === 'No')) {
          break;
        }
        outputArrayoption.push(value);
        // deleting empty values from the array
        outputArrayoption = outputArrayoption.filter(value => value.trim() !== "");
        
      }
      console.log("Option Names", outputArrayoption)

      // Offset from the dictionary_mpcc and update that cell with the velue in outputArray
      var keys = Object.keys(dictionary_mpcc);
      if (outputArrayoption.length === 1) {
        for (var i = 0; i < keys.length; i++) {
          sheet.getRange(keys[i]).offset(0, 1).setValue(outputArrayoption[0]);
        }
      } else {
        for (var i = 0; i < Math.min(keys.length, outputArrayoption.length); i++) {
          sheet.getRange(keys[i]).offset(0, 1).setValue(outputArrayoption[i]);
        }
      }
    }
    else if (sheet.getName() === "cc" &&
      range.getColumn() === 2 &&  // Assuming the dropdown is in column B
      editedValue !== "Add Option") {
    
      var adjacentCell = range.offset(0, 1);  // Offset to the adjacent cell
      adjacentCell.clearContent();  // Clear the adjacent cell value
    }
  }

问题原因 #

1. dictionary_mpcc仅收集Add Product的单元格，但处理Add Option时仍使用该字典的键，导致所有之前标记为Add Product的单元格被错误替换成选项名称。
2. 代码遍历整个B列进行批量修改，而非针对当前编辑的单个单元格操作，引发无差别覆盖。
3. 重复过滤数组的逻辑冗余，且条件判断存在冲突。

修正后的代码 #

function onEdit(e) {
  const cpd = e.source;
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const editedValue = e.value;

  // 仅处理cc工作表B列（Action列）且行号≥17的单元格
  if (sheet.getName() !== "cc" || range.getColumn() !== 2 || range.getRow() < 17) return;

  const adjacentCell = range.offset(0, 1);

  // 通用函数：获取目标工作表C列的首个有效名称
  const getFirstValidName = (sheetName) => {
    const targetSheet = cpd.getSheetByName(sheetName);
    const values = targetSheet.getRange("C13:C" + targetSheet.getLastRow()).getValues();
    for (const row of values) {
      const value = row[0].trim();
      if (value === "Yes" || value === "No") break;
      if (value) return value; // 返回首个非空有效名称
    }
    return "";
  };

  // 根据选择的Action执行对应操作
  if (editedValue === "Add Product") {
    adjacentCell.setValue(getFirstValidName("Product Details"));
  } else if (editedValue === "Add Option") {
    adjacentCell.setValue(getFirstValidName("Option Details"));
  } else {
    adjacentCell.clearContent();
  }
}

修改说明 #

1. 改为针对当前编辑的单个单元格处理，彻底避免批量错误覆盖问题。
2. 提取通用函数getFirstValidName，简化重复逻辑，高效获取目标工作表的首个有效名称。
3. 精简条件判断，仅在符合触发规则的情况下执行操作，提升脚本效率。
4. 移除冗余的字典收集和批量遍历逻辑，直接对当前单元格的相邻单元格进行操作，逻辑更清晰。

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