Read Excel File Using Script

Pradyumna Das
Tera Expert

on ‎07-27-2023 11:09 AM

We can read Excel from a server-side script using the below code.

 

Example: Suppose I have one Excel file which has 3 field Employee ID, LAST NAME, and FIRST NAME. I wanted to retrieve all the data from Excel and wanted to store it in any table or wanted to show it in MRVS. 

 

Please use or leverage the below code based on your needs.

function prepareData(row) {
    myData = {};
   //Update the below variables based on your Excel column name and need
    myData.employee_id = row['Employee ID'];
    myData.last_name = row['LAST NAME'];
    myData.first_name = row['FIRST NAME'];
    return myData;
}
var myobjArray = [];
var attachmentSID = '{SYS_ID}'; // pass your excel attachment sys_id
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();

// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(attachmentSID);
parser.parse(attachmentStream);
while (parser.next()) {
    var row = parser.getRow();
    myobjArray.push(prepareData(row));
}
gs.info(JSON.stringify(myobjArray));

 

Please mark this article help full if it helps you in any manner.

Thanks,

Pradyumna Das

  • 4,791 Views
Comments
sailesh1
Tera Contributor
‎07-29-2023 02:05 PM

@Pradyumna Das : This is awesome. I would like to know how we can select the data from a specific sheet. In my case if I would like to extract the data from sheet 2 in the Excel file, how can we achieve it?

0 Helpfuls
sailesh1
Tera Contributor
‎07-29-2023 02:08 PM

Looks like I found it. 
How To Parse Excel Sheets In ServiceNow? - ServiceNow Spectaculars

If anyone is looking for the same, please access the link above along with solution in this community post by @Pradyumna Das 

Edxavier Robert
Mega Sage
‎10-25-2024 12:20 PM

Hi @Pradyumna Das , 

 

I saw you video and is excellent. I have a question regarding this, in my use case I have mrvs with two questions: 

equipment and asset tag. Under equipment I have five options and for each equipment user will need to input the asset tag. How can I create the excel file headers or adjust your code to handle this? I tried adding multiple columns with header equipment and asset tag, but didn't work. 

In my case I have catalog item to just add the excel file, once is submitted the flow (custom action)  will call a script included called ExcelToCart. Here is the code that I have so far: 

 

