Parsing email body into Inbound Email Action

[StewartFletcher]

Good day,

I'm trying to figure out how to get data out of a table in an email, and I've scoured multiple articles and utilised this already - How to read body text in Inbound EMAIL Flow - ServiceNow Community

However, it doesn't work for our instnace because the emails come in a tabled format, with no colons or anything defining the end of a word. Example -:

Title
First name	Test
Last name	McTest

When I tested the parsing utility in our sandbox, I was sending emails in and putting a - between Title and whatever the title is, and it would find it. However, we cannot re-format these emails (there's no option for us to do this without significant development time from another team) so I'm having to try and find a solution to this.

Could anybody suggest how we can achieve this please? I've seen various articles with scripts etc, but none of them work for us.

[James Chun]

Hi @StewartFletcher,

With some help from GenAI and testing, below is a snippet of code you can use as a starting point.

Note that I am assuming your table in your email is in a <table> tag.

var tableHTML = '<table width="427"><tbody><tr><td><p>Title</p></td><td>&nbsp;</td></tr><tr><td><p>First name</p></td><td><p>Test</p></td></tr><tr><td><p>Last name</p></td><td><p>McTest</p></td></tr></tbody></table>';

var rows = tableHTML.split('<tr>');
for(var i = 0; i < rows.length; i ++){
	var cells = rows[i].split('<td>');
	if(cells.length >= 3){
		var key = cells[1].split('</td>')[0].replace(/<\/?[^>]+(>|$)/g, "");
		var value = cells[2].split('</td>')[0].replace(/<\/?[^>]+(>|$)/g, "");
	}
	gs.info(key + " : " + value);
}

The above will print something like:

*** Script: undefined : undefined
*** Script: Title :  
*** Script: First name : Test
*** Script: Last name : McTest

Obviously, this will require more refinement but I think it will give you a good starting point.

Hope that helps, let me know if you have any questions.

Cheers

[StewartFletcher]

Thanks James - that looks like it's tailored to that set of results though, and not a catch all?

Although I tried it and changed the values to my test data, and it didn't post anything for First name, but posted what looks like a sys id for Last name, so this doesn't work sadly

[StewartFletcher]

(function execute(inputs, outputs) {

    function htmlToStr(html) {

        var noHTML = html.replace(/<br(?:\s*)?\/?>/gi, "\n").replace(/(<([^>]+)>)/ig, '');

        return decodeURI(noHTML);

    }

    try {

        // var body_text = inputs.email.replace(/<\/?[^>]+(>|$)/g, ""); //Remove HTML tags

        var body_text = htmlToStr(inputs.email.toString());

        var regEx = /^(?:\s+)?(.+?)(?:\s+)?:(?:\s+)?(.+)$/; //Name is everything preceding first :, value everything after.

        var matches;

        var emailObj = {};

        var bodyArr = body_text.split("\n");

        //loop through email body and build object

        for (var i = 0; i < bodyArr.length; i++) {

            try {

                matches = bodyArr[i].match(regEx);

                emailObj[decodeURI(matches[1].toString().trim())] = decodeURI(matches[2].toString().trim()); //Add trimmed name/val to object

            } catch (ex) {

                gs.error(ex.message);

            }

        }

    } catch (ex) {

        gs.error(ex.message);

    }

    outputs.email_var = emailObj; //return parsed name/val pairs

})(inputs, outputs);


So that's the script from the Parse Email Body Text action I was able to find, which works perfectly for anything OUTSIDE of the table, but cannot fathom exactly how to get it to see what's INSIDE the table tags

[StewartFletcher · Accepted solution]

I've managed to work through and resolve this one myself. I had to amend the parsing script quite a lot, and in the end feed it a list of items in an array to be able to identify which should be the keys, and including blank values. Here's the script in case this helps anybody in future - the third replace in the htmlToStr function are table headers that we had that I needed to strip out. Further down this also strips the 

(function execute(inputs, outputs) {

    function htmlToStr(html) {

        return html.replace(/&lt;br(?:\s*)?\/?&gt;/gi, "\n")

            .replace(/(&lt;([^&gt;]+)&gt;)/ig, '')

            .replace(/(?:Details 1|Details 2|Details 3)\s*\n?/gi, '');

    }

    try {

        var expectedFields = [

            "Title", "First name", "Last name", "Address line 1", "Address line 2",

            "Address line 3", "Town / city / region", "Postcode:", "Email address",

            "Telephone number"

        ];

        var body_text = htmlToStr(inputs.email.toString());

        var bodyArr = body_text.split("\n").map(function(line) {

            return line.trim();

        }).filter(function(line) {

            return line.length > 0;

        });

        var emailObj = {};

        for (var i = 0; i < bodyArr.length; i++) {

            var key = bodyArr[i];

            if (expectedFields.includes(key)) {

                var value = (i + 1 < bodyArr.length && !expectedFields.includes(bodyArr[i + 1])) ? bodyArr[i + 1] : '';

                emailObj[key] = value;

                if (value !== '') i++; // Skip value line

            }

        }

        if (emailObj["Email address"]) {

        emailObj["Email address"] = emailObj["Email address"]

        .replace(/<.*?>/g, '') // Remove anything in angle brackets

        .replace(/mailto:/gi, '') // Remove 'mailto:' if present

        .trim();

}

        outputs.email_var = emailObj;

    } catch (ex) {

        gs.error("Email parsing error: " + ex.message);

        outputs.email_var = {};

    }

})(inputs, outputs);