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

Harsha1 · ‎08-18-2020

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

AbhishekGardade · ‎08-23-2020

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

Thank you,
Abhishek Gardade

View solution in original post

Ujjwala Wagh · ‎06-17-2022

Hello @Abhishek Gardade ,

Need your assistance for achieving this kind of requirement!

Thank you in advance!

Ujjwala

Harsha1 · ‎08-23-2020

Hello Abhishek,

Thank you so much for your answer.

Regards,

Harsha.

AbhishekGardade · ‎08-23-2020

Hello Harsha,

I guess you marked Willem answer as correct and not mine.

-ABhishek

Thank you,
Abhishek Gardade