如何用Google Apps Script实现Google Sheets整列联动下拉菜单

以下是优化后的代码，可实现SLEEPING ASSETS List - March表格中从第9行开始的整列S列，根据同行R列的选中值动态生成联动下拉菜单，选项来自DATA表格：

function onEdit(e) {
  var range = e.range;
  var spreadSheet = e.source;
  var sheetName = spreadSheet.getActiveSheet().getName();
  var column = range.getColumn();
  var row = range.getRow();
  var actionValue = e.value;
  
  // 仅处理目标表格的R列（第18列）且行号≥9的编辑操作
  if(sheetName === 'SLEEPING ASSETS List - March' && column === 18 && row >= 9) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var dataSheet = ss.getSheetByName("DATA");
    var mainSheet = ss.getSheetByName("SLEEPING ASSETS List - March");
    var targetCell = mainSheet.getRange(row, 19); // 对应同行的S列（第19列）
    
    // 清空目标单元格内容及旧的下拉规则
    targetCell.clearContent();
    targetCell.clearDataValidations();
    
    if(actionValue) {
      // 一次性获取DATA表格所有数据，提升性能
      var allData = dataSheet.getDataRange().getValues();
      var returnValues = [];
      
      // 遍历数据匹配R列选中值，收集对应的下拉选项
      for(var i = 1; i < allData.length; i++) { // 从第2行开始（数组索引1）
        if(allData[i][0] === actionValue) {
          returnValues.push(allData[i][1]);
        }
      }
      
      // 生成新的下拉规则并应用到目标单元格
      if(returnValues.length > 0) {
        var rule = SpreadsheetApp.newDataValidation()
          .requireValueInList(returnValues)
          .setAllowInvalid(false) // 可选：禁止输入非下拉选项的值
          .build();
        targetCell.setDataValidation(rule);
      }
    }
  }
}

关键优化点说明： #

• 扩展至整列：将原代码中row == 9的限制改为row >=9，覆盖从第9行开始的所有行。
• 动态单元格定位：使用mainSheet.getRange(row, 19)替代硬编码的S9，自动匹配同行的S列单元格。
• 性能优化：通过getDataRange().getValues()一次性获取DATA表格的所有数据，避免循环中反复调用getRange导致的性能损耗。
• 空值处理：当R列内容被清空时，自动清除对应S列的内容和下拉规则，保持数据一致性。
• 可选增强：添加setAllowInvalid(false)可强制用户只能选择下拉选项中的值，根据需求可移除该设置。

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