请求完善Google Sheets四级联动数据验证脚本

以下是完整的Google Apps Script代码，包含三级（Info列）和四级（Tarif列）联动验证函数，并整合到onEdit触发器中，直接基于"Tarifs - Data validation"工作表的数据源生成验证规则，避免手动维护筛选表导致的失效问题。

核心函数实现 #

1. 三级联动验证（Info列） #

function applyThirdLevelValidation(e) {
  const mainSheet = e.source.getActiveSheet();
  // 主表列索引：Type=4, bill to=5, Trip=6, Info=7（1-based，与工作表界面列号一致）
  const editedCol = e.range.getColumn();
  const editedRow = e.range.getRow();
  
  // 仅当编辑Trip列（第6列）且不是表头行时触发
  if (editedCol !== 6 || editedRow === 1) return;
  
  const typeValue = mainSheet.getRange(editedRow, 4).getValue();
  const billToValue = mainSheet.getRange(editedRow, 5).getValue();
  const tripValue = e.range.getValue();
  
  if (!typeValue || !billToValue || !tripValue) {
    // 清空Info列验证并清除内容，同时同步清空Tarif列
    mainSheet.getRange(editedRow, 7).clearDataValidations().clearContent();
    mainSheet.getRange(editedRow, 8).clearDataValidations().clearContent();
    return;
  }
  
  // 从数据源表获取匹配的Info选项
  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tarifs - Data validation");
  const allData = dataSheet.getDataRange().getValues();
  
  // 筛选符合Type、bill to、Trip的唯一Info值，过滤空值
  const infoOptions = [...new Set(allData.filter(row => 
    row[3] === typeValue && row[4] === billToValue && row[5] === tripValue
  ).map(row => row[6]))].filter(val => val !== "");
  
  // 设置数据验证规则
  if (infoOptions.length > 0) {
    const rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(infoOptions, true)
      .setAllowInvalid(false)
      .build();
    mainSheet.getRange(editedRow, 7).setDataValidation(rule).clearContent();
  } else {
    mainSheet.getRange(editedRow, 7).clearDataValidations().clearContent();
  }
  
  // 同步清空Tarif列的验证和内容
  mainSheet.getRange(editedRow, 8).clearDataValidations().clearContent();
}

2. 四级联动验证（Tarif列） #

function applyFourthLevelValidation(e) {
  const mainSheet = e.source.getActiveSheet();
  // 主表列索引：Type=4, bill to=5, Trip=6, Info=7, Tarif=8（1-based）
  const editedCol = e.range.getColumn();
  const editedRow = e.range.getRow();
  
  // 仅当编辑Info列（第7列）且不是表头行时触发
  if (editedCol !== 7 || editedRow === 1) return;
  
  const typeValue = mainSheet.getRange(editedRow, 4).getValue();
  const billToValue = mainSheet.getRange(editedRow, 5).getValue();
  const tripValue = mainSheet.getRange(editedRow, 6).getValue();
  const infoValue = e.range.getValue();
  
  if (!typeValue || !billToValue || !tripValue || !infoValue) {
    // 清空Tarif列验证并清除内容
    mainSheet.getRange(editedRow, 8).clearDataValidations().clearContent();
    return;
  }
  
  // 从数据源表获取匹配的Tarif选项
  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tarifs - Data validation");
  const allData = dataSheet.getDataRange().getValues();
  
  // 筛选符合条件的唯一Tarif值，过滤空值
  const tarifOptions = [...new Set(allData.filter(row => 
    row[3] === typeValue && row[4] === billToValue && row[5] === tripValue && row[6] === infoValue
  ).map(row => row[7]))].filter(val => val !== "");
  
  // 设置数据验证规则
  if (tarifOptions.length > 0) {
    const rule = SpreadsheetApp.newDataValidation()
      .requireValueInList(tarifOptions, true)
      .setAllowInvalid(false)
      .build();
    mainSheet.getRange(editedRow, 8).setDataValidation(rule).clearContent();
  } else {
    mainSheet.getRange(editedRow, 8).clearDataValidations().clearContent();
  }
}

整合到onEdit触发器 #

将以下代码替换或添加到你的脚本中，实现编辑时自动触发对应层级的验证：

function onEdit(e) {
  if (!e) throw new Error("请通过工作表编辑触发此函数，请勿直接运行");
  
  // 保留你已实现的二级验证逻辑（例如applySecondLevelValidation(e)）
  // applySecondLevelValidation(e);
  
  // 触发三级验证
  applyThirdLevelValidation(e);
  
  // 触发四级验证
  applyFourthLevelValidation(e);
}

注意事项 #

• 代码中列索引采用1-based（与Google Sheets界面列号一致），如果你的主表列顺序不同，请自行调整editedCol、getRange中的列参数。
• 数据源表"Tarifs - Data validation"的列顺序需与主表对应：Type(4), bill to(5), Trip(6), Info(7), Tarif(8)（1-based），若数据源表列顺序不同，需修改row[3]、row[4]等0-based数组索引值。
• 当上游列（Type/bill to/Trip/Info）内容清空时，下游列会自动清除验证规则和内容，避免无效数据残留。

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