Google Scripts to draft replies to emails with keyword in subject and inputting data from Google Sheets

Question

I'm new to Google Apps Script so I'm looking for some advice. There's multiple parts and I managed to do some of it but I'm stuck on others. Any help would be much appreciated.

I'm trying to make a script that:

1. drafts a reply to emails that contain specific keywords (found in the body or the subject line).
2. I also want it to include a template with data inputted from a Google Sheets file.
3. It would be preferable if the draft can be updated without making a duplicate whenever the Sheet is modified.
4. I plan on also including a row of values (the first one) that correspond to the Subject columns in the second row the but I haven't gotten to it yet.

Some details about the Google Sheet:

• Each row corresponds to a different person and email address that regularly emails me.
• The first three columns are details about the person which I include in the body of my draft.
• Each column after that represents a different string or keyword I expect to find in the subject of the emails sent to me.

• The rows underneath contain two patterned code-words separated by a space in one cell that I want to be able to choose from. Such as:

  • 3 letters that can contain permutations of the letters m, g, r (for ex: mmg, rgm, rgg, gmg)

  • and 0-3 letters with just p's (for ex: p, pp, ppp, or blank)

I want to be able to detect the different codes and assign it to a variable I can input into my draft.

---

What I have so far:

• I'm able to draft replies for emails within my specified filter. However, I only have it set up to reply to the person's most recent message. I want to be able for sort through the filter for specific emails that contain a keyword in the subject line when it loops through the columns.

• I'm able to input static strings from the Sheet into the body of my email but I'm still having trouble with the patterned codewords.

• I was able to loop through more than one row in earlier version but now it's not. I'll look over it again later.

Here's my code:

function draftEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();    // Use data from the active sheet
  var startRow = 1;                            // First row of data to process
  var numRows = sheet.getLastRow() - 1;        // Number of rows to process
  var lastColumn = sheet.getLastColumn();      // Last column
  var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn) // Fetch the data range of the active sheet
  var data = dataRange.getValues();            // Fetch values for each row in the range

  // Work through each row in the spreadsheet
  for (var i = 2; i < data.length; ++i) {
    var row = data[i];  
    // Assign each row a variable
    var grader = row[0];                    // Col A: Grader's name
    var firstName = row[1];                 // Col B: Student's first name
    var studentEmail = row[2];              // Col C: Student's email
    var grade = row[3].split(' ');           // Col D: Grade
    var pgrade = grade[1];
    var hgrade = grade[0];
      for (var n = 1; n < data.length; ++n) {
      var srow = data[n];  
      var subjectCol = srow[3];

    var threads = GmailApp.getUserLabelByName('testLabel').getThreads();

    for (i=0; i < threads.length; i++)
    {
          var thread = threads[i]; 
          var messages = thread.getMessages(); // get all messages in thread i
          var lastmsg  = messages.length - 1;  // get last message in thread i

          var emailTo    = WebSafe(messages[lastmsg].getTo());    // get only email id from To field of last message
          var emailFrom  = WebSafe(messages[lastmsg].getFrom());  // get only email id from FROM field of last message
          var emailCC    = WebSafe(messages[lastmsg].getCc());    // get only email id from CC field of last message


    // form a new CC header for draft email
        if (emailTo == "")
        {
           var emailCcHdr = emailCC.toString();
        } else
        {
          if (emailCC == "")
          {
            var emailCcHdr = emailTo.toString();
          } else
          {
            var emailCcHdr = emailTo.toString() + "," + emailCC.toString();
          }
        }

        var subject  = messages[lastmsg].getSubject().replace(/([\[\(] *)?(RE|FWD?) *([-:;)\]][ :;\])-]*|$)|\]+ *$/igm,"");
        // the above line remove REs and FWDs etc from subject line

        var emailmsg = messages[lastmsg].getBody(); // get html content of last message

        var emaildate   = messages[lastmsg].getDate(); // get DATE field of last message

        var attachments = messages[lastmsg].getAttachments(); // get all attachments of last message

        var edate = Utilities.formatDate(emaildate, "IST", "EEE, MMM d, yyyy"); // get date component from emaildate
        var etime = Utilities.formatDate(emaildate, "IST", "h:mm a"); // get time component from emaildate

    if (emailFrom.length == 0)
        {
          // if emailFrom is empty, it probably means that you may have written the last message in the thread. Hence 'you'.
          var emailheader = '<html><body>' + 
                            'On' + '&nbsp;' +
                             edate + '&nbsp;' + 
                            'at' + '&nbsp;' + 
                             etime +  ',' + '&nbsp;' + 'you' + '&nbsp;' + 'wrote:' + '</body></html>';
        } else 
        {
          var emailheader = '<html><body>' + 
                            'On' + '&nbsp;' +
                             edate + '&nbsp;' + 
                            'at' + '&nbsp;' + 
                             etime +  ',' + '&nbsp;' + emailFrom + '&nbsp;' + 'wrote:' + '</body></html>';
        }
          var emailsig = '<html>' + 
                         '<div>your email signature,</div>' +
                         '</html>'; // your email signature i.e. common for all emails.

                  // Build the email message
          var emailBody =  '<p>Hi ' + firstName + ',<p>';
              emailBody += '<p>For ' + subjectCol + ', you will be graded on #1, 2, and 3: <p>';
              emailBody += '<p>Participation: ' + pgrade + '</p>';
              emailBody += '<p>HW grade: ' + hgrade + '</p>';
              emailBody += '<p>If you have any questions, you can email me at ' + grader + '@email.com.<p>';
              emailBody += '<p>- ' + grader;


          var draftmsg = emailBody + '<br>' + emailsig + '<br>' + emailheader + '<br>' + emailmsg + '\n'; // message content of draft

      // Create the email draft
      messages[lastmsg].createDraftReply(
        " ", // Body (plain text)
        {
        htmlBody: emailBody    // Options: Body (HTML)
        }
      );

    }

}
}

