Gmail/Google Sheets邮件合并应用仅返回首行数据求助

问题根源 #

你的代码中，getEmailTemplate函数每次调用都会从Gmail搜索主题为Teste de Implantação的线程，并取该线程的最后一条消息作为模板。当第一封邮件发送后，该线程会新增一条已经替换过占位符的发送邮件，后续调用getEmailTemplate时，获取的不再是原始模板，而是这条已修改的邮件内容——此时占位符已经被首行数据替换，无法再进行新的替换，导致所有后续邮件都沿用首行数据的内容。

此外，String.replace()方法默认只替换第一个匹配的占位符，如果模板中有多个相同占位符，也会出现替换不彻底的问题。

修复方案 #

1. 缓存原始模板：仅在首次调用时从Gmail读取原始模板，之后复用该模板进行占位符替换，避免每次读取已修改的发送邮件。
2. 全局替换占位符：使用正则表达式的全局匹配（/{{nome}}/g）确保模板中所有相同占位符都被替换。
3. 优化表格数据读取：一次性读取所有行数据，避免循环中多次调用getRange，提升执行效率。

修改后的完整代码 #

// Defina o ID da planilha
var SHEET_ID = 'spreadsheet_id';

// Defina o número máximo de destinatários por dia
var MAX_RECIPIENTS_PER_DAY = 10;

// Armazenamento em cache da planilha e do template original
var cachedSheet = null;
var cachedOriginalTemplate = null;

function getSheet() {
  if (!cachedSheet) {
    cachedSheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName('Página1');
  }
  return cachedSheet;
}

function getOriginalEmailTemplate() {
  if (!cachedOriginalTemplate) {
    try {
      var threads = GmailApp.search('subject:Teste de Implantação');
      if (threads.length > 0) {
        var messages = threads[0].getMessages();
        // 取线程中的第一条消息作为原始模板（而非最后一条）
        var originalSubject = messages[0].getSubject();
        var originalBody = messages[0].getPlainBody();
        cachedOriginalTemplate = { subject: originalSubject, body: originalBody };
      } else {
        throw new Error("Modelo de e-mail não encontrado!");
      }
    } catch (error) {
      Logger.log("Erro ao obter o modelo de e-mail: " + error);
      cachedOriginalTemplate = { 
        subject: "Modelo de e-mail não encontrado", 
        body: "Erro ao obter o modelo de e-mail." 
      };
    }
  }
  return cachedOriginalTemplate;
}

function getEmailTemplate(name, company, market, role) {
  var originalTemplate = getOriginalEmailTemplate();
  // 使用正则表达式全局替换所有占位符
  var emailSubject = originalTemplate.subject
    .replace(/{{nome}}/g, name)
    .replace(/{{empresa}}/g, company)
    .replace(/{{setor}}/g, market)
    .replace(/{{cargo}}/g, role);
  
  var emailBody = originalTemplate.body
    .replace(/{{nome}}/g, name)
    .replace(/{{empresa}}/g, company)
    .replace(/{{setor}}/g, market)
    .replace(/{{cargo}}/g, role);
  
  return { subject: emailSubject, body: emailBody };
}

function sendEmails() {
  var sheet = getSheet();
  var lastRow = sheet.getLastRow();
  var recipientsToday = 0;
  var currentDate = new Date();

  // Verifica se a planilha tem dados
  if (lastRow < 2) {
    return;
  }

  // 一次性读取所有数据，提升效率
  var allData = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).getValues();

  for (var i = 0; i < allData.length; i++) {
    var rowData = allData[i];
    var market = rowData[1];
    var company = rowData[2];
    var name = rowData[3];
    var role = rowData[4];
    var email = rowData[5];
    var status = rowData[7];
    
    // Verifica se o e-mail já foi enviado
    if (status !== 'Email Enviado' && recipientsToday < MAX_RECIPIENTS_PER_DAY && email !== "") {

      // Envia o e-mail
      var emailTemplate = getEmailTemplate(name, company, market, role);
      GmailApp.sendEmail(email, emailTemplate.subject, emailTemplate.body);
      
      // Atualiza a planilha（注意行号是i+2，因为allData从第2行开始）
      sheet.getRange(i + 2, 7).setValue(currentDate);
      sheet.getRange(i + 2, 8).setValue('Email Enviado');
      
      // Acompanha o número de destinatários
      recipientsToday++;
    }
  }
}

function checkReplies() {
  var sheet = getSheet();
  var lastRow = sheet.getLastRow();
  
  // Verifica se a planilha tem dados
  if (lastRow < 2) {
    return;
  }

  var allData = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).getValues();

  for (var i = 0; i < allData.length; i++) {
    var rowData = allData[i];
    var email = rowData[5];
    var threads = GmailApp.search('to:' + email);
    
    var hasReply = false;
    for (var j = 0; j < threads.length; j++) {
      var messages = threads[j].getMessages();
      
      for (var k = 0; k < messages.length; k++) {
        if (messages[k].isInInbox() && !messages[k].isDraft() && !messages[k].isInTrash() && messages[k].getFrom().indexOf(email) !== -1) {
          hasReply = true;
          break;
        }
      }
      if (hasReply) break;
    }
    
    if (hasReply) {
      sheet.getRange(i + 2, 8).setValue('Resposta Recebida');
    }
  }
}

function removeRecipient(email) {
  var sheet = getSheet();
  var lastRow = sheet.getLastRow();
  
  // Verifica se a planilha tem dados
  if (lastRow < 2) {
    return;
  }

  for (var i = 2; i <= lastRow; i++) {
    var rowData = sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()[0];
    if (rowData[5] === email) {
      sheet.deleteRow(i);
      break;
    }
  }
}

关键修改说明 #

• 新增getOriginalEmailTemplate函数，仅首次调用时从Gmail线程的第一条消息读取原始模板并缓存，确保后续使用的始终是未修改的模板内容。
• getEmailTemplate改为使用全局正则替换占位符，保证所有匹配项都被替换。
• sendEmails和checkReplies改为一次性读取所有表格数据，减少getRange调用次数，提升脚本执行效率。
• checkReplies中新增了判断回复来源的逻辑，避免误判自己发送的邮件为回复。

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