MRVS export

Tommaso2
Tera Expert

Hi all,

 

I have multiple MRVS in RITM and I need to generate excel from them, possibly I would like to have each MRVS in a separate sheet of the same excel. 

 

Could you please support?

Many thanks in advance,

Tommaso

1 ACCEPTED SOLUTION

Robbie
Kilo Patron
Kilo Patron

Hi @Tommaso2,

 

I saw this very same question answered earlier.

Kudos to @Martina283L who provided the solution - https://www.servicenow.com/community/virtual-agent-forum/exporto-mrvs-to-excel/m-p/3144156/page/3.

 

To help others (and for me to gain recognition for my efforts), please mark this response correct by clicking on Accept as Solution and/or Kudos.




Thanks, Robbie

 

Just in case the link is broken moving forward, below is @Martina283L's solution:

 var columns_of_excel = "<?xml version='1.0' encoding='UTF-8'?>";
    columns_of_excel += "<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>";

    // Define the Styles for bold headers
    columns_of_excel += "<Styles>";
    columns_of_excel += "<Style ss:ID='BoldHeader'>";
    columns_of_excel += "<Font ss:Bold='1'/>"; // Make the font bold
    columns_of_excel += "</Style>";
    columns_of_excel += "</Styles>";

    var gr_sh = new GlideRecord('io_set_item');
    gr_sh.addEncodedQuery('sc_cat_item='+[YOUR CAT ITEM]); //MRVS included in your cat item
    gr_sh.query();

    while (gr_sh.next()) {
        var rows_data = "";
        //gs.info('row_data first'+rows_data);
        columns_of_excel += "<Worksheet ss:Name='" + gr_sh.variable_set.title +
            "'>";
        columns_of_excel += "<Table>";

        // Define the headers and apply the bold style
        columns_of_excel += "<Row>";
        var gr_col = new GlideRecord('item_option_new');
        gr_col.addEncodedQuery('variable_set=' + gr_sh.variable_set + '^ORDERBYorder');
        gr_col.query();

        while (gr_col.next()) {
            columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>" + gr_col.question_text + "</Data></Cell>";
        }

        columns_of_excel += "</Row>";

        // Retrieve the MRVS
        var rows = '';
        var qa_r = new GlideRecord('sc_multi_row_question_answer'); //Tabella con i valori del multirow variable set
        qa_r.addEncodedQuery('parent_id=' + current.getUniqueValue() + '^variable_set=' + gr_sh.variable_set);
        qa_r.query();
        while (qa_r.next()) {
            if (rows == '') {
                rows = qa_r.row_index;
            } else {
                rows += ',' + qa_r.row_index;
            }

        }
 
        var arrayUtil = new ArrayUtil();
        var rowIndex = arrayUtil.unique(rows.split(','));

        for (var r = 0; r < rowIndex.length; r++) {
            rows_data += "<Row>";
            var gr_col2 = new GlideRecord('item_option_new');
            gr_col2.addEncodedQuery('variable_set=' + gr_sh.variable_set + '^ORDERBYorder');
            gr_col2.query();

            while (gr_col2.next()) {
                var gr = new GlideRecord('sc_multi_row_question_answer'); //Tabella con i valori del multirow variable set
                gr.addEncodedQuery('parent_id=' + current.getUniqueValue() + '^variable_set=' + gr_sh.variable_set + '^row_index=' + rowIndex[r] + "^item_option_new=" + gr_col2.getUniqueValue());
                gr.query();

                if (gr.next()) {
                    rows_data += "<Cell><Data ss:Type='String'>" + gr.getDisplayValue('value') + "</Data></Cell>";
                } else {
                    rows_data += "<Cell><Data ss:Type='String'> </Data></Cell>";
                }

            }
            rows_data += "</Row>";
        }
         // Close the XML structure
        columns_of_excel += rows_data;
        columns_of_excel += "</Table>";
        columns_of_excel += "</Worksheet>";
    }
    columns_of_excel += "</Workbook>";

    // Combine the columns (headers) and rows data
    var columns_and_rows = columns_of_excel; // complete data including headers and rows

    // Create the attachment
    var attachment = new GlideSysAttachment();
    attachment.write(current, current.number + ".xls", "application/vnd.ms-excel", columns_and_rows);
   
    gs.info(current.number+" Excel file exported and attached successfully. Please check.");

 

View solution in original post

3 REPLIES 3

Viraj Hudlikar
Giga Sage

Hello @Tommaso2 

