如何修复Google Apps Script脚本，实现日期范围按钮与月份数据筛选

修改后实现的功能 #

1. 先弹出输入框获取用户姓名
2. 接着弹出输入框要求输入月份（格式支持September 2024或2024-09）
3. 从「Monitoring (Database)」工作表中筛选出姓名匹配且记录日期在指定月份内的数据
4. 新建（或覆盖）同名工作表展示指定列数据，表头正确显示

完整优化代码 #

function showInputBox() {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dbSheet = ss.getSheetByName("Monitoring (Database)");
  
  // 步骤1：获取用户姓名
  const nameInput = ui.prompt("请输入你的名字", ui.ButtonSet.OK_CANCEL);
  if (nameInput.getSelectedButton() !== ui.Button.OK) {
    ui.alert("操作已取消");
    return;
  }
  const targetName = nameInput.getResponseText().trim().toLowerCase();
  if (!targetName) {
    ui.alert("请输入有效的名字");
    return;
  }

  // 步骤2：获取目标月份
  const monthInput = ui.prompt("请输入月份（格式示例：September 2024 或 2024-09）", ui.ButtonSet.OK_CANCEL);
  if (monthInput.getSelectedButton() !== ui.Button.OK) {
    ui.alert("操作已取消");
    return;
  }
  const monthStr = monthInput.getResponseText().trim();
  // 解析月份为日期范围
  const dateRange = parseMonthToDateRange(monthStr);
  if (!dateRange) {
    ui.alert("月份格式不正确，请重新输入");
    return;
  }
  const { startOfMonth, endOfMonth } = dateRange;

  // 读取数据库数据
  const lastRow = dbSheet.getLastRow();
  if (lastRow < 2) {
    ui.alert("数据库中无数据");
    return;
  }
  const data = dbSheet.getRange(2, 1, lastRow - 1, 24).getValues(); // A2:X对应24列

  // 双重筛选：姓名匹配 + 日期在指定月份内
  const filteredData = data.filter(row => {
    const rowName = row[23]?.toString().toLowerCase().trim();
    const startDate = row[18]; // 对应原代码的START DATE列
    // 检查姓名匹配，且日期是有效日期并在目标月份范围内
    return rowName === targetName && startDate instanceof Date && startDate >= startOfMonth && startDate <= endOfMonth;
  });

  if (filteredData.length === 0) {
    ui.alert(`未找到${targetName}在指定月份的匹配数据`);
    return;
  }

  // 提取指定列数据（对应原需求的列顺序）
  const selectedColumns = filteredData.map(row => [
    row[10], // PLATFORM
    row[9],  // TYPE OF ACCESS
    row[1],  // INSTITUTION
    row[17], // QUANTITY/ACCESS CODE
    row[18], // START DATE
    row[19], // START TIME
    row[20], // END DATE
    row[21], // END TIME
    row[22]  // TOTAL TIME
  ]);

  // 处理结果工作表：存在则删除，再新建
  let resultSheet = ss.getSheetByName(targetName);
  if (resultSheet) {
    ss.deleteSheet(resultSheet);
  }
  resultSheet = ss.insertSheet(targetName);

  // 写入表头和数据
  const headers = ["PLATFORM", "TYPE OF ACCESS", "INSTITUTION", "QUANTITY/ACCESS CODE", "START DATE", "START TIME", "END DATE", "END TIME", "TOTAL TIME"];
  resultSheet.getRange(3, 3, 1, headers.length).setValues([headers]);
  resultSheet.getRange(4, 3, selectedColumns.length, selectedColumns[0].length).setValues(selectedColumns);

  // 格式化日期列（可选，优化显示）
  resultSheet.getRange(4, 3 + 4, selectedColumns.length, 2).setNumberFormat("yyyy-MM-dd"); // START DATE和END DATE列
  ui.alert(`已成功生成${targetName}的指定月份数据，查看工作表：${targetName}`);
}

// 辅助函数：解析月份字符串为该月的起始和结束日期
function parseMonthToDateRange(monthStr) {
  let dateObj;
  // 处理"Month YYYY"格式（如September 2024）
  if (/^[A-Za-z]+ \d{4}$/.test(monthStr)) {
    dateObj = new Date(monthStr);
  } 
  // 处理"YYYY-MM"格式（如2024-09）
  else if (/^\d{4}-\d{2}$/.test(monthStr)) {
    dateObj = new Date(monthStr + "-01");
  } else {
    return null;
  }

  if (isNaN(dateObj.getTime())) {
    return null;
  }

  // 计算该月第一天和最后一天
  const startOfMonth = new Date(dateObj.getFullYear(), dateObj.getMonth(), 1);
  const endOfMonth = new Date(dateObj.getFullYear(), dateObj.getMonth() + 1, 0, 23, 59, 59);
  return { startOfMonth, endOfMonth };
}

关键改动说明 #

• 新增月份输入与解析：通过parseMonthToDateRange函数将用户输入的月份字符串转换成该月的起始和结束日期，确保日期筛选的准确性
• 双重筛选逻辑：同时校验姓名匹配和记录日期是否在目标月份范围内，避免无效数据
• 工作表冲突处理：如果已存在同名结果表，先删除再创建，避免重复创建报错
• 修复表头覆盖问题：原代码先写入数据再写表头，会覆盖第一行数据，现在调整为先写表头再写数据
• 日期格式化：对结果表中的日期列设置统一格式，提升可读性
• 输入校验：增加空输入、无效格式的判断，给出明确提示

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