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.

[HIROSHI SATOH]

Can I load tables with DOMParser?

var htmlContent = email.body_html;

// Extract the table from the HTML content
var parser = new DOMParser();
var doc = parser.parseFromString(htmlContent, 'text/html');
var table = doc.querySelector('table'); // Assuming there's only one table

// Get the header (first row)
var headers = [];
var headerRow = table.rows[0];
for (var i = 0; i < headerRow.cells.length; i++) {
    headers.push(headerRow.cells[i].textContent.trim());
}

// Loop through the data rows
for (var r = 1; r < table.rows.length; r++) {
    var row = table.rows[r];
    var incidentData = {};
    
    // Map values from the row to headers
    for (var c = 0; c < row.cells.length; c++) {
        incidentData[headers[c]] = row.cells[c].textContent.trim();
    }
    
    // Create an Incident for each row of data
    var gr = new GlideRecord('incident');
    gr.initialize();
    gr.short_description = 'Created from inbound email data';
    gr.caller_id = incidentData['Caller']; // Example field
    gr.description = incidentData['Description']; // Example field
    // Set other fields as necessary using the data in incidentData
    gr.insert();
}

[jmiskey]

Thanks. 

I am not sure what you mean by this:

"Can I load tables with DOMParser?"

I don't really understand what that means, or what you are asking me.

The other thing I am not sure I see in the code, is how to extract the information from the first and second columns to put in the Short Description and Description fields on the Incident.

[HIROSHI SATOH]

Let CHATGPT analyze this code and he'll explain it in detail.

[jmiskey]

Did you use ChatGPT to come up with the code, or did you write it yourself?

Were you able to confirm that it works?

I am having some issues getting it to fire.

Initially, I had the Target Table set to "Incident", as that is what we are trying to create and that is how we have done Inbound Email actions in the past.  However, when you use that, the Script typically does not include GlideRecords and you just set the field values on the Incident using like this:

current.field_name = "value";

And when I ran a test with the Target Table set to Incident, it did not run and the log file gave me the message:

"Inbound email action name: did not create or update incident using current".

So I thought maybe we are trying to create multiple records, and are using a GlideRecord to do that, I should leave the Target Table field on the Inbound Email Action as "None", and just let the script create the records.  However, that did not work either.  The message I got that time was "Skipping script, inbound email action name:  a suitable GlideRecord not found".

What am I doing wrong?