In this article we will see how to use scripting in a workflow to import a spreadsheet into a Service Catalog item's Multi Row Variable Set (MVRS). We're assuming you are familiar with the workflow editor, javascript, import sets, and the service catalog.
Before we go any further, here it is in action:
First, create a Service Catalog item that has an MVRS with 2 columns. You should then be able to order the item and see something like this after entering a few values in the MVRS:
Now, this is great, but we want to allow a user to attach a spreadsheet to the SC request and have ServiceNow automagically import the spreadsheet and create the MVRS rows.
Let's look at the spreadsheet - note that it has a column called "First" and "Second" corresponding to the MVRS column values:
The only way I could come up with to do this is to load the spreadsheet into ServiceNow after the request is submitted and update the MVRS then. To do that, we need to create a workflow with a "Run Script" step that will do the work. Here's what the workflow looks like:
Don't forget to set the "Workflow" on the catalog item to be this workflow. Do that and we'll fill out the script to actually import the spreadsheet:
Now - let's get to the script. It needs to do several things to make this work:
- Check for attachment - Query the sys_attachment table to see if current.sys_id has an attachment there
var attach = new GlideRecord('sys_attachment');
attach.addQuery('table_sys_id', current.sys_id);
attach.query();
if (attach.next()) {
log("Found attachment for record: " + attach.file_name);
} else {
log("No attachment found, throwing error");
throw ("No attachment found!");
}
- Create data source from attachment - A data source is how ServiceNow imports data into an Import Set
var dataSource = new GlideRecord(dataSourceTable);
dataSource.name = "mikeski.net-excel-mvrs-" + current.sys_id;
dataSource.type = "File";
dataSource.format = "Excel";
dataSource.file_retrieval_method = "Attachment";
dataSource.import_set_table_name = importSetTableName;
dataSource.header_row = 1;
dataSource.sheet_number = 1;
dataSource.sys_package.setDisplayValue(applicationScope);
dataSource.sys_scope.setDisplayValue(applicationScope);
var dataSourceId = dataSource.insert();
log("Data source created: " + dataSourceId);
- Copy attachment to data source - A data source with an excel attachment type expects an excel attachment to import from
log("Copying attachment from current table: " + current.getTableName() + "." + current.sys_id + " to data source");
new GlideSysAttachment().copy(current.getTableName(), current.sys_id, dataSourceTable, dataSourceId);
log("Done copying attachment, data source is now setup");
- Create import set from data source - Read the spreadsheet and insert the rows into an import set
log("Importing data from data source into import set");
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(dataSource);
var ranload = loader.loadImportSetTable(importSetRec, dataSource);
importSetRec.state = "loaded";
importSetRec.update();
log("Loaded records into import set number: " + importSetRec.number + " and sys id " + importSetRec.sys_id);
- Read import set table - We do not use a transform for this, rather we read the import set and build our data while looping over the rows
log("Getting rows from import set we just loaded - creating array of rows for MVRS");
var mvrsArray = [];
var importSetRow = new GlideRecord(importSetTableName);
importSetRow.addQuery("sys_import_set", importSetRec.sys_id);
importSetRow.query();
while(importSetRow.next()){
log("Got row from import set, creating object for MVRS row and adding it to MVRS array");
var mvrsValue = {
first_value: importSetRow.u_first.getValue(),
second_value: importSetRow.u_second.getValue()
};
mvrsArray.push(mvrsValue);
}
- Create MVRS data array - MVRS records are stored as an array of Javascript objects - each object is a row and the array is the table. Therefore, we generate an array of objects and JSON.stringify it to set the MVRS data on the SC item.
log("Got all rows, now setting the MVRS variable value on the SC item");
current.variables.mikeski_net_demo_2_field_mvrs = JSON.stringify(mvrsArray);
Once that's done, we have our spreadsheet data imported into our record.
https://gist.github.com/mikebski/ebafc0cc0dc41eaca8399f107407cd8c
I have figured out what you need to change in this script to get it to work in your own context.
on line 60/61 you need to change the existing values and add more should you need to:
first_value: importSetRow.u_first.getValue(),
second_value: importSetRow.u_second.getValue()
"first_value" is the reference to the first variable in the multi row variable set in Mike's own example - you need to change this to the name of your first variable in your multi row variable set and the same for the second and so on here is my example, using it for importing firewall rules from a spreadsheet:
Variable set variables:
Workflow script edit:
source: importSetRow.u_source.getValue(),
destination: importSetRow.u_destination.getValue(),
service_application: importSetRow.u_service_application.getValue(),
protocol: importSetRow.u_protocol.getValue(),
port: importSetRow.u_port.getValue()
Notice also that you need to change importSetRow variable to match the fields created in the import set that is generated. In my case my import set named all the fields starting with "u_":
XML of import set:
<u_destination>192.168.1.1</u_destination>
<u_port>443</u_port>
<u_protocol>tcp</u_protocol>
<u_service_application>https</u_service_application>
<u_source>192.168.1.1</u_source>
the last variable that needs to be changed is on line 66:
current.variables.mikeski_net_demo_2_field_mvrs = JSON.stringify(mvrsArray);
This is simply the internal name of your multi row variable set. mine is:
current.variables.fw_rules = JSON.stringify(mvrsArray);
Once these changes are made, your script should work fine. I must stress that you do not need to run the transform for this import set. As Mike stated, we are just using import sets to generate the fields which we can pull the data from and into the multi row variable set. You might want to consider deleting the import set after the script has imported the data, otherwise there will be many import sets in your table over time.