function WebSafe(fullstring)
{
  var splitString = fullstring.split(",");
  var finalarray = [];
  for (u=0; u < splitString.length; u++)
  {
    var start_pos = splitString[u].indexOf("<") + 1;
    var end_pos   = splitString[u].indexOf(">",start_pos);
    if (!(splitString[u].indexOf("<") === -1 && splitString[u].indexOf(">",start_pos) === -1)) // if < and > do exist in string
    {
      finalarray.push(splitString[u].substring(start_pos, end_pos));
    } else if (!(splitString[u].indexOf("@") === -1))
    {
      finalarray.push(splitString[u]);
    }
  }
  var index = finalarray.indexOf(grader + "@email.com"); // use your email id. if the array contains your email id, it is removed.
  if (index > -1) {finalarray.splice(index, 1);}
  return finalarray
}
}

I've never coded in JavaScript before or used Google Scripts so I mostly looked at similar examples.

Thank you for any feedback.

Answer 1

I prefer reading code that isn't too nested. So I took the liberty to re-write your code and make it easier to read.

Your main function:

function mainFunction(){

  // Use data from the active sheet
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var data = sheet.getDataRange().getValues();
  var threads = GmailApp.getUserLabelByName('<YOUR-LABEL-HERE>').getThreads();
  var subject1 = data[1][3];

  // Work through each row in the spreadsheet omit headers
  for (var i = 2; i < data.length; ++i) {

    // Get grader's data
    var grader = getGrader(data[i]);
    console.log(grader);

    // Loop through threads
    for (j=0; j < threads.length; j++){
      var thread = threads[j];
      // Get last message in thread
      var messages = thread.getMessages(); 
      var lastMsg = messages[messages.length - 1];
      var email = new Email(grader, lastMsg, subject1);
      // Create the draft reply.
      var draftMessageBody = createDraftMessage(email);
      lastMsg.createDraftReply(draftMessageBody);
    }
  }
}

Support functions:

1. Function getGrader:

