How to read in values from an excel, manipulate this info and then put it into a MRVS

[gunishi]

Hi all, 

I have the following code in a runscript activity in a workflow. I am trying to populate different RITMs from the information in the excel spreadsheet that is attached to a catalog item.

I have the following code in a runscript activity to extract the infrormation from the spreadsheet. I want to be able to take the asset tags in the spreadsheet and then seach the server table with these to then return the corresponing servers to fill in my RITMs. I really don't think this is correct though. Brad Bowman was kind enough to provide a template, but I would appreciate a sanity check to make sure I am doing this correctly. 

var attachment = new GlideRecord('sys_attachment');

attachment.addQuery('table_name', 'sc_cart_item');

attachment.addQuery('table_sys_id', current.sys_id);

attachment.query();

if (attachment.next()) {

var attachmentTwo = new GlideSysAttachment();

var attachmentStream = attachmentTwo.getContentStream(attachment.sys_id);

var parser = new sn_impex.GlideExcelParser();

parser.parse(attachmentStream);

var mrvs = current.variables.order_servers;

var headers = parser.getColumnHeaders();

var assetTagCol = headers[0];

var assetTags = [];

while (parser.next()) {

var row = parser.getRow();

newRow = mrvs.addRow();

newRow.asset_tags = row[assetTagCol];

assetTags.push(row[assetTagCol]);

}

var assetTagsStr = assetTags.toString();

}

var gr = new GlideRecord('cmdb_ci_server');

gr.addEncodedQuery("asset_tagIN" + assetTagsStr);

gr.query();

var servers = [];

while (gr.next()) {

servers.push(gr.getDisplayValue('name'));

}

//section below unsure about, is this now creating double the rows in the MRVS?

for (var i = 0; i < servers.length; i++) {

newRow = mrvs.addRow();

newRow.asset_tags = row[assetTagCol];

}

I then have this second piece of code in a separate run script activity which should create new RITMs and populate with the relevant information:

var ritmsysid = '';

var mrvs = current.variables.order_servers; //internal name of MRVS

var rowCount = mrvs.getRowCount();

var asset_tag = '7c60a75d972571104a02bcb3f153af9e';

var server = 'ca9755f0972531104a02bcb3f153af51';

//autofill the desired variable on the one, existing RITM

var row = mrvs.getRow(0);

current.variables.asset_tags = row.asset_tags;

current.variables.server = row.server;

//if there is more than one row in the MRVS, create a new RITM under the same REQ for all of the other rows

if (rowCount > 1) {

for (var i = 1; i < rowCount; i++) {

row = mrvs.getRow(i);

var ritm = new GlideRecord('sc_req_item');

ritm.initialize();

ritm.request = current.request;

ritm.cat_item = current.cat_item;

ritm.opened_by = current.opened_by;

ritm.short_description = current.short_description;

//Add variables to the new RITMs

addVariable(sysID, asset_tags, row.asset_tag,1); //the same variable autofilled above

addVariable(sysID, server, current.variables.server,2); //one line for every other variable to be populated on the new RITM }

//Start the workflow on all the new RITMs

startWf(current.request);

}

function addVariable(ritm,varID,value,order) {

var variable = new GlideRecord('sc_item_option');

variable.initialize();

variable.item_option_new = varID;

variable.value = value;

variable.order = order;

var sysID = variable.insert();

var itemm2m = new GlideRecord('sc_item_option_mtom');

itemm2m.initialize();

itemm2m.request_item = ritm;

itemm2m.sc_item_option = sysID;

itemm2m.insert();

}

function startWf(request) {

var ritm = new GlideRecord('sc_req_item');

ritm.addQuery('request',request);

ritm.addQuery('sys_id','!=',current.sys_id);

ritm.addNullQuery('context');

ritm.query();

while (ritm.next()) {

ritm.setForceUpdate(true);

ritm.update();

}

}

Any help/comments/feedback/corrections would be much much much appreciated as I am very new to servicenow and I think the complexity of this code is inhibiting understanding. 

Thank you, 

G