Extract substring of e-mail message body to google spreadsheet

Question

I'm using google app scripts to extract e-mail data into a google spreadsheet. I've got the below working code that I am trying to modify. I'm sure there's a smarter way ... but for now this works

function emf() {

  var ss = SpreadsheetApp.getActiveSheet();

  var label = GmailApp.getUserLabelByName("tkh_emf");
  var threads = label.getThreads();

  for (var i=0; i<threads.length; i++)
  {
    var messages = threads[i].getMessages();

    for (var j=0; j<messages.length; j++)
    {
      var name = messages[j].getPlainBody().split("Name*:")[1].split("\n")[0];
      var email = messages[j].getPlainBody().split("E-mail*:")[1].split("\n")[0];
      var phone = messages[j].getPlainBody().split("Phone:")[1].split("\n")[0];
      var addr = messages[j].getPlainBody().split("Street Address:")[1].split("\n")[0];
      var city = messages[j].getPlainBody().split("City*:")[1].split("\n")[0];
      var find = messages[j].getPlainBody().split("hear about us?*:")[1].split("\n")[0];
      var sub = messages[j].getSubject().split("Feedback via the ")[1].split("[")[0];
      var num = messages[j].getSubject().split("Feedback via the ")[1].split("[")[1].split("]")[0];
      var dat = messages[j].getDate();
      var referrer = messages[j].getPlainBody().split("Referer URL:")[1].split("\n")[0];

      ss.appendRow([name, email, phone, addr, city, find, sub, num, dat, referrer])
    }
      threads[i].removeLabel(label);
  }
}

My e-mail looks like this:

Name*: name

E-mail*: email@gmail.com

Phone:

Street Address: 3704 17th St.

City*: city

How did you hear about us?*: Search engine results

Brief description of work requested*: work here

So my code extracts the appropriate strings for each field except the 'Phone' and 'Address' fields which are not required. If these fields are not filled, then the e-mail does not have the words 'Phone' or 'Street Address' so the lines for var phone and var addr fail because you can't split a null. Is there a way to insert an if string 'Phone' and 'Street Address' exists then execute the above? Thanks.

Are you interested in also being able to validate the format of emails or addresses, or do you just want the data separated out? — jmindel, Jul 31 '17 at 22:37
Did you consider or try putting phone and address each inside its own try {} block where catch ignores the split error? — Jeremy Kahan, Aug 01 '17 at 02:56
But yes to your question, See https://stackoverflow.com/questions/20527670/google-script-to-see-if-text-contains-a-value — Jeremy Kahan, Aug 01 '17 at 03:03

score 0 · Answer 1 · answered Jul 31 '17 at 23:38

You were right that you'll need regex to accomplish the job (or it'll certainly make it easier). I've written a simple script in Codepen that'll show you how to use the regex.

In my script, I split the body data at the newline character, and then loop through the resulting array of lines. I pipe each line into a function that captures and returns the text you need. You needn't pipe in anything other the line--it detects what the name of the field is, and uses it appropriately, based on your current format.

In your own code, you would have to do the following to msg before placing it into your spreadsheet:

var msg = messages[j].getPlainBody();
var sub = messages[j].getSubject();
var dat = messages[j].getDate();

var bodyLines = msg.split("\n");
var fields = [];

for (var k = 0; k < bodyLines.length; k++) {
    fields.push(getText(bodyLines[k]));
}

// do something with the resulting array of fields here

Here's the getText(str) function (can also be found in Codepen):

function getText(str) {
    var fieldRe = new RegExp("(.+)\:", "g");
    var fieldGroups = fieldRe.exec(str);
    var fieldName = fieldGroups[1].split("*")[0];
    fieldName = (fieldName == null) ? fieldGroups[1] : fieldName;
    fieldName = fieldName.replace(/[\!\@\#\$\%\^\&\*\(\)\-\_\+\=\`\~\[\]\{\}\\\/\|\:\;\'\"\<\>\,\.\?]/g, function transformIllegal(x) {
        return "\\" + x;
    });

    var re = new RegExp(`${fieldName}\\*?\\:\\s+(.*)`, "g");
    var groups = re.exec(str);
    var out = (groups == null) ? "" : groups[1];

    return out;
}

@user5753132 What error is it giving you? Is it for the new code that you've edited in your question, or the previous code? Also, if you're looking for a simpler way to do it, what I've written will at least simplify your breakdown of the message's plain body, and you can then use each of the items in the resulting array in place of `name`, `email`, `phone`, `addr`, `city`, and `find`, respectively. I'm not sure what the format of your subject and referrer are, so I don't know if the method I wrote will help with those. Might be worth a shot though. — jmindel, Aug 01 '17 at 10:20

score 0 · Answer 2 · answered Aug 02 '17 at 11:25

Here's what I'm ending with. Not sophisticated but works.

function emf() {

  var ss = SpreadsheetApp.getActiveSheet();

  var label = GmailApp.getUserLabelByName("tkh_emf");
  var threads = label.getThreads();

  for (var i=0; i<threads.length; i++)
  {
    var messages = threads[i].getMessages();

    for (var j=0; j<messages.length; j++)
    {
      var name = messages[j].getPlainBody().split("Name*:")[1].split("\n")[0];
      var email = messages[j].getPlainBody().split("E-mail*:")[1].split("\n")[0];
      try {var phone = messages[j].getPlainBody().split("Phone:")[1].split("\n")[0];}
      catch(e){var phone = "-";}
      try {var addr = messages[j].getPlainBody().split("Street Address:")[1].split("\n")[0];}
      catch(e){var addr = "-";} 
      var city = messages[j].getPlainBody().split("City*:")[1].split("\n")[0];
      var find = messages[j].getPlainBody().split("hear about us?*:")[1].split("\n")[0];
      try {var referrer = messages[j].getPlainBody().split("Referrer Name:")[1].split("\n")[0];}
      catch(e){var referrer = "-";}
      var sub = messages[j].getSubject().split("Feedback via the ")[1].split("[")[0];
      var num = messages[j].getSubject().split("Feedback via the ")[1].split("[")[1].split("]")[0];
      var dat = messages[j].getDate();

      ss.appendRow([name, email, phone, addr, city, find, referrer, sub, num, dat])
    }
      threads[i].removeLabel(label);
  }
}

Extract substring of e-mail message body to google spreadsheet

2 Answers2