Google sheets script to send email

Question

I just need the script (sendEmail02()) to collect data from the table of only the person whose mail is contained in column 2 and send him all the data in one letter, and so each person in turn. It is necessary that during the iteration, the addresses are read from column 2 and data is collected and sent in a separate letter to each recipient. It would be great if each user was sent only one letter at a time with the contents of all the necessary lines.

  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 = '[email protected]'; // 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,
  });
   }
  }
  }

From the above code

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

I dont know how to collect all unique emails from Col2 and run check for each one to send message. — Vladimir Melnikov
Please edit the question to make it clear what you are asking. — Rubén
Related Javascript extract subarray from 2D array using spreadsheet range A1 notation — Rubén
You seem to want this to run from an onEdit trigger is that correct? — Cooper

Cooper Cooper · Accepted Answer · 2019-12-23T00:57:46

This function collects all of the messages for each recipient and sends each recipient one email with all messages in it. I modified the messages to make them simpler for debugging purposes so you will need to rewrite your messages.

I also used a dialog to display message content rather than sending emails so you'll have to enable that and test it yourself.

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");
  }
}

I tested it with you data and this is the dialog output after setting all the lines in column one to sent.

Recipient: [email protected]
Subject; Hello
Message:

Email:[email protected] Text:Data1
Email:[email protected] Text:Data2
Email:[email protected] Text:Data3
Email:[email protected] Text:Data6
Email:[email protected] Text:Data7

Recipient: [email protected]
Subject; Hello
Message:

Email:[email protected] Text:Data4

Recipient: [email protected]
Subject; Hello
Message:

Email:[email protected] Text:Data5
Email:[email protected] Text:Data8
Email:[email protected] Text:Data9

Google sheets script to send email

1 Answers