Google Apps Script如何在Google表单提交时判断注册码列是否达末行触发对应邮件函数

逻辑说明 #

• 提前在Sheet1的C列（注册码列）预填全部可用注册码，代码会自动识别预填注册码的最大有效行数
• 绑定表单提交触发器，用户提交谷歌表单后自动触发代码执行
• 当表单提交新增的行号超过预填注册码的最大行数时，判定注册码已耗尽，向提交用户发送注册关闭通知；否则正常发送注册成功邮件

触发器配置 #

1. 打开对应Google表格，点击顶部菜单栏「扩展程序」→「Apps Script」进入脚本编辑器
2. 点击左侧闹钟形状的「触发器」按钮，点击右下角「添加触发器」，按如下参数配置：
   • 选择要运行的函数：sendmail
   • 选择部署版本：Head（默认值即可）
   • 事件源选择：「电子表格」
   • 事件类型选择：「表单提交」
3. 保存后按照页面提示完成Google账号授权即可

调整后代码 #

function sendmail() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("Sheet1");
  var EMAIL_SENT = "EMAIL_SENT";
  
  // 获取当前表格最后一行（即刚提交的表单数据行）
  var lastRow = sh1.getLastRow();
  // 读取当前提交的用户信息
  var email = sh1.getRange(lastRow, 2).getValue();
  var name = sh1.getRange(lastRow, 1).getValue();

  // 统计C列（注册码列）预填的非空值最大行号，排除空行
  var regCodeValues = sh1.getRange("C:C").getValues();
  var maxRegCodeRow = 0;
  for (var i = regCodeValues.length - 1; i >= 0; i--) {
    if (regCodeValues[i][0] !== "") {
      maxRegCodeRow = i + 1; // 数组下标从0开始，表格行号从1开始，需+1转换
      break;
    }
  }

  // 判断注册码是否耗尽
  if (lastRow > maxRegCodeRow) {
    // 注册关闭通知逻辑
    var closeSubject = "注册通知";
    var closeBody = "Dear " + name + ",<br>registration had been closed, thank you.";
    MailApp.sendEmail({
      to: email,
      subject: closeSubject,
      htmlBody: closeBody
    });
  } else {
    // 原有注册成功通知逻辑
    var registercode = sh1.getRange(lastRow, 3).getValue();
    var subjecttxt = "Thank you for registered with us";
    var email_Body = "Dear " + name + ",<br>" +
                    "your registration is successful, your registered code is " + registercode + 
                    "<br>Thank you for your registration";
    MailApp.sendEmail({
      to: email,
      subject: subjecttxt,
      htmlBody: email_Body,
    });           
  }
  // 标记邮件已发送
  sh1.getRange(lastRow, 4).setValue(EMAIL_SENT);
}

注意事项 #

• 上述代码默认Sheet1列规则为：A列存储姓名、B列存储邮箱、C列预填注册码、D列存储邮件发送标记，若你的表格列序不同，可自行调整getRange对应的列号参数
• 若你设置了表头行，可自行调整regCodeValues的遍历起始下标，排除表头行的干扰

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