修复Google Apps Script代码：解决谷歌表格月度笔记列显示失效问题

问题场景 #

谷歌表格中I至T列依次对应"January 2024"至"December 2024"（当前处于隐藏状态），H列配置了包含上述月份的下拉选择菜单。需求是通过下拉选择对应月份，仅显示该月份的笔记列，但现有代码无任何效果。

原代码如下：

function onEdit(e) {
  // Ensure the function only runs during actual edits
  if (!e) return;  // If no event object (e), stop the execution to avoid errors

  var sheet = e.source.getActiveSheet();
  
  // Ensure the edit was made in column H (8th column) and after the header row
  if (e.range.getColumn() == 8 && e.range.getRow() > 1) {
    var selectedMonth = e.range.getValue(); // Get the value of the selected month

    // Define the mapping of months to columns
    var monthMapping = {
      "January 2024": 9,   // Column I
      "February 2024": 10, // Column J
      "March 2024": 11,    // Column K
      "April 2024": 12,    // Column L
      "May 2024": 13,      // Column M
      "June 2024": 14,     // Column N
      "July 2024": 15,     // Column O
      "August 2024": 16,   // Column P
      "September 2024": 17,// Column Q
      "October 2024": 18,  // Column R
      "November 2024": 19, // Column S
      "December 2024": 20  // Column T
    };

    // Notes columns from I to T (9 to 20)
    var notesColumnStart = 9;
    var notesEndColumn = 20;

    // Unhide all columns from I to T
    sheet.showColumns(notesColumnStart, notesEndColumn - notesColumnStart + 1);
    
    // Check if the selected month exists in the mapping
    if (selectedMonth in monthMapping) {
      var selectedColumn = monthMapping[selectedMonth]; // Get the corresponding column number
      
      // Check if the selected column is already visible
      var isColumnVisible = !sheet.isColumnHiddenByUser(selectedColumn);
      
      if (isColumnVisible) {
        // If the column is visible, re-hide it (toggle behavior)
        sheet.hideColumns(selectedColumn);
        Logger.log("Hiding column: " + selectedColumn);
      } else {
        // If the column is hidden, show it and hide all others
        Logger.log("Showing column: " + selectedColumn);
        for (var col = notesColumnStart; col <= notesEndColumn; col++) {
          if (col !== selectedColumn) {
            sheet.hideColumns(col);  // Hide the column if it is not the selected month
          }
        }
        sheet.showColumns(selectedColumn); // Make sure the selected column is shown
      }
    } else {
      // If the selected month isn't in the mapping, log the issue
      Logger.log("Month not found in the mapping: " + selectedMonth);
    }
  }
}

问题排查 #

1. HTML转义字符导致条件失效：代码中使用了&&（对应&&）和>（对应>）这类HTML转义字符，JavaScript无法识别，直接导致列判断和行判断的条件永远不成立，核心逻辑完全没执行。
2. 冗余的全列显示逻辑：先执行showColumns把所有月度列显示出来，再进行隐藏操作，既不符合需求，也会造成不必要的界面波动。
3. 切换逻辑偏离需求：原代码的“显示则隐藏，隐藏则显示”的切换逻辑，和“选中月份仅显示对应列”的需求不符，用户选择月份的预期是直接显示目标列，而非切换状态。

修复后的代码 #

function onEdit(e) {
  if (!e) return;

  var sheet = e.source.getActiveSheet();
  // 修正HTML转义字符，确保条件判断生效
  if (e.range.getColumn() == 8 && e.range.getRow() > 1) {
    var selectedMonth = e.range.getValue();

    var monthMapping = {
      "January 2024": 9,
      "February 2024": 10,
      "March 2024": 11,
      "April 2024": 12,
      "May 2024": 13,
      "June 2024": 14,
      "July 2024": 15,
      "August 2024": 16,
      "September 2024": 17,
      "October 2024": 18,
      "November 2024": 19,
      "December 2024": 20
    };

    var notesColumnStart = 9;
    var notesEndColumn = 20;

    // 处理空值情况：选中空值时隐藏所有月度列
    if (!selectedMonth) {
      sheet.hideColumns(notesColumnStart, notesEndColumn - notesColumnStart + 1);
      Logger.log("All month columns hidden");
      return;
    }

    if (selectedMonth in monthMapping) {
      var selectedColumn = monthMapping[selectedMonth];
      // 遍历所有月度列，仅保留选中列显示
      for (var col = notesColumnStart; col <= notesEndColumn; col++) {
        if (col === selectedColumn) {
          sheet.showColumns(col);
        } else {
          sheet.hideColumns(col);
        }
      }
      Logger.log("Showing only column: " + selectedColumn);
    } else {
      Logger.log("Month not found in mapping: " + selectedMonth);
    }
  }
}

关键修改说明 #

• 修正转义字符：将&&改为&&，>改为>，确保列和行的判断条件能正常触发。
• 移除冗余逻辑：删除了先全显示所有列的操作，直接根据选中状态处理每一列的显示/隐藏。
• 贴合需求的逻辑调整：去掉了切换逻辑，改为选中月份后直接隐藏所有非目标列，仅显示目标列，确保操作结果符合预期。
• 增加空值处理：当下拉菜单选择空值时，自动隐藏所有月度列，完善使用场景。

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