Exporto MRVS to Excel

Martina283L · ‎01-08-2025

I have several MRVS that I should export each to a different sheet, I should attach the file to the ritm

Martina283L · ‎01-08-2025

I was able to create the xls file with different sheets by creating this custom BR:

 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

Ankur Bawiskar · ‎01-08-2025

@Martina283L

not easy to generate excel.

why not create csv and each MRVS row will be a row in csv?

you can check my blog on how to generate CSV from RITM and enhance it as per your requirement to handle MRVS

Generate csv file with the catalog variables and attaching to RITM record

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

Martina283L · ‎01-08-2025

The user requirement is that he must have a file that can be manipulated with excel so that he can compare it with other excel files.

Martina283L · ‎01-08-2025

@Ankur Bawiskar Do you think Microsoft 365 Excel Spoke plugin can help me?

Ankur Bawiskar · ‎01-08-2025

@Martina283L

I haven't used that spoke.

Did you check what that spoke offers?

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

Ankur Bawiskar · ‎01-08-2025

@Martina283L

if that spoke has capability to generate excel then also it won't be easy if that spoke doesn't allow heavy scripting

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