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