Google Sheets脚本按年份过滤数据失败：无法显示2024年全部记录求助

问题描述 #

需要解决Google Sheets的数据过滤问题：输入年份（如2024）后，无法展示该年份的全部相关记录，当前使用的showInputYear脚本无法正常工作。以下是原脚本代码及相关截图：

function showInputYear() {
  var ui = SpreadsheetApp.getUi();
  var input = ui.prompt("Please enter your First Name.", ui.ButtonSet.OK_CANCEL);

  if (input.getSelectedButton() == ui.Button.OK) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getSheetByName("Monitoring (Database)");
    var data = ws.getRange("A2:X" + ws.getLastRow()).getValues();
    var userSelectedRep = input.getResponseText().toLowerCase();

    var newData = data.filter(function (r) {
      return r[23].toLowerCase() == userSelectedRep;
    });

    var yearInput = ui.prompt("Please enter the year (e.g., 2024):", ui.ButtonSet.OK_CANCEL);

    if (yearInput.getSelectedButton() == ui.Button.OK) {
      var selectedYear = yearInput.getResponseText();
      var filteredYearData = newData.filter(function (r) {
        var date = new Date(r[3]);
        return date.toLocaleString('default', { year: 'numeric' }).toLowerCase() === selectedYear;
      });

      var selectedColumns = filteredYearData.map(function (r) {
        return [r[3], r[10], r[9], r[1], r[17], r[18], r[19], r[20], r[21], r[22]];
      });

      if (filteredYearData.length > 0) {
        var newWs = ss.insertSheet(userSelectedRep);
        var headers = ["YEAR", "PLATFORM", "TYPE OF ACCESS", "INSTITUTION", "QUANTITY/ACCESS CODE", "START DATE", "START TIME", "END DATE", "END TIME", "TOTAL TIME"];

        newWs.getRange(4, 3, selectedColumns.length, selectedColumns[0].length).setValues(selectedColumns);
        newWs.getRange(3, 3, 1, headers.length).setValues([headers]);

      } else {
        ui.alert("No matching data found for the entered year.");
      }
    } else {
      ui.alert("Year input canceled.");
    }
  } else {
    ui.alert("Operation Canceled.");
  }
}

相关截图 #

• 输入界面示例：
• 数据表格示例：
• 脚本运行结果示例：

故障排查与修复方案 #

核心问题点 #

1. 日期转换不可靠：new Date(r[3])对文本格式日期或非标准日期转换时，会生成无效日期对象，导致年份匹配失败。
2. 冗余大小写处理：年份为数字类型，无需调用toLowerCase()，反而会引入匹配误差。
3. 无无效日期判断：未校验日期是否有效，无效日期返回NaN直接参与匹配，导致过滤错误。
4. 无输入合法性校验：未验证年份输入是否为有效数字，非数字输入会引发逻辑错误。

修复后的代码 #

function showInputYear() {
  var ui = SpreadsheetApp.getUi();
  var input = ui.prompt("请输入你的名字：", ui.ButtonSet.OK_CANCEL);

  if (input.getSelectedButton() == ui.Button.OK) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ws = ss.getSheetByName("Monitoring (Database)");
    var data = ws.getRange("A2:X" + ws.getLastRow()).getValues();
    var userSelectedRep = input.getResponseText().toLowerCase();

    // 过滤空值记录，避免空字符串匹配错误
    var newData = data.filter(function (r) {
      return r[23] && r[23].toLowerCase() === userSelectedRep;
    });

    var yearInput = ui.prompt("请输入年份（例如：2024）：", ui.ButtonSet.OK_CANCEL);

    if (yearInput.getSelectedButton() == ui.Button.OK) {
      var selectedYear = parseInt(yearInput.getResponseText(), 10);
      // 校验年份输入是否为有效数字
      if (isNaN(selectedYear)) {
        ui.alert("请输入有效的数字年份！");
        return;
      }

      var filteredYearData = newData.filter(function (r) {
        var dateValue = r[3];
        var date;
        // 区分Google Sheets原生Date对象与文本日期，保证转换有效性
        if (dateValue instanceof Date) {
          date = dateValue;
        } else {
          date = new Date(dateValue);
        }
        // 仅匹配有效日期且年份一致的记录
        return !isNaN(date.getTime()) && date.getFullYear() === selectedYear;
      });

      var selectedColumns = filteredYearData.map(function (r) {
        return [r[3], r[10], r[9], r[1], r[17], r[18], r[19], r[20], r[21], r[22]];
      });

      if (filteredYearData.length > 0) {
        // 避免重复创建同名工作表
        try {
          var existingSheet = ss.getSheetByName(userSelectedRep);
          if (existingSheet) {
            ss.deleteSheet(existingSheet);
          }
          var newWs = ss.insertSheet(userSelectedRep);
        } catch (e) {
          ui.alert("创建工作表失败：" + e.message);
          return;
        }
        var headers = ["年份", "平台", "访问类型", "机构", "数量/访问码", "开始日期", "开始时间", "结束日期", "结束时间", "总时长"];

        // 先写入表头再写入数据，避免格式混乱
        newWs.getRange(3, 3, 1, headers.length).setValues([headers]);
        newWs.getRange(4, 3, selectedColumns.length, selectedColumns[0].length).setValues(selectedColumns);

      } else {
        ui.alert("未找到该年份的匹配数据。");
      }
    } else {
      ui.alert("年份输入已取消。");
    }
  } else {
    ui.alert("操作已取消。");
  }
}

修复说明 #

• 精准日期处理：区分原生Date对象与文本日期，确保日期转换有效性。
• 直接年份匹配：使用date.getFullYear()获取数字年份，与输入的数字年份直接比较，消除字符串匹配误差。
• 输入合法性校验：增加年份输入的数字验证，避免非数字输入导致的逻辑错误。
• 重复工作表处理：创建新表前检查并删除同名表，避免报错。
• 空值过滤：提前过滤空记录，避免空字符串匹配错误。

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