var ExcelToCart = Class.create();
ExcelToCart.prototype = {
    initialize: function() {},

    processExcelAndCreateCart: function(attachmentSysId, catalogItemSysId) {
        try {
            var parser = new sn_impex.GlideExcelParser();
            var attachment = new GlideSysAttachment();
            var attachmentStream = attachment.getContentStream(attachmentSysId);

            if (!attachmentStream) {
                gs.error("Attachment not found or unable to read stream for sys_id: " + attachmentSysId);
                return;
            }

            parser.setSource(attachmentStream);
            if (parser.parse()) {
                var headers = parser.getColumnHeaders();

                while (parser.next()) {
                    var row = parser.getRow();
                    var cartId = GlideGuid.generate(null);
                    var cart = new Cart(cartId);

                    // Add the catalog item to the cart
                    var item = cart.addItem(catalogItemSysId);

                    // Handle req_for_employee_name field
                    var requestedForUserId = row.requested_for;
                    var userGr = new GlideRecord('sys_user');
                    userGr.addQuery('user_name', requestedForUserId);
                    userGr.query();
                    if (userGr.next()) {
                        gs.info("User found: " + requestedForUserId + ", sys_id: " + userGr.sys_id);
                        cart.setVariable(item, 'req_for_employee_name', userGr.sys_id);
                    } else {
                        gs.warn("User not found for userId: " + requestedForUserId);
                        continue; // Skip this row if the user is not found
                    }

                    // Handle state field
                    var stateuser = row.state;
                    var userState = new GlideRecord('sys_report_map');
                    userState.addQuery('parent', '6ee51951d7320200bd4a4ebfae610354');
                    userState.addQuery('name', stateuser);
                    userState.query();
                    if (userState.next()) {
                        gs.info("User State is: " + stateuser + " - " + userState.sys_id);
                        cart.setVariable(item, 'state', userState.sys_id);
                    }

                    // Set other variables from the row data
                    cart.setVariable(item, 'zip', row.zip);
                    cart.setVariable(item, 'do_you_want_the_label_mailed_or_shipped', row.do_you_want_the_label_mailed_or_shipped);
                    cart.setVariable(item, 'any_additional_information', row.any_additional_information);
                    cart.setVariable(item, 'city', row.city);
                    cart.setVariable(item, 'address_1', row.address_1);
                    cart.setVariable(item, 'do_you_need_boxes', row.do_you_need_boxes);
                    cart.setVariable(item, 'address_2', row.address_2);
                    cart.setVariable(item, 'which_organization', row.which_organization);
                    cart.setVariable(item, 'person_address', row.person_address);
                    cart.setVariable(item, 'email_address', row.email_address);
                    cart.setVariable(item, 'phone_number', row.phone_number);

                    // Create sc_item_option records
                    this.createItemOptions(item, row, catalogItemSysId);

                    // Place the order
                    var rc = cart.placeOrder();
                    gs.info("Request created: " + rc.number);

                    // Get the sys_id of the newly created Request
                    var requestSysId = rc.sys_id;

					gs.info("Request SysID " + requestSysId + " RC: " +rc.sys_id);

                    // Check if requestSysId is defined
                    if (!requestSysId) {
                        gs.error("Error: requestSysId is not defined for the created request.");
                        continue; // Skip further processing if requestSysId is not available
                    }

                    // Update the Request's requested_for and opened_by fields
                    this.updateRequestAndRITM(requestSysId, userGr.sys_id);

					// Handle MRVS for 'please_add_all_the_equipment_s_you_are_returning_with_asset_tags_serial_numbers'
                    this.createMRVS(item, row.equipment, row.asset_tags_serial_number_of_the_equipment, catalogItemSysId, requestSysId);
                }
            } else {
                gs.error("Error parsing Excel: " + parser.getErrorMessage());
            }
        } catch (ex) {
            gs.error("Exception caught while processing Excel: " + ex.message);
        }
    },
//Handle the MRVS and the variables on RITM
createMRVS: function(item, equipment, assetTag, catalogItemSysId, requestSysId) {

	// MRVS Data
    var mrvsData = [];
    if (equipment && assetTag) {
        mrvsData.push({
            "equipment": equipment,
            "asset_tags_serial_number_of_the_equipment": assetTag
        });
    } else {
        gs.warn("Missing equipment or asset tag data.");
        return;
    }

    // Fetch the RITM based on the requestSysId from updateRequestAndRITM
    var ritmGr = new GlideRecord('sc_req_item');
    ritmGr.addQuery('request', requestSysId);
    ritmGr.query();
    if (!ritmGr.next()) {
        gs.warn("No RITM found for the given request: " + requestSysId);
        return;
    }

    // Get variable set and options
    var varSet = '1b88b6421b0802907a7864a8624bcb22'; // Variable set Sys ID - Please add all the equipment(s) you are returning with asset tags/serial numbers
    var equipVar = '8a522246c3911210c662b7ddd40131e8'; // Equipment variable Sys ID Equipment Variable
    var assetVar = '58eab6c21b0802907a7864a8624bcbf4'; // Asset tag variable Sys ID Asset tags/serial number of the equipment

    // Create sc_item_option for "equipment" (once for all rows)
    var itemOption = new GlideRecord('sc_item_option');
    itemOption.initialize();
    itemOption.setValue('request_item', ritmGr.sys_id); // Set RITM
    itemOption.setValue('item_option_new', equipVar); // Associate with "equipment" variable
    itemOption.setValue('value', mrvsData[0].equipment); // Store the first equipment value
    var equipmentOptionSysId = itemOption.insert(); // Insert record and reuse this Sys ID for all rows

    // Create sc_item_option for "asset_tags_serial_number_of_the_equipment" (once for all rows)
    itemOption.initialize();
    itemOption.setValue('request_item', ritmGr.sys_id); // Set RITM
    itemOption.setValue('item_option_new', assetVar); // Associate with "asset tag" variable
    itemOption.setValue('value', mrvsData[0].asset_tags_serial_number_of_the_equipment); // Store the first asset tag value
    var assetOptionSysId = itemOption.insert(); // Insert record and reuse this Sys ID for all rows

    // Process each row for MRVS
    var rowIndex = 0;
    mrvsData.forEach(function(row) {
        // Create MRVS record for equipment
        var mrvsRecord = new GlideRecord('sc_multi_row_question_answer');
        mrvsRecord.initialize();
        mrvsRecord.setValue('variable_set', varSet);
        mrvsRecord.setValue('parent_table_name', 'sc_req_item');
        mrvsRecord.setValue('parent_id', ritmGr.sys_id); // Use fetched RITM Sys ID
        mrvsRecord.setValue('item_option_new', equipVar); // Set "equipment" variable
        mrvsRecord.setValue('value', row.equipment);
        mrvsRecord.setValue('sc_item_option', equipmentOptionSysId); // Reuse sc_item_option for equipment
        mrvsRecord.setValue('row_index', rowIndex); // Associate with correct row index
        mrvsRecord.insert();

        // Create MRVS record for asset tag
        mrvsRecord.initialize();
        mrvsRecord.setValue('variable_set', varSet);
        mrvsRecord.setValue('parent_table_name', 'sc_req_item');
        mrvsRecord.setValue('parent_id', ritmGr.sys_id); // Use fetched RITM Sys ID
        mrvsRecord.setValue('item_option_new', assetVar); // Set "asset tag" variable
        mrvsRecord.setValue('value', row.asset_tags_serial_number_of_the_equipment);
        mrvsRecord.setValue('sc_item_option', assetOptionSysId); // Reuse sc_item_option for asset tag
        mrvsRecord.setValue('row_index', rowIndex); // Associate with correct row index
        mrvsRecord.insert();

        // Increment rowIndex for each row
        rowIndex++;
    });

    gs.info("MRVS records created successfully.");
},

// end of code

    updateRequestAndRITM: function(requestSysId, requestedForSysId) {
		gs.log("Inside updateRequestAndRITM: " + requestSysId + "Another: "+ requestedForSysId);
        var requestGr = new GlideRecord('sc_request');
        if (requestGr.get(requestSysId)) {
            requestGr.setValue('requested_for', requestedForSysId);
            requestGr.setValue('opened_by', requestedForSysId);
            requestGr.update();
            gs.info("Request updated with requested_for: " + requestedForSysId + ", opened_by: " + requestedForSysId);
        } else {
            gs.warn("Request not found: " + requestSysId);
        }

        var ritmGr = new GlideRecord('sc_req_item');
        ritmGr.addQuery('request', requestSysId);
        ritmGr.query();
        while (ritmGr.next()) {
            ritmGr.setValue('requested_for', requestedForSysId);
            ritmGr.setValue('opened_by', requestedForSysId);
            ritmGr.update();
            gs.info("RITM updated: " + ritmGr.sys_id);
        }
    },

    type: 'ExcelToCart'
};
0 Helpfuls
Edxavier Robert
Mega Sage
‎10-25-2024 01:04 PM

