如何在Google Apps Script中实现点击后禁用邮件内的审批链接

我有一段用于发送请假审批邮件的Google Apps Script代码，邮件里的Approve/Reject链接点击后会给申请人发状态邮件，但现在重复点击链接会重复发邮件，而且链接不会失效。需要实现：点击链接后不再重复发送邮件，同时禁用该链接。现有代码如下：

var options = '?approval=%APPROVE%&timestamp=%TIMESTAMP%&reply=%EMAIL%&last=%Last%&clicked=%CLICKED%'
         .replace("%TIMESTAMP%",encodeURIComponent(e.namedValues["Timestamp"]))
         .replace("%EMAIL%",e.namedValues["Email Address"])
         .replace("%Last%",last)
         .replace("%CLICKED%",clicked)
  var approve = url+options.replace("%APPROVE%","Approved");
  var reject = url+options.replace("%APPROVE%","Rejected");

  var html = "<body>" +
    "Name : " + Name + "<br/>" +
    "Email id : " + email + "<br/>" +
    "Leave Type : " + leavetype + "<br/>" +
    "Reason : " + Reason + "<br/>" +
    "Start Date : " + start_date + "<br/>" +
    "End Date : " + end_date + "<br/>" +
    "<a href=" + approve + ">Approve</a><br />" +
    "<a href=" + reject + ">Reject</a><br />" +
    "</body>";

  MailApp.sendEmail("email",
                    "Leave Approval Request",
                    "Requires html",
                    {htmlBody: html});

function doGet(e){
  var answer = (e.parameter.approval === 'Approved') ? 'Hi. Your Leave Request is APPROVED,    Please update the same in Calender. ' : 'Hi. Your Leave Request is REJECTED';
  
  var timestamp = e.parameter.timestamp;
  var last = e.parameter.last;
  var clicked = e.parameter.clicked;

  MailApp.sendEmail(e.parameter.reply, "Leave Request Status",
                      answer );
}

核心思路 #

要实现需求，关键是持久化记录每个请假请求的处理状态——Google Apps Script本身无内置存储，用Google Sheet保存请求状态（未处理/已批准/已驳回），每次点击链接先检查状态，再决定是否执行操作。

步骤1：准备状态存储表格 #

新建Google Sheet并命名为「请假审批状态」，第一行表头设为：Timestamp、Status（分别对应请求时间戳、处理状态）。

步骤2：修改发送邮件代码 #

去掉无用的clicked参数，用唯一的Timestamp作为请求标识：

// 替换为你的Web App部署地址
var url = "https://script.google.com/macros/s/你的部署ID/exec";

// 构造请求参数，移除clicked参数
var options = '?approval=%APPROVE%&timestamp=%TIMESTAMP%&reply=%EMAIL%&last=%Last%'
         .replace("%TIMESTAMP%", encodeURIComponent(e.namedValues["Timestamp"]))
         .replace("%EMAIL%", e.namedValues["Email Address"])
         .replace("%Last%", last);

var approve = url + options.replace("%APPROVE%", "Approved");
var reject = url + options.replace("%APPROVE%", "Rejected");

// 修正链接的引号语法，避免HTML解析错误
var html = "<body>" +
  "Name : " + Name + "<br/>" +
  "Email id : " + email + "<br/>" +
  "Leave Type : " + leavetype + "<br/>" +
  "Reason : " + Reason + "<br/>" +
  "Start Date : " + start_date + "<br/>" +
  "End Date : " + end_date + "<br/>" +
  "<a href='" + approve + "'>Approve</a><br />" +
  "<a href='" + reject + "'>Reject</a><br />" +
  "</body>";

MailApp.sendEmail("审批人邮箱",
                  "Leave Approval Request",
                  "请使用支持HTML的邮箱查看",
                  {htmlBody: html});

步骤3：修改doGet函数，添加状态检查逻辑 #

点击链接时先查询状态，已处理则返回禁用提示，未处理则发送邮件并更新状态：

function doGet(e) {
  // 获取请求参数
  var approvalStatus = e.parameter.approval;
  var timestamp = decodeURIComponent(e.parameter.timestamp);
  var replyEmail = e.parameter.reply;
  
  // 连接状态存储表格（替换为你的Sheet ID）
  var sheet = SpreadsheetApp.openById("你的Google Sheet ID").getSheetByName("Sheet1");
  var data = sheet.getDataRange().getValues();
  
  // 检查该请求是否已处理
  var isProcessed = false;
  var currentStatus = "";
  for (var i = 1; i < data.length; i++) {
    if (data[i][0] === timestamp) {
      isProcessed = true;
      currentStatus = data[i][1];
      break;
    }
  }
  
  // 已处理则返回禁用提示页面
  if (isProcessed) {
    return HtmlService.createHtmlOutput(`
      <body>
        <p>该请假请求已${currentStatus.toLowerCase()}，操作已失效</p>
        <p>原审批链接已禁用</p>
      </body>
    `);
  }
  
  // 未处理则发送状态邮件
  var answer = approvalStatus === 'Approved' 
    ? 'Hi. Your Leave Request is APPROVED, Please update the same in Calender.' 
    : 'Hi. Your Leave Request is REJECTED';
  
  MailApp.sendEmail(replyEmail, "Leave Request Status", answer);
  
  // 在表格中记录处理状态
  sheet.appendRow([timestamp, approvalStatus]);
  
  // 返回操作成功页面，提示链接已禁用
  return HtmlService.createHtmlOutput(`
    <body>
      <p>操作成功！已${approvalStatus.toLowerCase()}该请假请求</p>
      <p>原审批链接已禁用，重复点击无效</p>
    </body>
  `);
}

注意事项 #

• 部署Web App时，权限选择「任何人，甚至匿名」（邮箱客户端点击链接为匿名请求）。
• 确保Google Sheet权限允许脚本访问（默认权限即可）。
• 依赖表单提交的Timestamp作为唯一标识，Google Forms默认的Timestamp具备唯一性。

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