用于发送电子邮件的 Google Sheets 脚本

链接到工作表 https://docs.google.com/spreadsheets/d/1qOuZigHnjBi85ttU4vbj0vIJpn7GAlqHPa7Lt8I2bUA/edit?usp=sharing

我只需要脚本 (sendEmail02()) 只从第 2 列中包含邮件的人的表中收集数据，然后将所有数据通过一封信发送给他，依次发送给每个人。有必要在迭代过程中从第 2 列读取地址并收集数据并以单独的信件形式发送给每个收件人。如果一次只向每个用户发送一封包含所有必要行内容的信件，那就太好了。

  function sendEmail02() {
  var ss = SpreadsheetApp.openById(sheetID);
  var ActiveSheet = ss.getSheetByName("Sheet1");
  if (ActiveSheet.getName() == 'Sheet1')  {
  var StartRow = 2;
  var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
  var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,3);
  var AllValues = WholeRange.getValues();

  var message = "";
  for (i in AllValues) {

  var CurrentRow = AllValues[i];

  if (CurrentRow[0] == "sent" && CurrentRow[1] != ""&& CurrentRow[2] != "") {

    message +=
    "<p><b> ???? </b>" +
      "<p><b> Email: </b>" + CurrentRow[1] + "</p>" +
        "<p><b> Text: </b>" + '<font color="green">' + CurrentRow[2] + '</font>' + "</p>" + "</p> 
   <br><br>";

    var setRow = parseInt(i) + StartRow;

    ActiveSheet.getRange(setRow, 1).setValue("done");
   }
   }
   var recipientsTO = 'te[email protected] /cdn-cgi/l/email-protection'; // this must be collected from Col2.But how?
   var SendTo = (MyEmail + "," + recipientsTO); 
   var Subject = "Hello" ;

   if (message) {
    MailApp.sendEmail({
    bcc: SendTo,
    subject: Subject,
    htmlBody: message,
  });
   }
  }
  }

从上面的代码来看

var recipientsTO = '[email protected] /cdn-cgi/l/email-protection'; // this must be collected from Col2.But how?

此功能收集每个收件人的所有消息，并向每个收件人发送一封包含所有消息的电子邮件。我修改了这些消息，使其更易于调试，因此您需要重写消息。

我还使用了一个对话框来显示消息内容而不是发送电子邮件，因此您必须启用它并自行测试。

function sendEmail02() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Sheet1');
  if (sh.getName()=='Sheet1')  {
    var rg=sh.getRange(2,1,sh.getLastRow()-1,3);//What about column 4?
    var v=rg.getValues();
    var mObj={rA:[]};
    var html='';
    for (var i=0;i<v.length;i++) {
      if (v[i][0]=="sent" && v[i][1]!="" && v[1][2] != "") {
        var message=Utilities.formatString('<br />Email:%s Text:%s',v[i][1],v[i][2]);
        if(mObj.hasOwnProperty(v[i][1])) {
          mObj[v[i][1]]+=message;
          mObj[v[i][1]+'rows'].push(i+2);
        } else {
          mObj[v[i][1]]=message;
          mObj.rA.push(v[i][1]);
          mObj[v[i][1]+'rows']=[];
          mObj[v[i][1]+'rows'].push(i+2);
        } 
      }
    }
    for(var i=0;i<mObj.rA.length;i++) {
      var SendTo=mObj.rA[i];
      var Subject = "Hello" ;
      //MailApp.sendEmail({bcc: SendTo,subject: Subject,htmlBody: mObj[mObj.rA[i]]+});
      html+=Utilities.formatString('<br />Recipient: %s<br />Subject; %s<br />Message: <br />%s<br /><hr width="100%">',SendTo,Subject,mObj[mObj.rA[i]])
      for(var j=0;j<mObj[mObj.rA[i]+'rows'].length;j++) {
        sh.getRange(mObj[mObj.rA[i]+'rows'][j],1).setValue("done");
      }
    }
    var ui=HtmlService.createHtmlOutput(html).setWidth(1000);
    SpreadsheetApp.getUi().showModelessDialog(ui, "Emails Sent");
  }
}

我用您的数据对其进行了测试，这是将第一列中的所有行设置为发送后的对话框输出。

Recipient: [email protected] /cdn-cgi/l/email-protection
Subject; Hello
Message:

Email:[email protected] /cdn-cgi/l/email-protection Text:Data1
Email:[email protected] /cdn-cgi/l/email-protection Text:Data2
Email:[email protected] /cdn-cgi/l/email-protection Text:Data3
Email:[email protected] /cdn-cgi/l/email-protection Text:Data6
Email:[email protected] /cdn-cgi/l/email-protection Text:Data7

Recipient: [email protected] /cdn-cgi/l/email-protection
Subject; Hello
Message:

Email:[email protected] /cdn-cgi/l/email-protection Text:Data4

Recipient: [email protected] /cdn-cgi/l/email-protection
Subject; Hello
Message:

Email:[email protected] /cdn-cgi/l/email-protection Text:Data5
Email:[email protected] /cdn-cgi/l/email-protection Text:Data8
Email:[email protected] /cdn-cgi/l/email-protection Text:Data9