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 protected]

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
Did you consider or try putting phone and address each inside its own try {} block where catch ignores the split error? — Jeremy Kahan
But yes to your question, See stackoverflow.com/questions/20527670/… — Jeremy Kahan

jmindel jmindel · Accepted Answer · 2017-07-31T23:38:21

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

Extract substring of e-mail message body to google spreadsheet

2 Answers