Inbound Email Action: Parse Data From Table in Email to Create Multiple Incidents

[jmiskey]

We have a project where ServiceNow will be receiving an inbound email, and needs to create Incidents off of it.  The information needed to create the Incidents is found in an HTML table in the email.  I have found other threads discussing similar issues, but this one appears to be a bit different than those ones I found. 

In all the other ones I found, the field name was in the first column of the table, and the value was in the second column.  My table is structured differently.  It has field name in the first row of the table, then data runs under it.  So it will look something like this:

So, there will always be at least one row of data, but there could be more.

From this, I need to create an Incident for each separate data row.  So, from the example I above, I would need to create 3 separate Incidents, one for each data row.  

How can I do this? 

How can I parse my data table in the inbound email action, and create an Incident for each row (using the values/details from each row)?

So, my end result would be three separate Incidents.  The Short Description of these Incidents would look like:

Incident 1: "Site Expiration Notice for abc.com"

Incident 2: "Site Expiration Notice for lmn.com"

Incident 3: "Site Expiration Notice for xyz.com"

Thanks.

[jmiskey]

I am not sure that code works within the Inbound Email Action.

Were you actually able to get this code to work yourself?

I am trying to figure out if I am doing something wrong, or the code won't work in an Inbound Email Action. I really don't want to waste any more time on something that will not work.

[jmiskey]

Still looking for a workable solution.

[jmiskey]

No need to worry about this anymore.  I went another route, and converted all the HTML to text, and parsed that to get what I need.

[chilakalasa]

Hi @jmiskey,

Can you share the solution that's working for you? I also need to extract the data from a table from the email.

[jmiskey]

This project never got past the testing phase before the testing team pivoted off of emails and worked on direct connections with ServiceNow.  So we never implemented it.  However, I was able to dig through my old notes, and locate the Script we had created for the Inbound Email Action that did this.  

Here is what that code looks like:

(function() {
    // Email body text
    var emailBodyText = email.body_text;
    
    // Extract relevant section of the email body
    var startKeyword = "Days Left";  //header of last column name before the start of the first row of data
    var endKeyword = "This article";  //first words after the last row of data
    var extractedText = extractTextBetweenKeywords(emailBodyText, startKeyword, endKeyword);
    
    // Parse the extracted text and create incidents
    createIncidentsFromText(extractedText);
    
    function extractTextBetweenKeywords(text, startKeyword, endKeyword) {
        var startIndex = text.indexOf(startKeyword);
        var endIndex = text.indexOf(endKeyword);
        
        if (startIndex === -1 || endIndex === -1 || startIndex >= endIndex) {
            gs.error("Invalid start or end keyword in the email body");
            return "";
        }
        
        // Extract and return the text between the keywords
        return text.substring(startIndex + startKeyword.length, endIndex).trim();
    }

    function createIncidentsFromText(text) {
        var lines = text.split(/\r?\n/);
        
        for (var i = 0; i < lines.length; i += 3) {
            if (i + 2 < lines.length) {
                var siteName = lines[i].trim();
                var expirationDate = lines[i + 1].trim();
                var daysLeft = lines[i + 2].trim(); // daysLeft is not used but can be logged or used as needed
                
                // Create a new incident record
                var incident = new GlideRecord('incident');
                incident.initialize();
                incident.submitter = email.from;  
                incident.caller_id = email.from;  
                incident.category = "Network";
                incident.subcategory = "Configuration";
                incident.incident_state = 1;
                incident.impact = '4';
                incident.urgency = '3';
                incident.priority = '3';
                incident.contact_type = "email";
                incident.assignment_group.setDisplayValue("Network");
                incident.short_description = 'Expiration Warning - ' + siteName;
                incident.description = 'Site Name: ' + siteName + '\nExpiration Date: ' + expirationDate;
                incident.insert();
                
                //gs.info('Created incident for Site Name: ' + siteName + ' with Expiration Date: ' + expirationDate);
            }
        }
    }
})();