Hi Team,
We have a catalog item for Bulk Upload of hardware required for new hire. When an excel sheet containing the data is attached to the catalog, every row of the excel should convert into a new row in the MRVS. Every column of the excel will be a variable in the MRVS. I tried implementing this using data source and import set. My script is working fine but is failing in the last step. Data is populating as json and can be seen in the logs but that json is not converting to MRVS. Please assist.
I even tried doing it using GlideExcelParser but in that the working is throwing error. I need to get atleast one method working.Parse excel to feed data in MRVS - ServiceNow Community
//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()) {
gs.log("Found attachment for record: " + attach.file_name);
gs.addErrorMessage("Found attachment for record: " + attach.file_name);
} else {
gs.log("No attachment found, throwing error");
gs.addErrorMessage("No attachment found, throwing error");
throw ("No attachment found!");
}
var dataSource = new GlideRecord("sys_data_source");
dataSource.name = "New Bulk upload request" + current.sys_id;
dataSource.type = "File";
dataSource.format = "Excel";
dataSource.file_retrieval_method = "Attachment";
dataSource.import_set_table_name = "u_bulk_uploads";
dataSource.header_row = 1;
dataSource.sheet_number = 1;
var dataSourceId = dataSource.insert();
gs.log("Data source created: " + dataSourceId);
gs.log("Copying attachment from current table: " + current.getTableName() + "." + current.sys_id + " to data source");
new GlideSysAttachment().copy(current.getTableName(), current.sys_id, 'sys_data_source', dataSourceId);
gs.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
gs.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();
gs.log("Loaded records into import set number: " + importSetRec.number + " and sys id " + importSetRec.sys_id);
gs.addErrorMessage("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
gs.log("Getting rows from import set we just loaded - creating array of rows for MVRS");
var mvrsArray = [];
var importSetRow = new GlideRecord('u_bulk_uploads');
importSetRow.addQuery("sys_import_set", importSetRec.sys_id);
importSetRow.query();
while(importSetRow.next()){
gs.log("Got row from import set, creating object for MVRS row and adding it to MVRS array");
var mvrsValue = {
requested_for_employee_name: importSetRow.u_requested_fo__employee_name.getValue(),
address: importSetRow.u_address.getValue(),
shortdescription: importSetRow.u_shortdescription.getValue(),
cost_center: importSetRow.u_cost_center.getValue(),
computerproduct: importSetRow.u_computerproduct.getValue(),
quantity_computerdesktop: importSetRow.u_quantity_computerdesktop.getValue()
};
gs.log('MVRSVALUE '+mvrsValue);
gs.addErrorMessage('MVRSVALUE '+mvrsValue);
// 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.
gs.log("Got all rows, now setting the MVRS variable value on the SC item");
var data = JSON.stringify(mvrsValue);
gs.log("Testing "+data);
gs.infomessage("Testing "+data);
workflow.variables.bulk_upload_mvrs=data;// This is the line that is failing.
Regards,SM.
S.M
ServiceNow Developer
