Requirement to generate excel based on MRVS list user data or email or company in

[priyarrrrr]

hi all,

i have a requirement to generate excel based on MRVS list user data or email or company in service catalog form and attach that to ritm form post item got requested. Is there any way to do this.

var getExcel = Class.create();

getExcel.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    prepareData: function(row) {

        var companyName = row['External Company Name'] || '';

        var emailAddress = row['Email Address'] || '';

        var userName = row['External User Name'] || '';

        var results = [];

        var userRecord = new GlideRecord('sys_user');

        var companyRecord = new GlideRecord('core_company');

        // Handling email address

        if (emailAddress) {

            userRecord.addQuery('email', emailAddress);

            userRecord.query();

            if (userRecord.next()) {

                var data = {

                    external_user_name: userRecord.getValue('user_name'),

                    email_address: userRecord.getValue('email'),

                    external_company_name: this.getCompanyName(userRecord)

                };

                results.push(data);

            } else {

                gs.error('User record not found for email: ' + emailAddress);

            }

        }

        // Handling user name

        else if (userName) {

            userRecord.addQuery('user_name', userName);

            userRecord.query();

            if (userRecord.next()) {

                var data = {

                    external_user_name: userRecord.getValue('user_name'),

                    email_address: userRecord.getValue('email'),

                    external_company_name: this.getCompanyName(userRecord)

                };

                results.push(data);

            } else {

                gs.error('User record not found for username: ' + userName);

            }

        }

        // Handling company names

        else if (companyName) {

            var companyNames = companyName.split(',').map(function(name) {

                return name.trim(); // Remove any extra whitespace

            });

            companyNames.forEach(function(name) {

                companyRecord.addQuery('name', name);

                companyRecord.query();

                if (companyRecord.next()) {

                    var companySysId = companyRecord.getValue('sys_id');

                    var usersInCompany = new GlideRecord('sys_user');

                    usersInCompany.addQuery('company', companySysId);

                    usersInCompany.query();

                    while (usersInCompany.next()) {

                        var data = {

                            external_user_name: usersInCompany.getValue('user_name'),

                            email_address: usersInCompany.getValue('email'),

                            external_company_name: name

                        };

                        results.push(data);

                    }

                    if (results.length === 0) {

                        gs.error('No users found for company: ' + name);

                    }

                } else {

                    gs.error('Company record not found for name: ' + name);

                }

            });

        } else {

            gs.error('Neither external user name, email address, nor company name provided');

        }

        return results;

    },

    getCompanyName: function(userRecord) {

        var companySysId = userRecord.getValue('company');

        if (companySysId) {

            var companyRecord = new GlideRecord('core_company');

            if (companyRecord.get(companySysId)) {

                return companyRecord.getValue('name');

            } else {

                gs.error('Company record not found for Sys ID: ' + companySysId);

            }

        }

        return '';

    },

    getData: function() {

        var id = this.getParameter('sysparm_id');

        if (!id) {

            gs.error('Attachment Sys ID is missing');

            return JSON.stringify([]);

        }

        var resultsArray = [];

        var attachment = new GlideSysAttachment();

        var parser = new sn_impex.GlideExcelParser();

        try {

            var stream = attachment.getContentStream(id);

            parser.parse(stream);

            while (parser.next()) {

                var row = parser.getRow();

                var data = this.prepareData(row);

                if (Array.isArray(data)) {

                    resultsArray = resultsArray.concat(data);

                } else if (data) {

                    resultsArray.push(data);

                }

            }

        } catch (e) {

            gs.error('Error parsing Excel file: ' + e.message);

        }

        // Log the results for debugging

        gs.info('Parsed Data: ' + JSON.stringify(resultsArray));

        return JSON.stringify(resultsArray);

    },

    type: 'getExcel'

});

 

var getExcel = Class.create();
getExcel.prototype = Object.extendsObject(AbstractAjaxProcessor, {
 
    prepareData: function(row) {
        var companyName = row['External Company Name'] || '';
        var emailAddress = row['Email Address'] || '';
        var userName = row['External User Name'] || '';
 
        var results = [];
 
        var userRecord = new GlideRecord('sys_user');
        var companyRecord = new GlideRecord('core_company');
 
        // Handling email address
        if (emailAddress) {
            userRecord.addQuery('email', emailAddress);
            userRecord.query();
            if (userRecord.next()) {
                var data = {
                    external_user_name: userRecord.getValue('user_name'),
                    email_address: userRecord.getValue('email'),
                    external_company_name: this.getCompanyName(userRecord)
                };
                results.push(data);
            } else {
                gs.error('User record not found for email: ' + emailAddress);
            }
        }
        // Handling user name
        else if (userName) {
            userRecord.addQuery('user_name', userName);
            userRecord.query();
            if (userRecord.next()) {
                var data = {
                    external_user_name: userRecord.getValue('user_name'),
                    email_address: userRecord.getValue('email'),
                    external_company_name: this.getCompanyName(userRecord)
                };
                results.push(data);
            } else {
                gs.error('User record not found for username: ' + userName);
            }
        }
        // Handling company names
        else if (companyName) {
            var companyNames = companyName.split(',').map(function(name) {
                return name.trim(); // Remove any extra whitespace
            });
 
            companyNames.forEach(function(name) {
                companyRecord.addQuery('name', name);
                companyRecord.query();
                if (companyRecord.next()) {
                    var companySysId = companyRecord.getValue('sys_id');
                    var usersInCompany = new GlideRecord('sys_user');
                    usersInCompany.addQuery('company', companySysId);
                    usersInCompany.query();
 
                    while (usersInCompany.next()) {
                        var data = {
                            external_user_name: usersInCompany.getValue('user_name'),
                            email_address: usersInCompany.getValue('email'),
                            external_company_name: name
                        };
                        results.push(data);
                    }
 
                    if (results.length === 0) {
                        gs.error('No users found for company: ' + name);
                    }
                } else {
                    gs.error('Company record not found for name: ' + name);
                }
            });
        } else {
            gs.error('Neither external user name, email address, nor company name provided');
        }
 
        return results;
    },
 
    getCompanyName: function(userRecord) {
        var companySysId = userRecord.getValue('company');
        if (companySysId) {
            var companyRecord = new GlideRecord('core_company');
            if (companyRecord.get(companySysId)) {
                return companyRecord.getValue('name');
            } else {
                gs.error('Company record not found for Sys ID: ' + companySysId);
            }
        }
        return '';
    },
 
    getData: function() {
        var id = this.getParameter('sysparm_id');
        if (!id) {
            gs.error('Attachment Sys ID is missing');
            return JSON.stringify([]);
        }
 
        var resultsArray = [];
        var attachment = new GlideSysAttachment();
        var parser = new sn_impex.GlideExcelParser();
 
        try {
            var stream = attachment.getContentStream(id);
            parser.parse(stream);
 
            while (parser.next()) {
                var row = parser.getRow();
                var data = this.prepareData(row);
                if (Array.isArray(data)) {
                    resultsArray = resultsArray.concat(data);
                } else if (data) {
                    resultsArray.push(data);
                }
            }
        } catch (e) {
            gs.error('Error parsing Excel file: ' + e.message);
        }
 
        // Log the results for debugging
        gs.info('Parsed Data: ' + JSON.stringify(resultsArray));
 
        return JSON.stringify(resultsArray);
    },
 
    type: 'getExcel'
});
 

now i want to generate the excel and attach the fetched data into the RITM form,