How to generate excel file from a script in ServiceNow

[karishmashaik]

Hi

we need to generate Excel sheet  a request to be placed in a file to be auto generated (possibly xls, xlsx etc. format) as an attachment & should be attached to the Requested item submitted.

We did had something similar to be done for an request item which was achieved by using the below snippet in the Runscript activity 

All that is required is to retrieve the list of variables & then use the Write functionality of GlideSysAttachment API class.

i am trying this script pls help me on this :

var Headers = ["Number","Caller","Short Desc","Assignment Group", "Assigned To"];
var fileName = 'Incidents.xls';
var xlsData = ''; //The variable xlsData will contain a string which is used to build the xls file contents
for (var i = 0; i < Headers.length; i++) { //Build the Headers
xlsData = xlsData + '"' + Headers[i] + '"' + ',';
}
xlsData = xlsData+"\r\n";

var gr = new GlideRecord("incident");
gr.addActiveQuery();
gr.query();
while(gr.next()) {
xlsData = xlsData + '"' + gr.number + '",' + '"' + gr.caller_id.getDisplayValue() + '",' + '"' + gr.short_description+'",' + '"' + gr.assignment_group.getDisplayValue() + '",' + '"' + gr.assigned_to.getDisplayValue() + '"';
xlsData = xlsData+"\r\n";
}

//attach the file to a record.
var grRec = new GlideRecord("incident");
grRec.addQuery("sys_id","00b4939bdb010110c8cf9026ca9619e7");
grRec.query();
if(grRec.next()){
var grAttachment = new GlideSysAttachment();
grAttachment.write(grRec, fileName,'application/xls',xlsData);
}

for example

This would then print data in Excel in format as above

If at all data is required to be printed in the format as above

with all Variable questions in Column A & its corresponding values in column B (something in tabular form) then all you need is to use the above snippet

[Nandu3]

Hello Mattias Johnsson,

I need to create a file of size up to 80 MB. I have created a flow using utility action spoke but My flow displaying below error while creating the file.

Error:  String object would exceed maximum permitted size of 33554432.

Could you please let me know, if there is any other way to create big size file.

Regards,

Nanda Kishore

[MattiasJonsson]

I'm not sure. There's probably a sys_property you could tweak to make that work. I haven't investigated it that deep to be honest.

[Bernard A_ Mora]

You can use this script:

(function(){

    /*

     * Bernard Armando Morales Nicola

     * Test Class to generate a CSV and attachs it to a

     * Scheduled Email of Report

     */

    Object.prototype.stringify = function(obj) {

        gs.print(JSON.stringify(obj, null, 2));

    };

    var GenerateExcelReport = Class.create();

    GenerateExcelReport.prototype = {

        initialize: function() {

            this.headers = ['Name','Assigned To','Company Alias'];

            this.fileName = 'TestBM.csv';

            this.xlsData = '';

            this.scheduledEmailReport = 'a813d5f1db6b859018d30bf5f39619ba';

        },

        generateHeaders: function() {

            this.xlsData = this.headers.reduce(function(xlsHeader, currentHeader, index) {

                xlsHeader += currentHeader;

                xlsHeader += (index !== this.headers.length - 1) ? ';' : this.newLine;

                return xlsHeader;

            }.bind(this), '');

            return this;

        },

        setRowsData: function() {

            var query          = 'sys_class_name=cmdb_ci_computer^model_id.nameLIKEHP EliteBook^company!=NULL';

            var companyAliases = ['ActiveDev Company', 'Others'];

            var regExp         = /^(?:ActiveDev|Acc)[\w\W]*/i;

            var grComputers = new GlideRecord('cmdb_ci_computer');

                grComputers.addEncodedQuery(query);

                grComputers.setLimit(10);

                grComputers.query();

            while (grComputers.next()) {

                var computerData = {

                    computerName:        String(grComputers.name),

                    computerAssignedTo:  String(grComputers.assigned_to.name),

                    computerUserCompany: regExp.test(String(grComputers.assigned_to.company.name)) ?  companyAliases[0] : companyAliases[1]

                };

                this.xlsData += computerData.computerName + ';' +

                                computerData.computerAssignedTo + ';' +

                                computerData.computerUserCompany + this.newLine;

            }

            return this;

        },

        generateAttachment: function() {

            var grAttachment = new GlideRecord('sysauto_report');

            if (grAttachment.get(this.scheduledEmailReport)) {

                gs.print(grAttachment.name);

                var xlsAttachment = new GlideSysAttachment();

                    xlsAttachment.deleteAll(grAttachment);

                    xlsAttachment.write(grAttachment, this.fileName, 'text/csv', this.xlsData);

            }

            return this;

        },

        newLine: String.fromCharCode(13) + String.fromCharCode(10),

        type: 'GenerateExcelReport'

    }

    Object.stringify(new GenerateExcelReport().generateHeaders().setRowsData().generateAttachment());

})();