Refer the solution over Solved: Exporto MRVS to Excel - ServiceNow Community


If my response has helped you hit helpful button and if your concern is solved do mark my response as correct.

 

Thanks & Regards
Viraj Hudlikar.

Ankur Bawiskar
Tera Patron
Tera Patron

@Tommaso2 

Seems a duplicate question in community from other person

It was answered here, the link already shared by Viraj

Exporto MRVS to Excel 

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

Robbie
Kilo Patron
Kilo Patron

Hi @Tommaso2,

 

I saw this very same question answered earlier.

Kudos to @Martina283L who provided the solution - https://www.servicenow.com/community/virtual-agent-forum/exporto-mrvs-to-excel/m-p/3144156/page/3.

 

To help others (and for me to gain recognition for my efforts), please mark this response correct by clicking on Accept as Solution and/or Kudos.




Thanks, Robbie

 

Just in case the link is broken moving forward, below is @Martina283L's solution:

 var columns_of_excel = "<?xml version='1.0' encoding='UTF-8'?>";
    columns_of_excel += "<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>";

    // Define the Styles for bold headers
    columns_of_excel += "<Styles>";
    columns_of_excel += "<Style ss:ID='BoldHeader'>";
    columns_of_excel += "<Font ss:Bold='1'/>"; // Make the font bold
    columns_of_excel += "</Style>";
    columns_of_excel += "</Styles>";

    var gr_sh = new GlideRecord('io_set_item');
    gr_sh.addEncodedQuery('sc_cat_item='+[YOUR CAT ITEM]); //MRVS included in your cat item
    gr_sh.query();

    while (gr_sh.next()) {
        var rows_data = "";
        //gs.info('row_data first'+rows_data);
        columns_of_excel += "<Worksheet ss:Name='" + gr_sh.variable_set.title +
            "'>";
        columns_of_excel += "<Table>";

        // Define the headers and apply the bold style
        columns_of_excel += "<Row>";
        var gr_col = new GlideRecord('item_option_new');
        gr_col.addEncodedQuery('variable_set=' + gr_sh.variable_set + '^ORDERBYorder');
        gr_col.query();

        while (gr_col.next()) {
            columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>" + gr_col.question_text + "</Data></Cell>";
        }

        columns_of_excel += "</Row>";

        // Retrieve the MRVS
        var rows = '';
        var qa_r = new GlideRecord('sc_multi_row_question_answer'); //Tabella con i valori del multirow variable set
        qa_r.addEncodedQuery('parent_id=' + current.getUniqueValue() + '^variable_set=' + gr_sh.variable_set);
        qa_r.query();
        while (qa_r.next()) {
            if (rows == '') {
                rows = qa_r.row_index;
            } else {
                rows += ',' + qa_r.row_index;
            }

        }
 
        var arrayUtil = new ArrayUtil();
        var rowIndex = arrayUtil.unique(rows.split(','));

        for (var r = 0; r < rowIndex.length; r++) {
            rows_data += "<Row>";
            var gr_col2 = new GlideRecord('item_option_new');
            gr_col2.addEncodedQuery('variable_set=' + gr_sh.variable_set + '^ORDERBYorder');
            gr_col2.query();

            while (gr_col2.next()) {
                var gr = new GlideRecord('sc_multi_row_question_answer'); //Tabella con i valori del multirow variable set
                gr.addEncodedQuery('parent_id=' + current.getUniqueValue() + '^variable_set=' + gr_sh.variable_set + '^row_index=' + rowIndex[r] + "^item_option_new=" + gr_col2.getUniqueValue());
                gr.query();

                if (gr.next()) {
                    rows_data += "<Cell><Data ss:Type='String'>" + gr.getDisplayValue('value') + "</Data></Cell>";
                } else {
                    rows_data += "<Cell><Data ss:Type='String'> </Data></Cell>";
                }

            }
            rows_data += "</Row>";
        }
         // Close the XML structure
        columns_of_excel += rows_data;
        columns_of_excel += "</Table>";
        columns_of_excel += "</Worksheet>";
    }
    columns_of_excel += "</Workbook>";

    // Combine the columns (headers) and rows data
    var columns_and_rows = columns_of_excel; // complete data including headers and rows

    // Create the attachment
    var attachment = new GlideSysAttachment();
    attachment.write(current, current.number + ".xls", "application/vnd.ms-excel", columns_and_rows);
   
    gs.info(current.number+" Excel file exported and attached successfully. Please check.");