need to attach my queried records from a table as excel attachment in email triggered from an event.

[Ravivarman Saee]

i already query indicator task table and filter incomplete records using an email script and the records are mentioned in the email as a table. I want the same queried records be attached as an excel file in the same email that is triggered using an event. How to do ?

The email script is mentioned below.

(function runMailScript( /* GlideRecord */ current, /* TemplatePrinter */ template,

    /* Optional EmailOutbound */

    email, /* Optional GlideRecord */ email_action,

    /* Optional GlideRecord */

    event) {

    var profiles = [];

    var func = [];

    var sfun = [];

    var conr = [];

    var ids = event.parm2;

    //get all pending tasks

    var gr = new GlideRecord('sn_grc_indicator_task');

    gr.addQuery('sys_id', 'IN', ids);

    gr.query();

    while (gr.next()) {

        profiles.push(gr.indicator.item.profile.name.toString());

        func.push(gr.indicator.item.type.name.toString());

        sfun.push(gr.indicator.item.u_sub_function.toString());

        conr.push(gr.indicator.item.owner.name.toString());

    }

    //consider unique profiles and functions

    var unique_array = new ArrayUtil();

    var pf = unique_array.unique(profiles);

    gs.print(pf);

    var fn = unique_array.unique(func);

    gs.print(fn);

    var sf = unique_array.unique(sfun);

    gs.print(sf);

    var onr = unique_array.unique(conr);

    gs.print(onr);

    //create table for combination of profiles and functions and count of each combination

    template.print('<table border="1"><tr><td>Profile</td><td>Function</td><td>Sub Function</td><td>#Pending Indicators</td></tr>');

    for (var i = 0; i < pf.length; i++) {

        for (var j = 0; j < fn.length; j++) {

            for (var k = 0; k < sf.length; k++) {

                for (var l = 0; l < onr.length; l++) {

                    var rec = new GlideRecord('sn_grc_indicator_task');

                    rec.addEncodedQuery('sys_idIN' + ids + '^indicator.item.profile.name=' + pf[i] + '^indicator.item.type.name=' + fn[j] + '^indicator.item.owner.name=' + onr[l] + '^indicator.item.u_sub_function=' + sf[k]);

                    rec.query();

                    var count = rec.getRowCount();

                    if (count) {

                        template.print('<tr><td>' + pf[i] + '</td><td>' + fn[j] + '</td><td>' + sf[k] + '</td><td>' + count + '</td></tr>');

                    }

                }

            }

        }

    }

    template.print('</table>');

})(current, template, email, email_action, event);

[Sumanth16]

Hi @Ravivarman Saee ,

Please. refer to the below thread:

https://www.servicenow.com/community/developer-articles/parsing-the-excel-sheet-data-use-the-data-in...

If I could help you with your Query then, please hit the Thumb Icon and mark it as Correct !!

Thanks & Regards,

Sumanth Meda

[Ravivarman Saee]

HI Sumanth,

As per my understanding, in this scenario, he inserts the template when raising a request. In my case i want the excel sheet created and attached as an attachment in the email that is already being sent.

[Ivan Betev]

Hi @Ravivarman Saee ,

there is an issue with this approach. At the time the notification and the associated email script get triggered, the email itself is not yet created.

As you can see on the screenshot below, the email parameter you are provided with has EmailOutbound type, which doesn't have any means to work with attachments.

In theory you could create an email inside of your Email Script and add attachments to it, but it will be a separate email.

Here is an unrelated illustration of how you can generate CSV (for the case simplification reasons) and add it to the email with the help of GlideSysAttachment API:

/* 1. Query the GlideRecord */ 
var gr = new GlideRecord('incident');
gr.addQuery('active', true);
gr.query();

/* 2. Format the Data (CSV) */
var csv = 'Number,Short Description,Priority\n';
while (gr.next()) {
    csv += gr.number + ',' + gr.short_description + ',' + gr.priority + '\n';
}

/* 3. Attach the File to an Email */
var email = new GlideRecord('sys_email');
email.initialize();
email.type = 'send-ready';
email.subject = 'Incidents Report';
email.body = 'Please find attached the incidents report.';
email.insert();

var attachment = new GlideSysAttachment();
attachment.write(email, 'incidents_report.csv', 'text/csv', csv);

Regards, Ivan

[Ravivarman Saee]

HI Ivan,
As you mentioned, i used the above code in my email script and it triggered a separate email only. Is there any way i can attach this csv file in the same mail i am triggering using email script ?