Creating a Service Request: Using Service Catalog Script API with Inbound Emails and Attachment.

[AbhishekGardade]

Before We Start, make sure you have gone through below docs-

• Understand Inbound Email Actions,
• Understand Service Catalog Script API. Also, you can look at the Script Include "Cart" on your dev instance
• Understand Attachment API’s.

Example: Email based User account Creation.

I've already created a Catalog Item with 5 variables: First Name, Last Name, Email, Company, Reporting Manager

We have Catalog Item for a User Creation, ServiceNow will receive an Email with attachment which contains details of User to be created from a Specific Email ID.

With the help of Inbound Action, we will fetch the content of Attachment, set to the variables and create a submit a request.

Inbound Action:

Name:   Email Based User Creation.

Target Table:   Requested Item [sc_req_item]

Type: New

Condition: email.recipients.toLowerCase().indexOf("xxxxxxxxx@company.com") > -1   (checks to ensure if its coming from given email address)

Script:

(function runAction(/*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {
         var managerID='';
         var obj = {};
                 // Fetching Contents from Excel File:
                 var grAttachment ='';
                 var gr = new GlideRecord('sys_email');
                 gr.addQuery('uid', email.uid); //queries one record from email log
                 gr.query();
                 if(gr.next()){
                          grAttachment = new GlideRecord('sys_attachment');
                          grAttachment.addQuery('table_sys_id',gr.getValue('sys_id'));
                          grAttachment.orderByDesc('sys_created_on');
                          grAttachment.query();
                          if(grAttachment.next()){
                                   var parser = new sn_impex.GlideExcelParser();
                                   var attachment = new GlideSysAttachment();
                                   // use attachment sys id of an excel file
                                   var attachmentStream = attachment.getContentStream(grAttachment.getValue('sys_id'));
                                   parser.parse(attachmentStream);
                                   //retrieve the column headers
                                   var headers = parser.getColumnHeaders();
                                   var key = headers[0];
                                   var value = headers[1];
                                   while(parser.next()) {
                                            var row = parser.getRow();
                                            //gs.log(row[key] + ': ' + row[value]) ; //print row value for both columns
                                            obj[row[key]] = row[value];
                                   }
                                   // Creating JSON FORMATTED OBJECT :
                                   var jSon = JSON.stringify(obj);
                                   // parsing JSON OBJECT :
                                   var parseJson = JSON.parse(jSon);  
                                   // finding sys id of reporting manager
                                   var grUser = new GlideRecord('sys_user');
                                   grUser.addQuery('name',parseJson.Reporting_Manager);
                                   grUser.query();
                                   if(grUser.next()){
                                            managerID =      grUser.getValue('sys_id');
                                   }
                                   // Creating Request, Requested Item and setting variables
                                   var cartId = GlideGuid.generate(null);
                                   var cart = new Cart(cartId); //calling the cart API
                                   var item = cart.addItem('53ae7ab21b633300364d32a3cc4bcba1'); //sys_id of the catalog item
                                   cart.setVariable(item, 'first_name', parseJson.First_Name); //sets catalog variable to the email's subject
                                   cart.setVariable(item, 'last_name', parseJson.Last_Name); //sets catalog variable to email's body
                                   cart.setVariable(item, 'email', parseJson.Email); //sets catalog variable to the email's subject
                                   cart.setVariable(item, 'reporting_manager', grUser.getValue('sys_id'));
                                   var req = cart.placeOrder(); //this submits a form/request and creates a request object.   req = the request object

                                   //we're passing the sys_id of the request so we know what RITM to grab.
                                   var grRITM = new GlideRecord('sc_req_item');
                                   grRITM.addQuery('request',req.getValue('sys_id'));
                                   grRITM.query();
                                   if(grRITM.next()) {

                                            // here you can update fields of RITM created.
                                            grRITM.description = email.body_text;
                                            grRITM.assignment_group ='0a52d3dcd7011200f2d224837e6103f2';
                                            grRITM.update();

                                            // Updating Target Field ON Emails table.
                                            gr.target_table = "sc_req_item";
                                            gr.instance =  grRITM.getValue('sys_id');
                                            gr.update();
                                   }
                          }
                 }               

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

Important Information:

• if you want to modify RITM post insert, use a nested function where-ever you're calling the Cart API
• Make sure your inbound action(s) runs before your Incident creation inbound action.
• Use stop processing once the script is complete. So no other inbound action will run after this.
• If the email source is automated/fixed then explore email.body.variables...  from body especially.
• Updating Target field on Emails table:

1. You will find that when using only "Cart.placeOrder();" in an inbound action it will return with a "skipped" status for Email Logs. When the cart API is called it starts the workflow process for requesting an item. This workflow process is outside of the inbound action where it creates the request, request item and tasks etc.
2. Inbound actions are recognized as "processed" if there has been a change to the "current" object in the target table. The inbound action doesn't recognize the request and thinks that there has been no update to the target table. This then returns the log entry: "Skipping '<inbound action name>', did not create or update <target table>".
3. If you add "current.update()" to the inbound action, it will change the status of the inbound action to "processed" but it will create an additional record in the target table. This is because current.update() creates a new record if it is unable to find a record to update.
4. You can set target field with script as below:

     gr.target_table = "table_name" 
     gr.instance =  “sys_id of Record”;
     gr.update();                          

References and links:

• https://docs.servicenow.com/bundle/london-application-development/page/script/server-scripting/refer...
• https://community.servicenow.com/community?id=community_article&sys_id=9ed305e2db63fb845129a851ca961...
• https://community.servicenow.com/community?id=community_blog&sys_id=c25da629dbd0dbc01dcaf3231f961933

Thanks for reading and do comment with your suggestion/improvements if any.

Hope you will find it as helpful. Don’t forget to Mark it Helpful, Bookmark.
Thanks,
Abhishek Gardade

[Vishal Khandve]

Great Article...

[AbhishekGardade]

Thank Vishal...

[Priyanka Patil2]

Nice post!!!

[AbhishekGardade]

Thanks...

[Pranav Bhagat]

Awesome Abhishek...

[AbhishekGardade]

Thanks Pranav for checking out the article.

[Swati Mishra]

Hi Abhishek,

I am a beginner in the ServiceNow platform and have a doubt in the above code, hope you can help me with this because I need to implement a similar requirement for my instance.

In this part of the code:

cart.setVariable(item, 'first_name', parseJson.First_Name); //sets catalog variable to the email's subject

'first_name' is the field id of my variable, and First_Name is the corresponding column in Excel sheet.

Is this correct?

Regards,

Swati Mishra