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).

[Ankur Bawiskar]

Hi @Rahul Raja Samineni 

then convert it and set in yyyy-mm-DD

(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]);

                var gd = new GlideDate();
                gd.setDisplayValue(row[header10], "mm-DD-yyyy");
                var myDate = gd.getByFormat("yyyy-mm-DD");

                cart.setVariable(item, 'effective_date', myDate);

                var rc = cart.placeOrder();

            }
        }
    } 
})(current, previous);

Regards
Ankur

[Rahul Raja Sami]

no sir , it is still showing the same blank in the field.

[Rahul Raja Sami]

cart.setVariable(item, 'effective_dates', row[header1]);

after changing the type in the sheet to yyyy-mm-dd, i have used the above code and it is populating the respective values correctly.

thanks for the time and effort, Ankur

[Ankur Bawiskar]

Hi @Rahul Raja Samineni 

Glad to know.

Mark your own response as correct and close the thread.

Regards
Ankur

[Ankur Bawiskar]

Hi,

after converting to yyyy-mm-DD it should work fine

Regards
Ankur