Hello ServiceNow Geeks,
Here again with an interesting use case where we will be going through the parsing of data from excel sheet & using that data with our ease.
I am taking some use case on my own, please don't mind. Hope you will focus on the logic, HAHAHA
Lets hear the UseCase -
We have a catalog form which is used by onboarding team to submit a request to manager for the onboarding approval. The 1st level of team has received the data in the excel sheet(template format below)
You have an excel sheet template where few predefine fields are there, once it is attached to catalog item & submitted from the onboarding team. After the manger approval & CTASK close email should trigger with an email template to the mentioned users from the email.
The above one is just a use case but once you get the the data from the excel sheet you can use it in your way. I hope it will help you to automate your onboarding process, updating the manager automatically for CI updates & many more. It depends how you take this forward from here.....
Let's Start...!
1) Take an excel sheet with fields name as NAME, EMAIL, ONBOARDING DATE etc. (We can add as many required field)
2) Creating approval for the manager & SC Task in the Workflow.
3) Creating notification template.
4) Creating BR
5) Email script
Template sample which will be attached
Filled the Template & going to submit the request
As we have filled the data in template submitted the request, now lets go deep how we can get the data & send the custom email to the joiners filed in the template(After approval only it should get trigger).
Create a BR - It will help us to parse the excel sheet & get the useful data, Will explain while doing the code.
Table - sc_task
Filter condition - To trigger at the particular condition.
Lets Talk about - How code is working:
(function executeRule(current, previous /*null when async*/ ) {
gs.info("tushar@21 current -" + current.request_item);
var gratt = new GlideRecord('sys_attachment');
gratt.addQuery('table_sys_id', current.request_item); //checking the sys_id of RITM & the attachment table
gratt.query();
var a = gratt.getRowCount();
gs.info("tushar@21 Count - " + a); //count should be always 1, if 0 it didn't get the atachment file
if (gratt.next()) {
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(gratt.sys_id);
parser.setSheetNumber(0);
parser.setNullToEmpty(true);
parser.parse(attachmentStream);
//retrieve the column headers
var headers = parser.getColumnHeaders();
var header1 = headers[0]; //header NAME
var header2 = headers[1]; //header EMAIL
var header3 = headers[2]; //ONBOARDING TEAM
var header4 = headers[3]; //TEAM
// var header4 = headers[3]; //according to the fileds of excel you can add the header over hear
//print headers
var arr = [];
var table = "";
while (parser.next()) {
var row = parser.getRow();
if (row[header1].toString() != '' || row[header2].toString() != '') {
//table - Sending the full table structure as an html code
table= '<p><table style="border-collapse: collapse; width: 99.5294%; height: 87px;" border="1"><tbody><tr style="background-color: #8c03fc;"><td style="width: 12.7358%; text-align: center; height: 29px;"><span style="font-size: 10pt; font-family: Times; color: #ffffff;"><strong>NAME</strong> </span></td><td style="width: 12.7358%; text-align: center; height: 29px;"><span style="font-size: 10pt; font-family: Times; color: #ffffff;"><strong>EMAIL</strong></span></td><td style="width: 10.1415%; text-align: center; height: 29px;"><span style="font-size: 10pt; font-family: Times; color: #ffffff;"><strong>ONBOARDING</strong> </span></td><td style="width: 10.1415%; text-align: center; height: 29px;"><span style="font-size: 10pt; font-family: Times; color: #ffffff;"><strong>TEAM</strong> </span></td></tr><tr style="height: 58px; background-color: #f3edf7;"><td style="width: 12.7358%; height: 58px; text-align: center;"><br /><span style="font-family: Times; font-size: 10pt;">' + row[header1].toString() + '</span></td><td style="width: 12.7358%; height: 58px; text-align: center;"><br /><span style="font-family: Times; font-size: 10pt;">' + row[header2].toString() + '</span></td><td style="width: 10.1415%; height: 58px; text-align: center;"><br /><span style="font-family: Times; font-size: 10pt;">' + row[header3].toString() + '</span></td><td style="width: 10.1415%; height: 58px; text-align: center;"><br /><span style="font-family: Times; font-size: 10pt;">' + row[header4].toString() + '</span></td></tr></tbody></table></p>';
var sendDetails = {}; //JSON object
sendDetails.bodyTab = table;
sendDetails.RecNAME = row[header1].toString();
sendDetails.Email = row[header2].toString();
var Details = JSON.stringify(sendDetails);
gs.eventQueue('email_trigger_automatic', current, Details, row[header2].toString()); //P1 - the details which will be send to the email script, P2 - The receiver email
} else {
break;
}
}
}
})(current, previous);Ignore my Comments in the code
Create Email Script:
Email script is use to make the emails dynamic , so here one by one data will come from BR & help us to receive the email dynamically.
(function runMailScript(current, template, email, email_action, event) {
var data = JSON.parse(event.parm1);
var bodyEmail = data.bodyTab; //body of the email
var UserName = data.RecNAME; //Rec email
gs.info("tushar@21 user name is " + UserName);
var EMail = data.Email;
email.subject = "Status of onboarding || " + new GlideDate(); //Custome subject
email.addAddress("cc", "tushar@gmail.com", "Choudhary, Tushar"); //If we want to keep anyone in the CC
template.print("Hello " + UserName + ",<br>\n" + "<br>\n"); //content for the email
template.print("Please find the onboarding date & the Team Name below." + "<br>\n" + "<br>\n" + bodyEmail + "<br>\n" + "Wish you all the best:)" +"<br>\n" + "DISCLAIMER: Please note this is an auto generated email.");
})(current, template, email, email_action, event);Create Event Registration -
It will trigger the notification once event will trigger.
Create Email Notification -
Parameter 2 - Rec email
HTML Syntax has been given for email script & We can set Reply to for the automated mail.
${mail_script:Auto_email_trigger_excel}
Regards
Team Onboarding
Workflow will be added with the Approval activity & Task creation activity.
Lets proceed towards the testing :
I have created a catalog item with one field, according to req's the fields can be added, The excel sheet template has been attached in the catalog item only(below are the steps).
The attachment has been attached to catalog item & the insert button is used to make the link for downloading the attachment from the link:
Once I have submitted the request with template details , It will go for approval -> catalog task will be generated -> Once Task will be "closed complete" - Email will trigger like below format
Output Email template
If the above article helps you, Please Mark Helpful & give your valuable comments for any improvement.
Cheers...!
Happy Learning
Tushar
