How to populate Manager field(reference type) and effective date from excel sheet when using Glide Excel Parser.

[Rahul Raja Sami]

I am using Glide Excel parser and Cart API to create requests from the excel.

everything works fine but cannot populate Manager and effective date fields from Excel 

(function executeRule(current, previous /*null when async*/ ) {

    var gr = new GlideRecord('sys_attachment');
    gr.addQuery('table_sys_id', current.sys_id);
    gr.query();
    gs.log('request ---------------' + current.sys_id);
    if (gr.next()) {
 
        var parser = new sn_impex.GlideExcelParser();
        var attachment = new GlideSysAttachment();
        // use attachment sys id of an excel file
        var attachmentStream = attachment.getContentStream(gr.sys_id);

        parser.parse(attachmentStream);

        //retrieve the column headers
        var headers = parser.getColumnHeaders();
        var header1 = headers[0];
        var header2 = headers[1];
        var header3 = headers[2];
        var header4 = headers[3];
        var header5 = headers[4];
        var header6 = headers[5];
        var header7 = headers[6];
        var header8 = headers[7];
        var header9 = headers[8];
        var header10 = headers[9];

        while (parser.next()) {
            var row = parser.getRow();
            var message = "";
            var gr1 = GlideRecord('sys_user');
            gr1.addQuery('name', row[header3]);
            gr1.query();
            if (gr1.next()) {

                var success = true;
                var cartId = GlideGuid.generate(null);
                var cart = new Cart(cartId); //calling the cart API  
                var item = cart.addItem('52d4943187b6e810d0d4ca27cebb3521');
                cart.setVariable(item, 'requested_by', gr1.sys_id);
                cart.setVariable(item, 'first_name', row[header2]);
                cart.setVariable(item, 'last_name', row[header3]);
                cart.setVariable(item, 'access', 'AD');
                cart.setVariable(item, 'off_user_email', row[header4]);
                cart.setVariable(item, 'manager', row[header9]);
                cart.setVariable(item, 'effective_date', row[header1]);

                var rc = cart.placeOrder();
             
            }
        }
    } 
})(current, previous);

the excel sheet contains 10 columns. (9th column is Manager and 1st column is Date).

[palanikumar]

Hi,

For Manager, run GlideRecord on User table and get the manager record and pass the sys_id

For Datetime, I see close square bracket missing. Check whether this is causing this issue

[Rahul Raja Sami]

Hi, that works for the manager but For Datetime ,it is a typing mistake.

[Mohith Devatte]

hello @Rahul Raja Samineni try this script 

if you want the manager for the user you selected in row[header3] then simpley use gr1.manager like below

(function executeRule(current, previous /*null when async*/ ) {

    var gr = new GlideRecord('sys_attachment');
    gr.addQuery('table_sys_id', current.sys_id);
    gr.query();
    gs.log('request ---------------' + current.sys_id);
    if (gr.next()) {
 
        var parser = new sn_impex.GlideExcelParser();
        var attachment = new GlideSysAttachment();
        // use attachment sys id of an excel file
        var attachmentStream = attachment.getContentStream(gr.sys_id);

        parser.parse(attachmentStream);

        //retrieve the column headers
        var headers = parser.getColumnHeaders();
        var header1 = headers[0];
        var header2 = headers[1];
        var header3 = headers[2];
        var header4 = headers[3];
        var header5 = headers[4];
        var header6 = headers[5];
        var header7 = headers[6];
        var header8 = headers[7];
        var header9 = headers[8];
        var header10 = headers[9];

        while (parser.next()) {
            var row = parser.getRow();
            var message = "";
            var gr1 = new  GlideRecord('sys_user');
            gr1.addQuery('name', row[header3]);
            gr1.query();
            if (gr1.next()) {

                var success = true;
                var cartId = GlideGuid.generate(null);
                var cart = new Cart(cartId); //calling the cart API  
                var item = cart.addItem('52d4943187b6e810d0d4ca27cebb3521');
                cart.setVariable(item, 'requested_by', gr1.sys_id);
                cart.setVariable(item, 'first_name', row[header2]);
                cart.setVariable(item, 'last_name', row[header3]);
                cart.setVariable(item, 'access', 'AD');
                cart.setVariable(item, 'off_user_email', row[header4]);
                cart.setVariable(item, 'manager', gr1.manager);
                cart.setVariable(item, 'effective_date', row[header10]);

                var rc = cart.placeOrder();
             
            }
        }
    } 
})(current, previous);

[Mohith Devatte]

@Rahul Raja Samineni thanks for marking the answer helpful .

Is the issue solved ?is yes mark the answer correct and close the thread so that it helps future readers and if not let me know the existing issue