Using GlideExcelParser() for bulk ordering of assets

March 17, 2020

At times there is a need to allow users to prepare data offline for later use in the system.  One such use-case comes to mind to allow a request with an attached spreadsheet to generate fulfillment tasks for multiple lines in a bulk order.

To meet this need, we turn to GlideExcelParser().

In today’s case, we will parse the contents of an attached spreadsheet from a request submitted via the portal in order to fulfill a bulk asset order consisting of different types of devices.

It is our customer’s policy to order assets quarterly in bulk to obtain a discount from their vendor.  To prevent the need to submit dozens of orders individually, we use GlideExcelParser to assist in populating fulfillment tasks within the request.

We start by submitting a request and attaching a templated spreadsheet that has been populated by our Asset manager during the quarterly inventory evaluations.   

In this spreadsheet, we define the type of devices we need, as well as the quantity.

Once submitted, the workflow takes over to determine the number of tasks required for the fulfilment of the order based on the number of rows.  Below is our workflow:

And our run script that brings the whole thing together:

[code]

//Create variable to hold attachment ID

var attachID = ”;

 

//Get excel sheet attachment sys_id

var gr = new GlideRecord(‘sys_attachment’);

gr.addQuery(‘table_sys_id’,current.request.getUniqueValue());

gr.addQuery(‘file_name’,’AssetBulkUpdate.xlsx’);

gr.query();

 

if (gr.next()){

                                attachID = gr.getUniqueValue();

}

 

//Begin to parse the excel sheet for relevant data

 

//Initialize the GlideSysAttachment call for retrieval of our sheet

var attachment = new GlideSysAttachment();

var attachmentStream = attachment.getContentStream(‘f807bbbedbd700102f4c7b603996196e’);

 

var parser = new sn_impex.GlideExcelParser();

 

parser.parse(attachmentStream);

 

var headers = parser.getColumnHeaders();

 

var header1 = headers[0];

var header2 = headers[1];

 

//gs.print(‘header1: ‘ + header1);

//gs.print(‘header2: ‘ + header2);

 

while (parser.next()) {

var desc = ”;

var row = parser.getRow();

 

for (var x in row){

if (JSUtil.notNil(row))

desc += ‘Bulk Asset Order for ‘+x+’: ‘+row[x]+’ ‘;

desc.trim();

}

var scTask = new GlideRecord(‘sc_task’);

                scTask.initialize();

                scTask.request_item = current.getUniqueValue();

                scTask.short_description = desc;

                scTask.insert();

}

[/code]

We can see that upon submission of the request, the workflow takes over and creates a catalog task for each row in the sheet outlining the device type to order and how many are needed.

Thanks to GlideExcelParser() we have taken a tedious task of repeat order entry and condensed it into a single simple submission!

Screenshots