how to extract the data from an excel file into slush bucket or select users list

[Hema koneti]

i ll upload into an attachment in a catalog item, that data in an excel file which consists of users, should be selected into a selected users variable field.

[Maddysunil]

@Hema koneti Create a Business Rule or a Script Include that will be triggered when the catalog item is submitted.

• This script will need to:
  • Parse the uploaded Excel file.
  • Extract user information from the file.
  • Update the selected users variable field.

Below is the sample template you can follow

// Get the uploaded Excel file attachment record
var attachment = new GlideRecord('sys_attachment');
attachment.addQuery('table_name', 'your_catalog_table'); // Replace 'your_catalog_table' with the actual table name of your catalog item
attachment.query();
if (attachment.next()) {
    // Parse the Excel file and extract user information
    var usersData = parseExcel(attachment);

    // Update the selected users variable with the extracted data
    current.variables.selected_users = usersData;

    // Save the catalog item
    current.update();
}

function parseExcel(attachment) {
    // Implement your logic to parse the Excel file and extract user information
    return extractedUserData;
}

Please mark my response correct/helpful if it helps you

[Hema koneti]

please give me the script to call it in client script

[Maddysunil]

@Hema koneti If you want to call it from client side then you can modify the updated code in script include and call it from client side:

var ExcelParserScriptInclude = Class.create();
ExcelParserScriptInclude.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    parseExcelAndUpdateCatalogItem: function () {
        // Get the uploaded Excel file attachment record
        var attachment = new GlideRecord('sys_attachment');
        attachment.addQuery('table_sys_id', currentRecord.sys_id); // Assuming 'current' is your catalog item record
        attachment.query();
        if (attachment.next()) {
            // Get the content stream of the attached Excel file
            var excelFileContent = GlideSysAttachment.getContentStream(attachment.sys_id);

            // Parse the Excel file content and extract user information
            var usersData = this.parseExcel(excelFileContent);
            currentRecord.variables.selected_users = usersData;
            currentRecord.update();
        }
    },

    parseExcel: function (excelFileContent) {
        // Implement your logic to parse the Excel file content and extract user information
        var extractedUserData = [];

        // Example using xlsx library to parse Excel content
        try {
            var workbook = XLSX.read(excelFileContent, { type: 'binary' });
            var sheetName = workbook.SheetNames[0]; // Assuming data is in the first sheet
            var sheet = workbook.Sheets[sheetName];
            extractedUserData = XLSX.utils.sheet_to_json(sheet, { header: 'column' });
        } catch (e) {
            gs.error('Error parsing Excel file: ' + e);
        }
        return extractedUserData;
    },
    type: 'ExcelParserScriptInclude'
});

[Hema koneti]

Need onLoad or onChange catalog client script, which includes the Script include that need to be called