如何为Google Sheets联动下拉菜单脚本添加清除无效值功能？

针对你提到的两种无效输入场景：第一级列粘贴非下拉选项的文本、修改第一级后第二级值失效，以下是修改后的完整代码及逻辑说明：

完整代码 #

function dropdown() {
  var activeCell = SpreadsheetApp.getActiveRange();
  var activeRow = activeCell.getRow();
  var activeCol = activeCell.getColumn();
  var activeValue = activeCell.getValue();
  var activeSheet = activeCell.getSheet();

  if (activeSheet.getName() == "Main Sheet" && activeRow > 1 && activeCol == 3) {
    var worksheet = SpreadsheetApp.getActiveSpreadsheet();
    var dataSheet = worksheet.getSheetByName("Data");
    var data = dataSheet.getDataRange().getValues();
    var secondLevelList = data.filter(row => row[0] == activeValue).map(row => row[1]);
    
    // 更新第二级单元格的验证规则
    var validation = SpreadsheetApp.newDataValidation()
      .requireValueInList(secondLevelList)
      .setAllowInvalid(false)
      .build();
    var secondLevelCell = activeCell.offset(0, 1);
    secondLevelCell.setDataValidation(validation);
    
    // 清除失效的第二级值
    var secondLevelValue = secondLevelCell.getValue();
    if (secondLevelValue && !secondLevelList.includes(secondLevelValue)) {
      secondLevelCell.clearContent();
    }
  }
}

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var col = range.getColumn();
  
  // 检查并清除第一级列的无效输入
  if (sheet.getName() == "Main Sheet" && row > 1 && col == 3) {
    var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
    var firstLevelValidOptions = dataSheet.getDataRange().getValues().map(row => row[0]);
    var cellValue = range.getValue();
    
    if (cellValue && !firstLevelValidOptions.includes(cellValue)) {
      range.clearContent();
    }
  }
  
  // 执行联动下拉更新
  dropdown();
}

核心逻辑说明 #

• 第一级无效值处理：在编辑第3列（第一级下拉列）时，自动比对Data表中第一列的所有有效选项，若当前单元格的值不在有效列表内，直接清除内容，杜绝粘贴无效文本的问题。
• 第二级失效值处理：当第一级值修改后，更新第二级的验证规则同时，检查第二级现有值是否匹配新的有效选项，不匹配则立即清除，避免残留失效值。
• 兼容原有功能：保留了原有的多级联动逻辑，确保第一级选择变化时，第二级下拉选项自动同步更新。

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