Parse an excel which we receive via email

[Qamrain Peerzad]

if I have to parse an excel sheet which we we receive via email that email will end up  in sys_email table and its attachment will reside in sys_attachment table. The Excel contains details about terminated users and that users need to be removed from multiple groups how can i achieve that. i have added the format of excel screenshot for reference. 

[Nishant_Shelar]

Hi @Qamrain Peerzad 

This is Untested

Set your Transform Map target to sys_user_grmember. create new Transform Map onBefore script and use the following logic to find the records and delete them during the transformation:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

    var empNumber = source.u_employee_number; // Column B
    var groupName = source.u_group;           // Column I
    
    if (empNumber && groupName) {
        var userSysId = '';
        var groupSysId = '';

        // 1. Find the User
        var userGr = new GlideRecord('sys_user');
        if (userGr.get('employee_number', empNumber)) {
            userSysId = userGr.getUniqueValue();
        }

        // 2. Find the Group
        var groupGr = new GlideRecord('sys_user_group');
        if (groupGr.get('name', groupName)) {
            groupSysId = groupGr.getUniqueValue();
        }

        // 3. Find and Delete the Membership
        if (userSysId && groupSysId) {
            var memberGr = new GlideRecord('sys_user_grmember');
            memberGr.addQuery('user', userSysId);
            memberGr.addQuery('group', groupSysId);
            memberGr.query();
            
            if (memberGr.next()) {
                memberGr.deleteRecord();
            }
        }
    }
    
    // Ignore the actual insert/update so the import set table doesn't create blank records
    ignore = true; 

})(source, map, log, target);

[Tanushree Maiti]

Hi  @Qamrain Peerzad 

• Setup a Data Source and Transform Map allows ServiceNow to do the this lifting automatically
• Ensure your Excel sheet has a unique identifier column for each user

1. Navigate to System Import Sets > Data Sources 
2. upload your Excel file.
3. Create a Transform Map & Map your user identifier (like u_email ) to the User table.
4. Add an onAfter Script to strip the terminated user out of every group they belong to in the sys_user_grmember table

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
var userGr = new GlideRecord('sys_user');

    userGr.addQuery('email', source.u_email);

    userGr.query();

    var groupGr = new GlideRecord('sys_user_group');

    groupGr.addQuery('name', source.u_group);

    groupGr.query();

    if (userGr.next() && groupGr.next()) {

        var memberGr = new GlideRecord('sys_user_grmember');

        memberGr.addQuery('user', userGr.sys_id);

        memberGr.addQuery('group', groupGr.sys_id);

        memberGr.query();

        if (memberGr.next()) {

            memberGr.deleteRecord();

        }

    }

})(source, map, log, target);

[Qamrain Peerzad]

Thanks for the Suggestion guys i really appreciate it but i will get this excel via email and and that will likely reside in sys_attachment table, i was thinking can i use GlideExcelParser() api for this if yes how can i do that 

in this format i will get excel via email

[Tanushree Maiti]

Hi @Qamrain Peerzad 

Refer these community posts to attach excel to email notification:

                         How to generate excel file from a script in ServiceNow 

                        Attaching an Excel file of a report to an email notification

                        Generate Excel file and attach in email notification