如何用Google Apps Script从表单关联表格拉取当周最新X条提交数据？

完全可以通过Google Apps Script实现你的需求，以下是匹配你需求的具体实现步骤和代码：

核心功能代码 #

function syncWeeklyTimesheet() {
  // 替换为你的表单响应表ID和目标表ID
  const formResponseSheetId = "你的表单响应表ID";
  const targetSheetId = "你的目标表格ID";
  
  // 获取表单响应表和目标表
  const formSheet = SpreadsheetApp.openById(formResponseSheetId).getSheets()[0];
  const targetSheet = SpreadsheetApp.openById(targetSheetId).getSheets()[0];
  
  // 获取表单响应的所有数据（跳过表头）
  const formData = formSheet.getDataRange().getValues().slice(1);
  if (formData.length === 0) return;
  
  // 定义员工名单（需和实际员工一致，后续变动要更新）
  const allEmployees = ["张三", "李四", "王五", "赵六"];
  
  // 获取当前周的起始和结束日期（以周一为一周开始，可根据需求调整）
  const today = new Date();
  const startOfWeek = new Date(today.setDate(today.getDate() - today.getDay() + 1));
  const endOfWeek = new Date(startOfWeek);
  endOfWeek.setDate(endOfWeek.getDate() + 6);
  
  // 筛选当周提交的记录
  const weeklyRecords = formData.filter(row => {
    const submitDate = new Date(row[0]); // 第一列是时间戳
    return submitDate >= startOfWeek && submitDate <= endOfWeek;
  });
  
  // 清空目标表已有数据（如果周二的清空脚本已处理，可注释此行）
  targetSheet.getDataRange().clearContent();
  
  // 写入表头（根据你的目标表格格式调整）
  const headers = ["员工姓名", "本周工时", "提交时间"];
  targetSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  
  // 写入当周工时记录
  if (weeklyRecords.length > 0) {
    // 提取需要的字段（假设表单里第2列是姓名，第3列是工时，第1列是时间戳）
    const outputData = weeklyRecords.map(row => [row[1], row[2], row[0]]);
    targetSheet.getRange(2, 1, outputData.length, outputData[0].length).setValues(outputData);
  }
  
  // 统计未提交工时的员工
  const submittedEmployees = weeklyRecords.map(row => row[1]);
  const missingEmployees = allEmployees.filter(emp => !submittedEmployees.includes(emp));
  
  // 将未提交名单写入目标表的指定位置（比如E列第1行开始）
  targetSheet.getRange(1, 5).setValue("未提交工时员工");
  if (missingEmployees.length > 0) {
    targetSheet.getRange(2, 5, missingEmployees.length, 1).setValues(missingEmployees.map(emp => [emp]));
  } else {
    targetSheet.getRange(2, 5).setValue("全部已提交");
  }
}

关键代码说明 #

• 当周数据筛选：以周一作为一周的起始，通过对比提交时间是否在当周范围内来筛选记录，可根据公司的周历调整起始日（比如改为周日）。
• 未提交员工统计：通过对比预设的员工名单和已提交记录中的姓名，找出未提交的员工。
• 字段映射：代码中假设表单的第1列是时间戳、第2列是员工姓名、第3列是本周工时，需根据你实际的表单字段顺序调整索引。

触发器设置 #

1. 打开Google表格的脚本编辑器（「扩展程序」→「Apps Script」）。
2. 点击左侧菜单的「触发器」图标，添加新触发器：
   • 选择运行函数：syncWeeklyTimesheet
   • 事件源：「时间驱动」
   • 类型：「周计时器」
   • 星期：「周三」
   • 时间时段：选择你需要的晚上时段（如20:00-21:00）

注意事项 #

• 首次运行脚本时，需要授权脚本访问你的Google表格和表单数据。
• 员工名单数组allEmployees需要定期更新，确保和实际在职员工一致。
• 如果表单字段有调整，需同步修改代码中的列索引和输出字段。

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