基于Google Apps Script实现列自动分组与今日列按需显示需求

实现逻辑 #

从冻结列（A、B列）之后的C列开始，遍历所有含日期的列：

• 识别每列日期所属周的周一，将该周的周二至周日列分组到对应周一列下并隐藏
• 始终保留A、B列和前5行可见

脚本代码 #

function groupColumnsByWeekAndShowOnlyMonday() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 遍历所有工作表，自动适配September、October等标签页
  const sheets = ss.getSheets(); 
  
  sheets.forEach(sheet => {
    // 跳过模板表，可根据实际命名调整判断条件
    if (sheet.getName().includes("模板")) return;
    
    const freezeColCount = sheet.getFrozenColumns();
    const startCol = freezeColCount + 1; // 从C列开始处理
    const lastCol = sheet.getLastColumn();
    
    if (startCol > lastCol) return;
    
    // 假设日期在第5行，可根据实际位置修改
    const dateRow = 5;
    const dateRange = sheet.getRange(dateRow, startCol, 1, lastCol - startCol + 1);
    const dates = dateRange.getValues()[0].map(cell => cell instanceof Date ? cell : null);
    
    let currentWeekMonday = null;
    let currentWeekCols = [];
    let mondayCol = null;
    
    dates.forEach((date, index) => {
      const col = startCol + index;
      if (!date) return;
      
      // 计算当前日期所属周的周一
      const dayNum = date.getDay(); // 0=周日，1=周一
      const calculatedMonday = new Date(date);
      calculatedMonday.setDate(date.getDate() - (dayNum === 0 ? 6 : dayNum - 1));
      
      // 进入新一周时，处理上一周的列分组与隐藏
      if (!currentWeekMonday || calculatedMonday.getTime() !== currentWeekMonday.getTime()) {
        if (currentWeekCols.length > 0) {
          sheet.groupColumns(currentWeekCols[0], currentWeekCols[currentWeekCols.length - 1]);
          currentWeekCols.forEach(c => c !== mondayCol && sheet.hideColumns(c));
        }
        currentWeekMonday = calculatedMonday;
        mondayCol = col;
        currentWeekCols = [col];
        sheet.showColumns(col);
      } else {
        currentWeekCols.push(col);
      }
    });
    
    // 处理最后一周的列
    if (currentWeekCols.length > 0) {
      sheet.groupColumns(currentWeekCols[0], currentWeekCols[currentWeekCols.length - 1]);
      currentWeekCols.forEach(c => c !== mondayCol && sheet.hideColumns(c));
    }
  });
}

保护范围的影响说明 #

如果目标列设置了保护：

• 确保运行脚本的账号拥有该保护范围的编辑权限
• 或者在保护设置中勾选「允许特定用户和脚本编辑」，将脚本运行账号加入白名单

问题修正 #

之前脚本导致全列消失，大概率是日期格式不匹配（单元格为文本而非日期格式）或未排除冻结列，以下是修正后的方案：

实现逻辑 #

• 假设复选框放在B1单元格，勾选时仅显示与今日日期匹配的列，取消勾选时恢复显示所有列
• 始终保留A、B列可见

脚本代码 #

function toggleTodayColumns() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const checkbox = sheet.getRange("B1");
  const isChecked = checkbox.getValue();
  
  const freezeColCount = sheet.getFrozenColumns();
  const startCol = freezeColCount + 1;
  const lastCol = sheet.getLastColumn();
  // 假设日期在第5行，可根据实际位置修改
  const dateRow = 5;
  
  if (startCol > lastCol) return;
  
  // 统一日期格式，去除时分秒避免匹配失败
  const today = new Date();
  today.setHours(0, 0, 0, 0);
  
  if (isChecked) {
    const dateRange = sheet.getRange(dateRow, startCol, 1, lastCol - startCol + 1);
    const dates = dateRange.getValues()[0].map(cell => {
      if (cell instanceof Date) {
        const d = new Date(cell);
        d.setHours(0, 0, 0, 0);
        return d;
      }
      return null;
    });
    
    // 先显示所有列，再隐藏不符合条件的列
    sheet.showColumns(startCol, lastCol - startCol + 1);
    dates.forEach((date, index) => {
      const col = startCol + index;
      if (!date || date.getTime() !== today.getTime()) sheet.hideColumns(col);
    });
  } else {
    // 取消勾选时显示所有列并展开分组
    sheet.showColumns(startCol, lastCol - startCol + 1);
    sheet.ungroupColumns(startCol, lastCol);
  }
}

// 创建复选框的编辑触发事件
function createCheckboxTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger("toggleTodayColumns")
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

使用步骤 #

1. 运行createCheckboxTrigger函数创建触发事件
2. 在B1单元格插入复选框（数据验证→勾选框）
3. 勾选复选框自动显示今日列，取消则恢复显示所有列

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