生成全年日期过滤超链接及多条件过滤的Google Apps Script求助

问题分析 #

原脚本的核心问题是：普通HYPERLINK公式只能跳转工作表，无法自动执行数据过滤逻辑。要实现点击链接后自动过滤数据，需要结合自定义脚本函数和单元格选择事件监听来完成。

完整脚本代码 #

替换原createLinks函数，并新增过滤逻辑与事件监听函数：

// 生成全年日期超链接
function createLinks() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let dataSheet = ss.getSheetByName("Data");
  let linksSheet = ss.getSheetByName("Links");

  // 自动创建缺失的工作表
  if (!dataSheet) {
    dataSheet = ss.insertSheet("Data");
    // 给Data表添加表头示例（可选）
    dataSheet.getRange("A1:C1").setValues([["关键字", "内容", "日期"]]);
  }
  if (!linksSheet) {
    linksSheet = ss.insertSheet("Links");
    linksSheet.getRange("A1").setValue("2023年日期链接");
  }

  const startDate = new Date(2023, 0, 1);
  const endDate = new Date(2023, 11, 31);
  let currentDate = new Date(startDate); // 避免修改原日期对象
  let row = 2; // 从第2行开始放日期链接

  // 清空原有链接（可选）
  linksSheet.getRange(2, 1, linksSheet.getLastRow() - 1, 1).clearContent();

  while (currentDate <= endDate) {
    // 格式化日期为yyyy-MM-dd，确保和Data表日期格式兼容
    const dateString = Utilities.formatDate(currentDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
    // 生成跳转至Data表的超链接
    const linkFormula = `=HYPERLINK("#gid=${dataSheet.getSheetId()}", "${dateString}")`;
    
    linksSheet.getRange(row, 1).setFormula(linkFormula);
    // 给单元格添加备注，标注过滤条件（可选）
    linksSheet.getRange(row, 1).setNote(`过滤条件：日期=${dateString} 且 A列包含"xyz"`);
    
    currentDate.setDate(currentDate.getDate() + 1);
    row++;
  }
}

// 数据过滤函数：按日期和关键字过滤
function filterData(targetDate, keyword = "xyz") {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("Data");
  const dataRange = dataSheet.getDataRange();
  
  // 清除现有过滤
  dataSheet.getFilter()?.remove();
  
  // 创建新过滤器
  const filter = dataRange.createFilter();
  const dateColumn = 3; // C列
  const keywordColumn = 1; // A列

  // 设置日期过滤条件（精确匹配）
  const dateFilterCriteria = SpreadsheetApp.newFilterCriteria()
    .whenDateEqualTo(new Date(targetDate))
    .build();
  
  // 设置关键字过滤条件（包含指定文本）
  const keywordFilterCriteria = SpreadsheetApp.newFilterCriteria()
    .whenTextContains(keyword)
    .build();

  // 应用过滤条件
  filter.setColumnFilterCriteria(dateColumn, dateFilterCriteria);
  filter.setColumnFilterCriteria(keywordColumn, keywordFilterCriteria);
}

// 监听单元格选择事件：点击Links表日期链接跳转后自动触发过滤
function onSelectionChange(e) {
  const activeSheet = e.source.getActiveSheet();
  const prevSheet = e.oldRange?.getSheet();

  // 判断是否从Links表跳转至Data表
  if (activeSheet.getName() === "Data" && prevSheet?.getName() === "Links") {
    const clickedDate = e.oldRange.getValue();
    if (clickedDate instanceof Date || !isNaN(Date.parse(clickedDate))) {
      const targetDate = Utilities.formatDate(new Date(clickedDate), Session.getScriptTimeZone(), "yyyy-MM-dd");
      filterData(targetDate);
    }
  }
}

// 初始化菜单：方便手动触发生成链接
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("日期链接工具")
    .addItem("生成全年日期链接", "createLinks")
    .addToUi();
}

使用说明 #

1. 授权脚本：首次运行createLinks或打开表格时，会提示授权，按流程完成即可（需信任自己编写的脚本）。
2. 生成链接：点击表格顶部的「日期链接工具」→「生成全年日期链接」，Links表会自动生成2023年所有日期的超链接。
3. 触发过滤：点击Links表中的任意日期链接，会自动跳转到Data表，并过滤出C列日期匹配且**A列包含"xyz"**的数据。

关键改进点 #

• 替换原无效的HYPERLINK格式，改用工作表跳转结合onSelectionChange事件自动触发过滤。
• 统一日期格式为yyyy-MM-dd，避免时区或格式差异导致的过滤失效。
• 新增自动创建表头、清空旧链接、初始化菜单等实用功能。
• 可通过修改filterData函数中的keyword参数，自定义A列的过滤文本。

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