function getGrader(array){ 
  var row = array
  var grader = {}
  grader.grader = row[0];                    
  grader.firstName = row[1];                 
  grader.studentEmail = row[2];              
  var grade = row[3].split(' ');          
  grader.pgrade = grade[1];
  grader.hgrade = grade[0];
  return grader
}

2. Function webSafe:

function webSafe(fullstring, grader){
  var splitString = fullstring.split(",");
  var finalarray = [];
  for (u=0; u < splitString.length; u++){
    var start_pos = splitString[u].indexOf("<") + 1;
    var end_pos   = splitString[u].indexOf(">",start_pos);
    // if < and > do exist in string
    if (!(splitString[u].indexOf("<") === -1 && splitString[u].indexOf(">",start_pos) === -1)){
      finalarray.push(splitString[u].substring(start_pos, end_pos));
    } else if (!(splitString[u].indexOf("@") === -1)){
      finalarray.push(splitString[u]);
    }
  }
  // use your email id. if the array contains your email id, it is removed.
  var index = finalarray.indexOf(grader.grader + "@mangoroot.com"); 
  if (index > -1) {
    finalarray.splice(index, 1);
  }
  return finalarray
}

3. Function Email: Behaves like a class

var Email = function(grader, lastMsg, subject){
  this.signature = "your_email_signature,";
  this.grader = grader;
  this.to = webSafe(lastMsg.getTo(), this.grader);
  this.from = webSafe(lastMsg.getFrom(), this.grader);
  this.cc = webSafe(lastMsg.getCc(), this.grader);
  this.subject = lastMsg.getSubject().replace(/([\[\(] *)?(RE|FWD?) *([-:;)\]][ :;\])-]*|$)|\]+ *$/igm,"");
  this.message = lastMsg.getBody();
  this.date = lastMsg.getDate();
  this.attachments = lastMsg.getAttachments();
  this.subject1 = subject;

  this.ccHeader = function() {
    var ccHeader = "";
    if (this.to == "" || this.cc == ""){
      ccHeader = this.cc.toString();
    }
    else {
      ccHeader = this.to.toString() + "," + this.cc.toString();
    }
    return ccHeader
  }

  this.eDate = function() {
    return Utilities.formatDate(this.date, "IST", "EEE, MMM d, yyyy");
  }

  this.eTime = function() {
    return Utilities.formatDate(this.date, "IST", "h:mm a");
  }

  this.header = function() {
    var header = ''.concat('On ');
    if (this.from.length == 0){
      header += this.eDate().concat(' at ',this.eTime(),', you wrote: ');
    }
    else {
      header += this.eDate().concat(' at ',this.eTime(),', ',this.from,' wrote: ');
    }
    return header
  }

  this.body = function(){
    var grader = this.grader;
    var body =  '<div>'.concat('<p>Hi ',grader.firstName,',</p>');
    body += '<p>For '.concat(this.subject1,', you will be graded on #1, 2, and 3: </p>');
    body += '<p>Participation: '.concat(grader.pgrade,'</p>');
    body += '<p>HW grade: '.concat(grader.hgrade,'</p>');
    body += '<p>If you have any questions, you can email me at '.concat(grader.grader,'@mangoroot.com.</p>');
    body += '<p>- '.concat(grader.grader,'</p>','</div>');
    return body;
  }
}

4. Function createDraftMessage:

function createDraftMessage(email){
  var draft = '<html><body>'.concat(email.body);
  draft += '<br>'.concat(email.signature);
  draft += '<br>'.concat(email.header);
  draft += '<br>'.concat(email.message);
  draft += '<br>'.concat('</body></html>');
  return draft;
}

---

Now when you run mainFunction() you should get your expected drafts.

---

Notes:

• It is good practice to keep functions flat, flat is better than nested. Makes the code more readable and maintainable.
• Also be consistent in your variable naming style.

var emailMsg = ''; // Good. 
var emailmsg = ''; // Hard to read. 

• Have a read about classes