如何让Google Sheets自定义筛选脚本实现智能自动更新菜单？

核心逻辑 #

• 扫描表格首行，自动识别带T-前缀的列，提取行程名称（去掉前缀）
• 动态把这些行程名称添加为自定义菜单选项
• 用通用函数统一处理对应行程行的显示/隐藏，不用为每个行程写单独代码

完整脚本代码 #

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('行程筛选');
  
  // 获取所有带T-前缀的行程列
  const tripColumns = getTripColumns();
  
  // 给每个行程添加菜单选项
  tripColumns.forEach(trip => {
    menu.addItem(`显示${trip.name}相关装备`, 'toggleTripRows')
      .addSeparator();
  });
  
  // 加个显示所有行的选项
  menu.addItem('显示所有装备', 'showAllRows')
    .addToUi();
}

// 抓取带T-前缀的行程列信息（列索引、行程名称）
function getTripColumns() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const tripColumns = [];
  
  headerRow.forEach((header, index) => {
    if (typeof header === 'string' && header.startsWith('T-')) {
      tripColumns.push({
        colIndex: index + 1, // 表格列从1开始计数
        name: header.replace('T-', '')
      });
    }
  });
  
  return tripColumns;
}

// 通用函数：切换指定行程的行显示/隐藏
function toggleTripRows() {
  const ui = SpreadsheetApp.getUi();
  const menuItem = ui.getItemById(); // 获取触发的菜单选项文本
  const tripName = menuItem.replace('显示', '').replace('相关装备', '');
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const tripColumns = getTripColumns();
  const targetColumn = tripColumns.find(trip => trip.name === tripName)?.colIndex;
  
  if (!targetColumn) {
    ui.alert('未找到对应的行程列');
    return;
  }
  
  // 获取所有数据行（从第2行开始，你要是首行下面还有表头，自己改行数）
  const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  const values = dataRange.getValues();
  
  // 遍历每行，判断是否显示（这里假设标记为"是"或true的行需要显示，你可以改成自己的标记）
  values.forEach((row, rowIndex) => {
    const rowNum = rowIndex + 2; // 对应表格的实际行号
    const isRelevant = row[targetColumn - 1] === '是' || row[targetColumn - 1] === true;
    sheet.hideRows(rowNum);
    if (isRelevant) {
      sheet.showRows(rowNum);
    }
  });
}

// 一键显示所有行
function showAllRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.showRows(2, sheet.getLastRow() - 1);
}

使用步骤 #

1. 给行程类型列的首行标题加T-前缀，比如T-单日徒步、T-多日露营
2. 在对应行程列里，给需要显示的装备行标记是（或布尔值true，要改标记的话去脚本里改toggleTripRows的判断条件）
3. 刷新Google Sheets，顶部会出现「行程筛选」菜单，里面自动列出所有带T-前缀的行程选项
4. 新增或移动行程列后，刷新表格就能更新菜单选项

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