如何在Google Sheets中基于Google Form两次连续缺勤记录触发自动邮件？

前置准备 #

• 确认考勤响应表结构：第1行存储学生姓名，每新增一行对应一次课程的考勤提交，每行对应列存储对应学生当次考勤状态
• 提前新建用于汇总连续缺勤学生的目标表格，复制该表格URL中d/和/edit之间的字符串作为表格ID备用

实现脚本 #

打开考勤表格的Apps脚本编辑器，粘贴以下代码，修改开头的自定义配置项即可使用：

// -------------- 自定义配置项 请修改以下内容 --------------
const NOTIFY_EMAIL = "your-admin-email@example.com"; // 接收告警的行政人员邮箱
const SUMMARY_SHEET_ID = "your-summary-sheet-id"; // 提前新建的缺勤汇总表ID
const ATTENDANCE_SHEET_NAME = "表单响应 1"; // 考勤响应表的工作表名称
const ABSENCE_KEYWORD = "缺勤"; // 考勤选项中缺勤对应的文本
const ATTENDANCE_KEYWORD = "出勤"; // 考勤选项中出勤对应的文本
// ------------------------------------------------------

function checkContinuousAbsence() {
  // 读取考勤表全量数据
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const attendanceSheet = ss.getSheetByName(ATTENDANCE_SHEET_NAME);
  const allData = attendanceSheet.getDataRange().getValues();
  const studentNames = allData[0];
  const totalRows = allData.length;
  const totalCols = studentNames.length;

  // 读取缺勤汇总表数据
  const summarySs = SpreadsheetApp.openById(SUMMARY_SHEET_ID);
  const summarySheet = summarySs.getSheets()[0];
  const existingAbsentees = summarySheet.getDataRange().getValues().flat();

  // 逐个检查学生连续缺勤状态
  for (let col = 0; col < totalCols; col++) {
    const studentName = studentNames[col];
    let continuousAbsenceCount = 0;
    let hasAttended = false;

    // 从最新提交的考勤记录倒序遍历
    for (let row = totalRows - 1; row >= 1; row--) {
      const currentStatus = allData[row][col];
      if (currentStatus === ABSENCE_KEYWORD) {
        continuousAbsenceCount++;
      } else if (currentStatus === ATTENDANCE_KEYWORD) {
        hasAttended = true;
        break;
      }
    }

    if (continuousAbsenceCount >= 2 && !hasAttended) {
      // 未在汇总表中则新增记录+触发邮件告警
      if (!existingAbsentees.includes(studentName)) {
        summarySheet.appendRow([studentName, new Date(), continuousAbsenceCount]);
        MailApp.sendEmail({
          to: NOTIFY_EMAIL,
          subject: `考勤告警：${studentName}已连续缺勤${continuousAbsenceCount}次`,
          body: `学生${studentName}累计连续缺勤${continuousAbsenceCount}次，请按规定跟进处理。`
        });
      }
    } else {
      // 学生已出勤，从汇总表中移除对应记录
      const targetRow = existingAbsentees.indexOf(studentName);
      if (targetRow > -1) {
        summarySheet.deleteRow(targetRow + 1);
      }
    }
  }
}

触发器配置步骤 #

1. 保存脚本后，点击编辑器左侧菜单栏「触发器」选项，选择「添加触发器」
2. 按以下参数配置触发器：
   • 选择要运行的函数：checkContinuousAbsence
   • 选择事件来源：电子表格
   • 选择事件类型：表单提交时
3. 保存触发器，按照页面提示完成脚本权限授权即可

功能说明 #

• 每次教师提交考勤表单后，脚本自动运行检查所有学生的连续缺勤状态
• 学生连续缺勤达2次时，仅触发1次邮件告警，同时自动同步到缺勤汇总表
• 学生后续提交出勤状态后，自动重置连续缺勤计数，同时从汇总表中删除该学生的记录
• 连续缺勤超过2次的情况不会重复发送告警邮件

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