如何实现Google Sheets基于单元格值自动发邮件并兼容现有下拉脚本

需求说明 #

需要在Google Sheets中实现功能：当D列值为Today时，自动向A列对应行的邮箱地址发送邮件，邮件主题取自B列内容，正文取自C列内容。

现有参考脚本 #

原有相近功能脚本仅支持向静态邮箱发送邮件，内容如下：

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");  // 仅处理指定触发工作表
  var startRow = 2;  // 首个待处理数据行
  var numRows = 2;   // 待处理数据行数
  // 获取A2:B3单元格范围
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // 获取范围内所有行的数值
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    if (row[2] === "Today") {       // 仅当C列值为"Yes"时触发
      var emailAddress = row[0];  // 第一列
      var message = row[1];       // 第二列
      var subject = "Bday ==" + row[2]; // 拼接主题，按触发逻辑此处值固定为Yes
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

兼容性要求 #

调整脚本适配上述需求的同时，需要和正在使用的动态依赖下拉列表脚本兼容，下拉列表脚本核心代码如下：

function onEdit(event) 
{
  var maxRows = false;
    
  // 修改设置:
  //--------------------------------------------------------------------------------------
  var TargetSheet = 'Main'; // 带数据验证的工作表名称
  var LogSheet = 'Data1'; // 存储数据的工作表名称
  var NumOfLevels = 4; // 数据验证的层级数
  var lcol = 2; // 验证起始列号，A=1、B=2以此类推
  var lrow = 2; // 验证起始行号
  var offsets = [1,1,1,2]; // 各层级偏移量
  //                   ^ 表示第4列向右偏移1位
  // var maxRows = 500; // 设置验证的最后一行，不需要可删除此行

  
  // =====================================================================================
    
  SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows);
  
  // 修改设置:
  //--------------------------------------------------------------------------------------
  var TargetSheet = 'Main'; // 带数据验证的工作表名称
  var LogSheet = 'Data2'; // 存储数据的工作表名称
  var NumOfLevels = 7; // 数据验证的层级数
  var lcol = 9; // 验证起始列号，A=1、B=2以此类推
  var lrow = 2; // 验证起始行号
  var offsets = [1,1,1,1,1,1,1]; // 各层级偏移量
  // var maxRows = 500; // 设置验证的最后一行，不需要可删除此行
  // =====================================================================================  
  SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows);
}

function SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows) 
// 其余代码省略

适配后脚本 #

以下脚本完全匹配需求，且和下拉列表脚本完全兼容，可直接添加到同一项目中使用：

function sendEmails() {
  // 替换为你实际存放邮件数据的工作表名称
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");
  // 从第2行开始读取，跳过表头
  const startRow = 2;
  // 自动获取有数据的总行数，无需手动修改
  const numRows = sheet.getLastRow() - 1;
  // 读取A到D列全部数据
  const dataRange = sheet.getRange(startRow, 1, numRows, 4);
  const data = dataRange.getValues();
  // 已发送标记列，默认E列，可自行调整列号
  const sentFlagCol = 5;

  for (let i = 0; i < data.length; i++) {
    const row = data[i];
    const mail = row[0]; // A列：邮箱
    const subject = row[1]; // B列：主题
    const content = row[2]; // C列：正文
    const trigger = row[3]; // D列：触发标记

    // 仅当D列为Today且未发送过邮件时触发
    if (trigger === "Today" && sheet.getRange(startRow + i, sentFlagCol).getValue() !== "已发送") {
      MailApp.sendEmail(mail, subject, content);
      // 标记已发送，避免重复发信
      sheet.getRange(startRow + i, sentFlagCol).setValue("已发送");
    }
  }
}

使用说明 #

• 两个脚本相互独立无冲突，下拉列表使用onEdit简单触发器，邮件脚本单独运行，不会互相干扰
• 需手动为sendEmails函数添加可安装触发器：打开Apps Script编辑器，点击左侧「触发器」→「添加触发器」，选择sendEmails函数：
  • 若需要每天定时检查发送，触发类型选「时间驱动」，频率设置为每日1次即可
  • 若需要修改D列后即时触发，触发类型选「来自电子表格」，事件类型选「编辑时」
• 不需要重复发信校验的话，可直接删除代码中sentFlagCol相关的判断和标记逻辑

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