Google Sheets一级依赖下拉列表双向联动功能优化问询

需求概述 #

我有一份包含4列核心数据的Google表格：

• Group（黑色金属/有色金属等）
• RI No.（唯一标识符）
• Item Description（RI No.对应物品名称）
• Supplier（物品对应供应商）

原有联动流程 #

选择Group→RI No.列自动生成对应类别下所有RI No.的下拉列表→选择RI No.后通过VLOOKUP自动填充Item Description→Supplier列生成对应物品的供应商下拉列表。

优化需求 #

1. 选择Group后，RI No.列和Item Description列均自动生成对应类别下的下拉列表（RI No.下拉为对应Group的所有唯一标识符，Item Description下拉为对应Group的所有物品名称）
2. 实现双向填充：选择RI No.自动填充Item Description，选择Item Description自动填充RI No.
3. Supplier列的下拉逻辑保持原有规则不变

优化后的完整代码 #

var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var wsopt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sorted items");
var opts = wsopt.getRange(2, 1, wsopt.getLastRow() - 1, 13).getValues(); 
// 定义列常量，对应表格列位置
var groupColumn = 2; // Group列
var riNoColumn = 5; // RI No.列
var itemDescColumn = 6; // Item Description列
var supplierColumn = 7; // Supplier列
var wsopt2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("supplier details");
var opts2 = wsopt2.getRange(3, 1, wsopt2.getLastRow() - 1, 12).getValues(); 

function onEdit(e) { 
  var activeCell = e.range;
  var val = activeCell.getValue();
  var row = activeCell.getRow();
  var col = activeCell.getColumn();
  var sheetName = activeCell.getSheet().getName();

  // 仅处理JPS和DDNI工作表，且行号大于2（表头行）
  if ((sheetName === "JPS" || sheetName === "DDNI") && row > 2) {
    if (col === groupColumn) {
      applyGroupValidation(val, row);
    } else if (col === riNoColumn) {
      syncItemDescFromRiNo(val, row);
      applySupplierValidation(row);
    } else if (col === itemDescColumn) {
      syncRiNoFromItemDesc(val, row);
      applySupplierValidation(row);
    }
  }
}

// 选择Group后，给RI No.和Item Description列添加对应下拉
function applyGroupValidation(val, row) {
  // 清空当前行的关联列内容和验证规则
  ws.getRange(row, riNoColumn).clearContent().clearDataValidations();
  ws.getRange(row, itemDescColumn).clearContent().clearDataValidations();
  ws.getRange(row, supplierColumn).clearContent().clearDataValidations();

  if (val === "") return;

  // 过滤当前Group下的所有条目
  var filteredItems = opts.filter(function(item) {
    return item[0] === val;
  });

  // 生成RI No.下拉列表
  var riNoList = filteredItems.map(function(item) {
    return item[1];
  });
  applyValidationToCell(riNoList, ws.getRange(row, riNoColumn));

  // 生成Item Description下拉列表
  var itemDescList = filteredItems.map(function(item) {
    return item[2]; // 对应sorted items表中Item Description的列索引
  });
  applyValidationToCell(itemDescList, ws.getRange(row, itemDescColumn));
}

// 选择RI No.时，自动填充对应的Item Description
function syncItemDescFromRiNo(val, row) {
  if (val === "") {
    ws.getRange(row, itemDescColumn).clearContent();
    return;
  }

  var groupVal = ws.getRange(row, groupColumn).getValue();
  var matchedItem = opts.find(function(item) {
    return item[0] === groupVal && item[1] === val;
  });

  if (matchedItem) {
    ws.getRange(row, itemDescColumn).setValue(matchedItem[2]);
  }
}

// 选择Item Description时，自动填充对应的RI No.
function syncRiNoFromItemDesc(val, row) {
  if (val === "") {
    ws.getRange(row, riNoColumn).clearContent();
    return;
  }

  var groupVal = ws.getRange(row, groupColumn).getValue();
  var matchedItem = opts.find(function(item) {
    return item[0] === groupVal && item[2] === val;
  });

  if (matchedItem) {
    ws.getRange(row, riNoColumn).setValue(matchedItem[1]);
  }
}

// 生成Supplier下拉列表（保持原有逻辑）
function applySupplierValidation(row) {
  ws.getRange(row, supplierColumn).clearContent().clearDataValidations();

  var groupVal = ws.getRange(row, groupColumn).getValue();
  var riNoVal = ws.getRange(row, riNoColumn).getValue();

  if (!groupVal || !riNoVal) return;

  // 过滤对应Group和RI No.的供应商列表
  var filteredSuppliers = opts2.filter(function(supplier) {
    return supplier[6] === groupVal && supplier[4] === riNoVal;
  });

  var supplierList = filteredSuppliers.map(function(supplier) {
    return supplier[0];
  });
  applyValidationToCell(supplierList, ws.getRange(row, supplierColumn));
}

// 通用数据验证设置函数
function applyValidationToCell(list, cell) { 
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
  cell.setDataValidation(rule);
}

代码关键修改说明 #

1. 列常量重命名：将原模糊的firstlevelcolumn等命名替换为groupColumn、riNoColumn等语义化名称，提升代码可读性
2. 扩展编辑事件监听：新增对Item Description列的编辑监听，触发双向同步和供应商下拉生成逻辑
3. Group选择逻辑增强：选择Group后同时生成RI No.和Item Description的下拉列表，分别提取对应数据源
4. 双向同步逻辑：新增syncItemDescFromRiNo和syncRiNoFromItemDesc函数，实现RI No.与Item Description的互相自动填充
5. 供应商逻辑适配：调整applySupplierValidation函数，确保无论通过RI No.还是Item Description触发，都能正确获取RI No.并生成对应供应商下拉

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