Want to send attchment(excel sheet) through inbound email action and information is populated on incident table

[Harsha1]

Hello,

I want to send a attachment  (through mail) excel sheet Information into the servicenow with the help of inbound email action. and the information is populated on the incident table. please provide step by step process.

Thanks & Regards,

Harsha

[Priyanka Patil2]

Hello Harsha,

Try what williem said. Also You may have a look on below option as well and this might Help. I have tried this code earlier and it worked. You Just need to make changes in code according to  your requirement, but rest remains the same.

Loading data from an email attachment

Creating CSV file using script And Fetching contents of CSV file using script

Please mark Helpful/correct if it resolves your issue.

Thank you,

Priyanka Patil.

[AbhishekGardade]

Hello Harsha, 
Can you let us know what all you have done to achieve this use case ? So we can help you this.

As already provided answer by other contributers, try out these options. My approach will be as below:

1. Create a Inbound action for Incident table with proper subject line. By specifying Subject line, will help us to understand on which scenario's we need to process inbound action to fetch excel contents.

2. In inbound action, verify if that email is available with attachment or not. If yes, do process the code otherwise do not run import set as per Willem's code. Also verify the attachment type before running all. It may cause not to run import set which process the code.

3. If you are doing with the way said then you dont need to worry about retriving contains from excel. If you are getting errors in Import set. Do check the Import logs and transform history for your import set run.

4. If your parsing excel in inbound instead using import set then, check if attachment contents are parsed correctly.

5. Add logs in code, logpoints or breakpoints to check where you are getting errors. 

6. In Emails table, you can find check if your inbound runs or which inbound email action is processed.

Thank You!
Abhishek Gardade
ServiceNow MVP 2020

[AbhishekGardade · Accepted solution]

Hello Harsha,

Below are the steps I performed on your instance to achieve your requirement.

1. Created Transform Map and Import Set table: 

Transform Map Name :Incident Creation from Inbound

Import set table Name : Incident Template(x_433801_email_incident_template)

Field Mappings : State, Category & Short Description,

Note : In case you need to add more fields in excel then you need to create a field mapping in transform maps as well.

2. Created a Inbound Action :

Name : "Create Incident With Attachment" with below code :

Table : Data Source (sys_data_source)     

Stop processing : TRUE (Checked)

Condition : Subject STARTS WITH "Template: Incident Creation"       

Order : 50

Script :

(function runAction( /*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {

var importSetTableName = "x_433801_email_incident_template";
var applicatonScope = "Email";
var transformMapIDs = "1d10d3e52fbed010622959a72799b611";

// Create the datasource record
current.name = "File import from: " + email.from; //Feel free to rename this as appropriate
current.import_set_table_name = importSetTableName;
current.file_retrieval_method = "Attachment";
current.type = "File";
current.format = "Excel"; // For Excel Files
//current.format = "CSV"; // For CSV Files
current.header_row = 1;
current.sheet_number = 1;
current.sys_package.setDisplayValue(applicatonScope);
current.sys_scope.setDisplayValue(applicatonScope);
var dataSourceID = current.insert();

new global.EmailFileImportUtils().scheduleImport(dataSourceID, transformMapIDs);

})(current, event, email, logger, classifier);

3. Created a Script Include In Global Application scope: If you are creating with in scoped application then schedule will be created in sys_trigger table and your transform wont run.

Name : EmailFileImportUtils

API Name : global.EmailFileImportUtils

Accessible from : All application Scope

Script :

var EmailFileImportUtils = Class.create();
EmailFileImportUtils.prototype = {
initialize: function() {},

scheduleImport: function(dataSourceID, transformMapIDs) {

var schRec = new GlideRecord("sys_trigger");
schRec.name = "Load Data Source: " + dataSourceID;
schRec.trigger_type = 0; // Run Once
schRec.script = "new global.EmailFileImportUtils().loadImportSet('" + dataSourceID + "', '" + transformMapIDs + "')";

var nextAction = new GlideDateTime();
nextAction.addSeconds(30); // 30 seconds should be enough time however this can be changed.
schRec.next_action = nextAction;
var triggerID = schRec.insert();
gs.info("triggerID:" + triggerID);
},

loadImportSet: function(dataSourceID, transformMapIDs) {

// Get Datasource Record
var dataSource = new GlideRecord("sys_data_source");
dataSource.get(dataSourceID);

// If CSV and header isn't on row 1, recreate attachment with empty rows removed, and delete original
if (dataSource.getValue("format") == "CSV" && dataSource.getValue("header_row") > 1) {
var attachmentRec = new GlideRecord("sys_attachment");
attachmentRec.addQuery("table_sys_id", dataSource.getValue("sys_id"));
attachmentRec.query();
if (attachmentRec.next()) {
var oldAttachmentID = attachmentRec.getValue("sys_id");
var inputStream = new GlideSysAttachment().getContentStream(oldAttachmentID);
var textReader = new GlideTextReader(inputStream);
var ln = " ";
var newLine = "";

var lineCounter = 0;
var headerRow = parseInt(dataSource.getValue("header_row"));
while ((ln = textReader.readLine()) != null) {
lineCounter += 1;
if (lineCounter < headerRow) {
continue;
}

if (ln.length > 1) {
newLine += ln + "\n";
}
}
new GlideSysAttachment().write(dataSource, 'Changed ' + attachmentRec.getValue('file_name'), "text/csv", newLine);
//Delete original email attachment so that the new one is processed. If two remain, API may get confused on which one to process.
attachmentRec.deleteRecord();
}
}

// Process data source file
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(dataSource);
var ranload = loader.loadImportSetTable(importSetRec, dataSource);
importSetRec.state = "loaded";
importSetRec.update();

// Transform import set
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapIDs);
transformWorker.setBackground(true);
transformWorker.start();
},

type: 'EmailFileImportUtils'

};

Please let me you if you are facing issue while testing or any explanation required on codes.

Please mark as Correct Answer and Helpful, if applicable.
Thank You!
Abhishek Gardade
ServiceNow MVP 2020

[mastanbe]

Hi Abhishek Gardade,

can i use this one for Scheduled imports , is there any changes required for this script include

[Amey8]

Hi Abhishek , 

Apart from the fields in the excel, how are we setting the other fields of the incident ? 

Say Caller , Priority , Opened by , Contact type , Issue type etc ?

I applied the same logic and it is running successfully and creating the incident. I just want to understand how and where can i set the other remaining fields of the incident instead those covered through field mapping.