Google Sheets计分板自动化：基于分数触发定向自动邮件的技术问询

一、核心思路：用「完成标记」精准触发 #

避免任意单元格编辑触发邮件，先给表格添加**「测试完成」复选框列**（比如最后一列），仅当考生勾选该复选框时，才触发邮件逻辑。

二、无代码/低代码实现方案（适合不会编码的用户） #

1. 用 Google Sheets 内置功能 + 扩展 #

• 添加「测试完成」复选框：选中目标列（如H列），右键→「数据验证」→ 选择「复选框」后保存。
• 安装「Email Notifications for Google Sheets」扩展：打开表格→扩展→添加-ons→搜索该扩展并完成安装。
• 配置规则：
  • 触发条件：当「测试完成」列被勾选为TRUE时
  • 目标范围：选中分数所在单元格区域（如G列）
  • 设置分档逻辑：
    • 规则1：分数<30% → 发送警告邮件给教师+考生，自定义主题和内容
    • 规则2：30%≤分数<50% → 发送提醒邮件给教师+考生
    • 规则3：分数≥60% → 发送祝贺邮件给考生并抄送给教师
• 权限确认：确保扩展获取邮件发送权限，该扩展默认支持所有表格使用者触发规则。

2. 用 AppSheet 搭建自动化 #

• 打开表格→扩展→AppSheet→创建应用
• 添加自动化规则：
  • 触发事件：当「测试完成」列的值变为TRUE时
  • 操作：发送邮件，根据分数列的值设置条件分支，匹配对应邮件内容和收件人

三、Google Apps Script 代码实现（自定义程度更高） #

1. 先梳理表格结构 #

• 新增「测试完成」复选框列（如列H），考生完成测试后勾选
• 确保表格包含：考生姓名列（如A列）、考生邮箱列（如B列）、教师邮箱列（如C列）、分数列（如G列）

2. 编写脚本 #

打开表格→工具→脚本编辑器，替换默认代码为以下内容：

function sendScoreNotification(e) {
  // 仅当「测试完成」复选框被勾选时触发逻辑
  const editedRange = e.range;
  const sheet = editedRange.getSheet();
  
  // 可根据你的表格结构修改以下列号
  const completionColumn = 8;    // 「测试完成」列（H列）
  const scoreColumn = 7;         // 分数列（G列）
  const studentEmailColumn = 2;  // 考生邮箱列（B列）
  const teacherEmailColumn = 3;  // 教师邮箱列（C列）

  // 判断是否为目标列且勾选状态为TRUE
  if (editedRange.getColumn() !== completionColumn || editedRange.getValue() !== true) {
    return;
  }

  // 获取当前行的核心数据
  const row = editedRange.getRow();
  const score = sheet.getRange(row, scoreColumn).getValue();
  const studentEmail = sheet.getRange(row, studentEmailColumn).getValue();
  const teacherEmail = sheet.getRange(row, teacherEmailColumn).getValue();
  const studentName = sheet.getRange(row, 1).getValue(); // A列为考生姓名

  // 根据分数段配置邮件内容
  let subject, body;
  if (score < 0.3) {
    subject = `⚠️ 警告：${studentName}测试成绩低于30%`;
    body = `
    您好，${studentName}的测试成绩为${score*100}%，未达到基础要求，请关注后续辅导。
    
    考生邮箱：${studentEmail}
    `;
  } else if (score < 0.5) {
    subject = `📢 提醒：${studentName}测试成绩低于50%`;
    body = `
    您好，${studentName}的测试成绩为${score*100}%，未通过测试，请安排补考或辅导。
    
    考生邮箱：${studentEmail}
    `;
  } else if (score >= 0.6) {
    subject = `🎉 祝贺：${studentName}测试成绩达标`;
    body = `
    您好，${studentName}的测试成绩为${score*100}%，顺利通过测试！
    
    教师邮箱：${teacherEmail}
    `;
  } else {
    // 50%-60%区间可自定义内容
    subject = `ℹ️ ${studentName}测试成绩通知`;
    body = `
    您好，${studentName}的测试成绩为${score*100}%，请继续努力。
    
    教师邮箱：${teacherEmail}
    `;
  }

  // 发送邮件给考生和教师
  const recipients = `${studentEmail}, ${teacherEmail}`;
  GmailApp.sendEmail(recipients, subject, body);
}

3. 设置可安装触发器（支持所有使用者触发） #

• 在脚本编辑器左侧点击「触发器」图标→添加触发器
• 配置参数：
  • 选择函数：sendScoreNotification
  • 事件源：「从电子表格」
  • 事件类型：「编辑时」
  • 运行身份：选择「触发事件的用户」（确保任何编辑表格的用户勾选复选框时，能用自身权限发送邮件）
  • 失败通知：选择「立即通知我」

4. 权限配置 #

• 第一次运行脚本时，按照提示完成授权（需允许脚本访问Gmail和电子表格）
• 确保所有表格使用者拥有编辑权限，可安装触发器默认支持多用户触发

四、注意事项 #

• 分数列需设置为数值格式（如0.5代表50%），避免脚本无法识别
• 使用脚本发送邮件时，用户需拥有Gmail账号并授权脚本访问权限
• 测试时手动勾选「测试完成」复选框，确认邮件是否正常发送

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