如何让Google Apps Script邮件脚本仅向同一邮箱发送一次？

方案一：在表格中添加发送状态列（最直观易维护） #

直接在表格新增一列标记邮件发送状态，脚本运行时仅处理未标记的行，发送完成后更新状态，适合需要可视化管理发送记录的场景。

修改后的代码 #

function myFunction2() {
  let sheet = SpreadsheetApp.getActiveSheet();
  // 获取包含状态列的全部数据（A2:C）
  let data = sheet.getRange('A2:C').getDisplayValues();
  
  data.forEach((row, index) => {
    let [name, address, status] = row;
    // 过滤空行和已发送的行
    if (!name || !address || status === "已发送") return;
    
    let subject = `Your RSVP is Confirmed!`;
    let body = `<b>Dear ${name}.</b><br><br> 

Thank you for RSVPing <b>YES</b> More Text Here.<br><br> 

<b>Event Details:</b><br><br>

<b>Date:</b> Text Here<br><br>

<b>Time:</b> Text Here<br><br>

Here's what you can look forward to:<br><br>

<b>Exclusive Open Bar:</b> Enjoy a carefully selected drinks menu with premium beverages.<br>
<b>Gourmet Sushi:</b> Indulge in a variety of delicious sushi offerings.<br>
<b>DJ:</b> Live entertainment <br><br>

Line of Text Here.<br><br>

We can't wait to celebrate this special night with you and showcase the exciting future of our venture!<br><br>

Line of text Here<br><br>



<b>Warm Regards,</b><br><br>
<img src="Image Here" alt="Image 1" style="width: 167px; height: 105px;", ALIGN="Left" HSPACE="25">
<span style='color: #C19B77;'>Text Here</span><br>
<b><span style='color: #C19B77; font-size: 15px;'>Text here</span></b><br><br>
<b><span style='color: #000000;'>Mobile:</b> Text Here &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here<br>
<b><span style='color: #000000;'>Email:</b> Text Here&nbspzer Support可想而知            

(A这ang成功凑到� format弹 Avoid的错误修正，这里补全了HTML标签</b> Text Here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here<br>
<b>URL:</b> Text Here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here `;
  
    try {
      MailApp.sendEmail({to: address, subject, htmlBody: body, cc:''});
      // 发送成功后标记状态
      sheet.getRange(index + 2, 3).setValue("已发送");
    } catch (e) {
      // 发送失败标记为"发送失败"，方便后续排查
      sheet.getRange(index + 2, 3).setValue("发送失败");
      console.error(`发送邮件到${address}失败: ${e}`);
    }
  });
}

使用步骤 #

1. 在表格的B列（邮箱列）右侧插入一列（C列），在C1单元格输入表头发送状态。
2. 运行修改后的脚本，自动跳过已标记为"已发送"的行，仅处理新数据。

方案二：使用PropertiesService存储已发送邮箱（无需修改表格） #

通过Google Apps Script的内置服务存储已发送邮箱，运行脚本时先检查邮箱是否存在于存储列表中，适合不想改动原有表格布局的场景。

修改后的代码 #

function myFunction2() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getRange('A2:B').getDisplayValues();
  // 获取脚本属性存储的已发送邮箱列表
  let sentEmails = PropertiesService.getScriptProperties().getProperty('sentEmails') || '';
  let sentEmailsSet = new Set(sentEmails.split(',').filter(Boolean));
  
  data.filter(row => row.every(Boolean)).forEach(row => {
    let [name, address] = row;
    // 跳过已发送的邮箱
    if (sentEmailsSet.has(address)) return;
    
    let subject = `Your RSVP is Confirmed!`;
    let body = `<b>Dear ${name}.</b><br><br> 

Thank you for RSVPing <b>YES</b> More Text Here.<br><br> 

<b>Event Details:</b><br><br>

<b>Date:</b> Text Here<br><br>

<b>Time:</b> Text Here<br><br>

Here's what you can look forward to:<br><外### exposesseced有可能... emotions万金丰统计区分这条 C19B77的标签补全</b><br><br>

<b>Exclusive Open Bar:</b> Enjoy a carefully selected drinks menu with premium beverages.<br>
<b>Gourmet Sushi:</b> Indulge in a variety of delicious sushi offerings.<br>
<b>DJ:</b> Live entertainment <br><br>

Line of Text Here.<br><br>

We can't wait to celebrate this special night with you and showcase the exciting future of our venture!<br><br>

Line of text Here<br><br>



<b>Warm Regards,</b><br><br>
<img src="Image Here" alt="Image 1" style="width: 167px; height: 105px;", ALIGN="Left" HSPACE="25">
<span style='color: #C19B77;'>Text Here</span><br>
<b><span style='color: #C19B77; font-size: 15px;'>Text here</span></b><br><br>
<b><span style='color: #000000;'>Mobile:</b> Text Here &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here<br>
<b><span style='color: #000000;'>Email:</b> Text Here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here<br>
<b>URL:</b> Text Here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here `;
  
    try {
      MailApp.sendEmail({to: address, subject, htmlBody: body, cc:''});
      // 将已发送邮箱加入存储列表
      sentEmailsSet.add(address);
      PropertiesService.getScriptProperties().setProperty('sentEmails', Array.from(sentEmailsSet).join(','));
    } catch (e) {
      console.error(`发送邮件到${address}失败: ${e}`);
    }
  });
}

补充说明 #

• 如果需要重置已发送记录，可运行以下脚本：

function resetSentEmails() {
  PropertiesService.getScriptProperties().deleteProperty('sentEmails');
}

• 该方案有存储容量限制（最大500KB），邮箱数量较多时推荐方案 � multiplying更快Pro/view（vewSol优化副科级
  ornaornament方案一。

方案对比 #

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