@Pradyumna Das 

 

I was able to fix my issue by adding the loop, also I need to adjust my column headers to pair with each column. Here is the final code just in case someone has a similar case. 

var ExcelToCart = Class.create();
ExcelToCart.prototype = {
    initialize: function() {},

    processExcelAndCreateCart: function(attachmentSysId, catalogItemSysId) {
        try {
            var parser = new sn_impex.GlideExcelParser();
            var attachment = new GlideSysAttachment();
            var attachmentStream = attachment.getContentStream(attachmentSysId);

            if (!attachmentStream) {
                gs.error("Attachment not found or unable to read stream for sys_id: " + attachmentSysId);
                return;
            }

            parser.setSource(attachmentStream);
            if (parser.parse()) {
                var headers = parser.getColumnHeaders();

                while (parser.next()) {
                    var row = parser.getRow();
                
                    // Prepare equipment array for MRVS
                    var equipmentArray = [];
                    for (var i = 1; i <= 2; i++) {
                        var equipment = row['equipment_' + i];
                        var assetTag = row['asset_tags_serial_number_of_the_equipment_' + i];
                        if (equipment || assetTag) {
                            equipmentArray.push({
                                "equipment": equipment,
                                "asset_tags_serial_number_of_the_equipment": assetTag
                            });
                        }
                    }

                    var cartId = GlideGuid.generate(null);
                    var cart = new Cart(cartId);
                    var item = cart.addItem(catalogItemSysId);

                    // Set variables
                    var requestedForUserId = row.requested_for;
                    var userGr = new GlideRecord('sys_user');
                    userGr.addQuery('user_name', requestedForUserId);
                    userGr.query();
                    if (userGr.next()) {
                        gs.info("User found: " + requestedForUserId + ", sys_id: " + userGr.sys_id);
                        cart.setVariable(item, 'req_for_employee_name', userGr.sys_id);
                    } else {
                        gs.warn("User not found for userId: " + requestedForUserId);
                        continue;
                    }

                    var stateuser = row.state;
                    var userState = new GlideRecord('sys_report_map');
                    userState.addQuery('parent', '6ee51951d7320200bd4a4ebfae610354');
                    userState.addQuery('name', stateuser);
                    userState.query();
                    if (userState.next()) {
                        gs.info("User State is: " + stateuser + " - " + userState.sys_id);
                        cart.setVariable(item, 'state', userState.sys_id);
                    }

                    cart.setVariable(item, 'zip', row.zip);
                    cart.setVariable(item, 'do_you_want_the_label_mailed_or_shipped', row.do_you_want_the_label_mailed_or_shipped);
                    cart.setVariable(item, 'any_additional_information', row.any_additional_information);
                    cart.setVariable(item, 'city', row.city);
                    cart.setVariable(item, 'address_1', row.address_1);
                    cart.setVariable(item, 'do_you_need_boxes', row.do_you_need_boxes);
                    cart.setVariable(item, 'address_2', row.address_2);
                    cart.setVariable(item, 'which_organization', row.which_organization);
                    cart.setVariable(item, 'person_address', row.person_address);
                    cart.setVariable(item, 'email_address', row.email_address);
                    cart.setVariable(item, 'phone_number', row.phone_number);

                    this.createItemOptions(item, row, catalogItemSysId);

                    var rc = cart.placeOrder();
                    gs.info("Request created: " + rc.number);

                    var requestSysId = rc.sys_id;
                    if (!requestSysId) {
                        gs.error("Error: requestSysId is not defined for the created request.");
                        continue;
                    }

                    this.updateRequestAndRITM(requestSysId, userGr.sys_id);

                    // Pass the equipment array to createMRVS
                    this.createMRVS(item, equipmentArray, catalogItemSysId, requestSysId);
                }
            } else {
                gs.error("Error parsing Excel: " + parser.getErrorMessage());
            }
        } catch (ex) {
            gs.error("Exception caught while processing Excel: " + ex.message);
        }
    },

    createMRVS: function(item, equipmentArray, catalogItemSysId, requestSysId) {
        var varSet = '1b88b6421b0802907a7864a8624bcb22'; // Variable set Sys ID
        var equipVar = '8a522246c3911210c662b7ddd40131e8'; // Equipment variable Sys ID
        var assetVar = '58eab6c21b0802907a7864a8624bcbf4'; // Asset tag variable Sys ID

        var ritmGr = new GlideRecord('sc_req_item');
        ritmGr.addQuery('request', requestSysId);
        ritmGr.query();
        if (!ritmGr.next()) {
            gs.warn("No RITM found for the given request: " + requestSysId);
            return;
        }

        var itemOption = new GlideRecord('sc_item_option');
        itemOption.initialize();
        itemOption.setValue('request_item', ritmGr.sys_id);
        itemOption.setValue('item_option_new', equipVar);
        itemOption.setValue('value', equipmentArray[0].equipment);
        var equipmentOptionSysId = itemOption.insert();

        itemOption.initialize();
        itemOption.setValue('request_item', ritmGr.sys_id);
        itemOption.setValue('item_option_new', assetVar);
        itemOption.setValue('value', equipmentArray[0].asset_tags_serial_number_of_the_equipment);
        var assetOptionSysId = itemOption.insert();

        var rowIndex = 0;
        equipmentArray.forEach(function(row) {
            var mrvsRecord = new GlideRecord('sc_multi_row_question_answer');
            mrvsRecord.initialize();
            mrvsRecord.setValue('variable_set', varSet);
            mrvsRecord.setValue('parent_table_name', 'sc_req_item');
            mrvsRecord.setValue('parent_id', ritmGr.sys_id);
            mrvsRecord.setValue('item_option_new', equipVar);
            mrvsRecord.setValue('value', row.equipment);
            mrvsRecord.setValue('sc_item_option', equipmentOptionSysId);
            mrvsRecord.setValue('row_index', rowIndex);
            mrvsRecord.insert();

            mrvsRecord.initialize();
            mrvsRecord.setValue('variable_set', varSet);
            mrvsRecord.setValue('parent_table_name', 'sc_req_item');
            mrvsRecord.setValue('parent_id', ritmGr.sys_id);
            mrvsRecord.setValue('item_option_new', assetVar);
            mrvsRecord.setValue('value', row.asset_tags_serial_number_of_the_equipment);
            mrvsRecord.setValue('sc_item_option', assetOptionSysId);
            mrvsRecord.setValue('row_index', rowIndex);
            mrvsRecord.insert();

            rowIndex++;
        });

        gs.info("MRVS records created successfully.");
    },

    updateRequestAndRITM: function(requestSysId, requestedForSysId) {
        var requestGr = new GlideRecord('sc_request');
        if (requestGr.get(requestSysId)) {
            requestGr.setValue('requested_for', requestedForSysId);
            requestGr.setValue('opened_by', requestedForSysId);
            requestGr.update();
            gs.info("Request updated with requested_for: " + requestedForSysId + ", opened_by: " + requestedForSysId);
        } else {
            gs.warn("Request not found: " + requestSysId);
        }

        var ritmGr = new GlideRecord('sc_req_item');
        ritmGr.addQuery('request', requestSysId);
        ritmGr.query();
        while (ritmGr.next()) {
            ritmGr.setValue('requested_for', requestedForSysId);
            ritmGr.setValue('opened_by', requestedForSysId);
            ritmGr.update();
            gs.info("RITM updated: " + ritmGr.sys_id);
        }
    },

    type: 'ExcelToCart'
};

 

Community Alums
Not applicable
‎11-19-2024 07:33 AM

Can someone provide the supporting client script with the above script include

0 Helpfuls
Nisar3
Giga Guru
‎01-16-2025 10:03 PM

If I've a standalone excel file (i.e not present in sys_attachment table yet), how would I go about uploading it in that table?

Once uploaded, I'll get a sys_id and then I can use the script above to read it.

0 Helpfuls
Version history
Last update:
‎07-27-2023 11:09 AM
Updated by:
Contributors