基于Google Forms的Google Sheets考勤表自动更新方案问询

核心逻辑对应你的伪代码 #

你的需求本质是：当员工提交考勤表单后，在「每日考勤表」的对应员工行、对应日期列标记「P」；未提交的员工在当日列标记「A」，对应你给出的伪代码逻辑：

IF sheet1(员工).value 存在于 sheet2(员工列).value 且 提交日期匹配 sheet2(日期列).value THEN 标记 "P" ELSE 标记 "A"

步骤1：明确两个工作表的结构 #

假设你的Google Sheets里有两个工作表：

• 表单响应：Google Forms自动生成的提交记录表，包含至少两列：员工姓名（或员工ID）、提交时间（表单自带的时间戳列）
• 每日考勤：你的目标考勤表，行是员工姓名，第一行是日期（格式如「2024/05/20」），单元格用于填写「P」/「A」

步骤2：编写Google Apps Script代码 #

打开你的Google Sheets，点击「扩展程序」→「Apps脚本」，替换默认代码为以下内容：

// 表单提交时自动触发的函数
function onFormSubmit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const responseSheet = ss.getSheetByName("表单响应"); // 替换为你的表单响应表名
  const attendanceSheet = ss.getSheetByName("每日考勤"); // 替换为你的每日考勤表名

  // 获取提交的员工和日期（提取日期部分，忽略时间）
  const submitEmployee = e.values[1]; // 假设员工姓名在表单响应的第2列（索引从0开始），根据实际列调整
  const submitDate = new Date(e.values[0]).toLocaleDateString("zh-CN"); // 统一日期格式为「YYYY/MM/DD」

  // 在考勤表中找到对应日期的列号
  const dateRow = attendanceSheet.getRange(1, 1, 1, attendanceSheet.getLastColumn()).getValues()[0];
  let dateCol = dateRow.indexOf(submitDate) + 1; // 转换为Google Sheets列号（从1开始）

  // 如果日期列不存在，自动添加新列
  if (dateCol === 0) {
    dateCol = attendanceSheet.getLastColumn() + 1;
    attendanceSheet.getRange(1, dateCol).setValue(submitDate);
  }

  // 在考勤表中找到对应员工的行号
  const employeeCol = attendanceSheet.getRange(2, 1, attendanceSheet.getLastRow() - 1, 1).getValues();
  let employeeRow = employeeCol.findIndex(row => row[0] === submitEmployee) + 2; // 员工从第2行开始

  // 如果员工不存在，自动添加新行（可选，按需调整）
  if (employeeRow === 1) {
    employeeRow = attendanceSheet.getLastRow() + 1;
    attendanceSheet.getRange(employeeRow, 1).setValue(submitEmployee);
  }

  // 标记该员工当日为「P」
  attendanceSheet.getRange(employeeRow, dateCol).setValue("P");

  // 批量标记当日未提交的员工为「A」（可选，按需开启）
  markAbsentForDay(attendanceSheet, submitDate, dateCol);
}

// 辅助函数：标记当日未提交的员工为「A」
function markAbsentForDay(sheet, targetDate, dateCol) {
  const lastRow = sheet.getLastRow();
  const employeeRange = sheet.getRange(2, 1, lastRow - 1, 1);
  const attendanceRange = sheet.getRange(2, dateCol, lastRow - 1, 1);
  
  const employees = employeeRange.getValues();
  const currentAttendance = attendanceRange.getValues();

  for (let i = 0; i < employees.length; i++) {
    // 空单元格标记为「A」
    if (currentAttendance[i][0] === "") {
      sheet.getRange(i + 2, dateCol).setValue("A");
    }
  }
}

// 批量同步历史提交记录到考勤表（首次使用时运行）
function syncHistoryResponses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const responseSheet = ss.getSheetByName("表单响应");
  const attendanceSheet = ss.getSheetByName("每日考勤");

  const responses = responseSheet.getRange(2, 1, responseSheet.getLastRow() - 1, responseSheet.getLastColumn()).getValues();
  
  responses.forEach(row => {
    const submitEmployee = row[1];
    const submitDate = new Date(row[0]).toLocaleDateString("zh-CN");
    
    // 重复日期列、员工行查找逻辑
    const dateRow = attendanceSheet.getRange(1, 1, 1, attendanceSheet.getLastColumn()).getValues()[0];
    let dateCol = dateRow.indexOf(submitDate) + 1;
    if (dateCol === 0) {
      dateCol = attendanceSheet.getLastColumn() + 1;
      attendanceSheet.getRange(1, dateCol).setValue(submitDate);
    }

    const employeeCol = attendanceSheet.getRange(2, 1, attendanceSheet.getLastRow() - 1, 1).getValues();
    let employeeRow = employeeCol.findIndex(r => r[0] === submitEmployee) + 2;
    if (employeeRow === 1) {
      employeeRow = attendanceSheet.getLastRow() + 1;
      attendanceSheet.getRange(employeeRow, 1).setValue(submitEmployee);
    }

    attendanceSheet.getRange(employeeRow, dateCol).setValue("P");
  });

  // 标记所有日期未提交的员工为「A」
  const dateRow = attendanceSheet.getRange(1, 1, 1, attendanceSheet.getLastColumn()).getValues()[0];
  dateRow.forEach(date => {
    const col = dateRow.indexOf(date) + 1;
    markAbsentForDay(attendanceSheet, date, col);
  });
}

步骤3：设置表单提交触发器 #

1. 在Apps脚本编辑器中，点击左侧「触发器」图标（闹钟形状）
2. 点击「添加触发器」，设置参数：
   • 运行函数：onFormSubmit
   • 事件源：「表单提交」
   • 事件类型：「提交时」
3. 保存并授权脚本权限（首次运行需按步骤完成授权）

关键注意事项 #

• 调整列索引：比如submitEmployee = e.values[1]，若员工姓名在表单响应第3列，改为e.values[2]（索引从0开始）
• 日期格式统一：确保「每日考勤」表第一行日期格式与脚本生成的「YYYY/MM/DD」一致
• 员工姓名匹配：表单填写的姓名需与考勤表中姓名完全一致（大小写、空格均需匹配）

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