如何用Apps Script发送邮件并标记已发送行（适配动态数据）

• 本人是Apps Script新手，当前实现方案尚不成熟，还请见谅。
• 数据来源：通过BigQuery连接获取，更新频率最高每小时一次。
• 已实现功能：设置每日触发器，当满足以下任一条件时发送邮件：
  1. FSI状态为Confirm
  2. FSI状态为Alert
• 现有代码中mailsent相关逻辑是为标记已发送邮件做的准备，现在需要实现：发送邮件后标记对应行，且该标记能随BigQuery连接刷新后的动态数据同步更新。

function main() {
 
  var sheet = SpreadsheetApp.getActive().getSheetByName("FSI PO Summary");
  var numRows = sheet.getLastRow();
  var range = sheet.getRange(2, 1, numRows - 1, 10).getValues();

  for(var index in range) {

    
    var row = range[index];
    var vendor = row[3];
    var status = row[6];
    var po = row[1];
    var fsi = row[5];
    var mailsent = row[9];


if(fsistatuscheckapp(status, mailsent)) {
//If yes, send an email reminder
emailReminder(po);
}



if(fsistatuscheckalert(status, mailsent)) {
//If yes, send an email reminder
emailAlert(po);
}

}
}


var url = "https://url.com";
var tem = HtmlService.createTemplateFromFile("Email Template");
tem.url = url;




function fsistatuscheckapp(status, mailsent) {

  if(status === 'Confirm' && mailsent === '') {
    return true;
  } else {
    return false;
  }
}


function fsistatuscheckalert(status, mailsent) {

  if(status === 'Alert' && mailsent === '') {
    return true;
  } else {
    return false;
  }
}


function emailReminder(po) {
    var email_body = tem.evaluate().getContent(); 

  MailApp.sendEmail( 
    {to: "email@email.com",
      subject: "FSI Approval Needed:" + po,
      htmlBody: email_body,
    });
  
  }


function emailAlert(po) {
    var email_body = tem.evaluate().getContent(); 

  MailApp.sendEmail( 
    {to: "email@email.com",
      subject: "FSI Schedule Date Needed:" + po,
      htmlBody: email_body,
    });
    
  }

由于BigQuery连接的表格是动态刷新的，直接在原表添加标记会被刷新覆盖，因此需要单独维护一张邮件发送记录表追踪已发送的订单，再通过公式或脚本关联原表实现标记同步。

步骤1：创建邮件发送记录表 #

在当前Spreadsheet中新建一张名为Sent Emails的工作表，表头设置为：

步骤2：修改邮件发送函数，添加记录 #

更新emailReminder和emailAlert函数，发送邮件后将订单信息写入记录表：

function emailReminder(po, poRev) {
    var email_body = tem.evaluate().getContent(); 

  MailApp.sendEmail( 
    {to: "email@email.com",
      subject: "FSI Approval Needed:" + po,
      htmlBody: email_body,
    });
  
  // 写入发送记录
  var sentSheet = SpreadsheetApp.getActive().getSheetByName("Sent Emails");
  sentSheet.appendRow([po, poRev, 'Confirm', new Date()]);
}


function emailAlert(po, poRev) {
    var email_body = tem.evaluate().getContent(); 

  MailApp.sendEmail( 
    {to: "email@email.com",
      subject: "FSI Schedule Date Needed:" + po,
      htmlBody: email_body,
    });
    
  // 写入发送记录
  var sentSheet = SpreadsheetApp.getActive().getSheetByName("Sent Emails");
  sentSheet.appendRow([po, poRev, 'Alert', new Date()]);
}

步骤3：修改主函数，检查发送记录 #

原代码依赖原表mailsent列的方式不可行，改为查询记录表判断是否已发送邮件：

function main() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("FSI PO Summary");
  var numRows = sheet.getLastRow();
  var range = sheet.getRange(2, 1, numRows - 1, 9).getValues();
  var sentSheet = SpreadsheetApp.getActive().getSheetByName("Sent Emails");
  var sentData = sentSheet.getDataRange().getValues();
  
  // 构建已发送记录映射：PO+PO_REV -> 已发送状态集合
  var sentMap = {};
  for (var i = 1; i < sentData.length; i++) {
    var key = sentData[i][0] + (sentData[i][1] || '');
    var status = sentData[i][2];
    if (!sentMap[key]) {
      sentMap[key] = new Set();
    }
    sentMap[key].add(status);
  }

  for(var index in range) {
    var row = range[index];
    var status = row[6];
    var po = row[1];
    var poRev = row[2] || '';
    var key = po + poRev;

    // 检查是否已发送对应状态的邮件
    var hasSent = sentMap[key] && sentMap[key].has(status);
    if(!hasSent && status === 'Confirm') {
      emailReminder(po, poRev);
    }
    if(!hasSent && status === 'Alert') {
      emailAlert(po, poRev);
    }
  }
}

步骤4：在原表添加动态标记列 #

在原表第10列（mailsent列）的单元格J2输入以下公式，下拉填充实现动态标记：

=IF(COUNTIFS('Sent Emails'!A:A, B2, 'Sent Emails'!B:B, C2, 'Sent Emails'!C:C, G2) > 0, "已发送", "")

BigQuery刷新数据后，该公式会自动同步查询记录表，标记已发送的行。

补充说明 #

• 用PO+PO_REV作为唯一标识，避免同PO不同版本的订单被误判
• 记录表永久保存发送记录，若需重新发送特定订单，可手动删除对应记录
• 若触发器执行频率较高，可在主函数开头添加锁机制，避免并发执行导致重复发送

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