Send an RITM data to email using excel format

Lakshmi53 · 3 weeks ago

Hi,

my query is, i need to send an email to the user every month 1st. that email contains last month RITM's and few additional field in excel format. below script i written but not worked. created event and notification and the table is sys_user.

schedule job:

var ritminfo = gs.getProperty("ritm.monthly_report");

var reqItemGR = new GlideRecord('sc_req_item');

reqItemGR.addEncodedQuery(ritminfo);

reqItemGR.query();

var ritmDetailsArray = [];

var recipients = [];

while (reqItemGR.next()) {

var financialApprovers =

reqItemGR.variables.financial_approvers + '';

// Collect unique recipients

if (financialApprovers) {

var approverList = financialApprovers.split(',');

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

var approver = approverList[i].trim();

if (approver && recipients.indexOf(approver) == -1) {

recipients.push(approver);

}

// Store RITM information

ritmDetailsArray.push({

number: reqItemGR.getValue('number'),

requested_for: reqItemGR.getDisplayValue('requested_for'),

created: reqItemGR.getValue('sys_created_on'),

approval: reqItemGR.getDisplayValue('approval'),

created_by: reqItemGR.getValue('sys_created_by')

});

}

// Trigger only ONE event

if (ritmDetailsArray.length > 0) {

gs.eventQueue(

'monthly.report',

reqItemGR,

recipients.join(','),

JSON.stringify(ritmDetailsArray)

);

}

email script:

(function runMailScript(current, template, email, email_action, event) {

var recipients = event.parm1 + '';

var ritmInfo = [];

try {

ritmInfo = JSON.parse(event.parm2 + '');

} catch (e) {

gs.error('JSON Parse Error: ' + e.message);

return;

}

function escapeCSV(value) {

if (value === null || value === undefined)

return '""';

value = String(value);

return '"' + value.replace(/"/g, '""') + '"';

}

var headers = [

"Number",

"Requested For",

"Created",

"Approval",

"Created By"

];

var csvData = headers.map(escapeCSV).join(',') + '\r\n';

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

csvData += [

ritmInfo[i].number,

ritmInfo[i].requested_for,

ritmInfo[i].created,

ritmInfo[i].approval,

ritmInfo[i].created_by

].map(escapeCSV).join(',') + '\r\n';

}

email.setTo(recipients);

var emailSysId = email.getSysID();

if (emailSysId) {

var gsa = new GlideSysAttachment();

gsa.write(

'sys_email',

emailSysId,

'Monthly_RITM_Report.csv',

'text/csv',

csvData

);

gs.info('CSV attached to email: ' + emailSysId);

}

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

vaishali231 · 3 weeks ago

Hey @Lakshmi53

I would approach this by keeping the Scheduled Job responsible for fetching and grouping the RITMs, and using the event only to pass the recipient and report data to the Notification.

One important point in the current script is that all RITMs are collected into one array and then the same CSV is sent to all financial approvers. If the expectation is that each approver should receive only their own RITMs, the data should first be grouped by approver.

1. Scheduled Job

I would use an object for grouping instead of maintaining a separate recipient array.

var encodedQuery = gs.getProperty('ritm.monthly_report');

var ritmGR = new GlideRecord('sc_req_item');
ritmGR.addEncodedQuery(encodedQuery);
ritmGR.query();
var approverData = {};
while (ritmGR.next()) {
    var financialApprovers = ritmGR.variables.financial_approvers + '';
    if (!financialApprovers) {
        continue;

    }
    var approverList = financialApprovers.split(',');
    for (var i = 0; i < approverList.length; i++) {
        var approver = approverList[i].trim();
        if (!approver) {
            continue;
        }
        if (!approverData[approver]) {

            approverData[approver] = [];

        }

        approverData[approver].push({

            number: ritmGR.getValue('number'),

            requested_for: ritmGR.getDisplayValue('requested_for'),

            created: ritmGR.getDisplayValue('sys_created_on'),

            approval: ritmGR.getDisplayValue('approval'),

            created_by: ritmGR.getValue('sys_created_by')

        });

    }

}




/*

 * Create one event per approver

 */

for (var approver in approverData) {

    if (approverData[approver].length > 0) {
        gs.eventQueue(

            'monthly.report',

            null,

            approver,

            JSON.stringify(approverData[approver])

        );

    }

}

Now, if the data is:

RITM001 -> John

RITM002 -> John

RITM003 -> Mary

RITM004 -> David

the events will be:

Event 1

parm1 = John

parm2 = RITM001, RITM002

Event 2

parm1 = Mary

parm2 = RITM003

Event 3

parm1 = David

parm2 = RITM004

This is much cleaner than sending one large report to everyone.

2. Event

Create:

Event name: monthly.report

Table: Global / appropriate event table

Parm 1: Recipient

Parm 2: JSON report data

The important part is that parm1 contains the user who should receive that particular report.

3. Notification

Configure the Notification to trigger when:

Event is fired

monthly.report

You don't need to hard-code the users in the Notification because the recipient is dynamically passed through event.parm1.

4. Mail Script

Your CSV generation logic is basically correct. I would use the following version:

(function runMailScript(current, template, email, email_action, event) {
    var recipient = event.parm1 + '';
    var ritmInfo = [];
    try {
        ritmInfo = JSON.parse(event.parm2 + '');
    } catch (e) {
        gs.error(
            'Monthly RITM Report - JSON parsing failed: ' +
            e.message

        );
        return;
    }

    if (!recipient || !ritmInfo || ritmInfo.length == 0) {
        return;
    }
    /*
     * CSV escaping
     */

    function escapeCSV(value) {
        if (value === null || value === undefined) {
            value = ''";
        }
        value = String(value);
        return '"' + value.replace(/"/g, '""') + '"';
    }
    /*

     * CSV Header

     */

    var headers = [

        'Number',

        'Requested For',

        'Created',

        'Approval',

        'Created By'

    ];

    var csvData = headers

        .map(escapeCSV)

        .join(',') + '\r\n';

    /*

     * CSV Data

     */

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

        var row = [

            ritmInfo[i].number,

            ritmInfo[i].requested_for,

            ritmInfo[i].created,

            ritmInfo[i].approval,

            ritmInfo[i].created_by

        ];

        csvData += row

            .map(escapeCSV)

            .join(',') + '\r\n';

    }

    /*

     * Set recipient

     */

    email.setTo(recipient);
    /*

     * Attach CSV

     */

    var emailSysId = email.getSysID();

    if (!emailSysId) {

        gs.error(

            'Monthly RITM Report - Unable to get sys_email Sys ID'

        );

        return;

    }

    var attachment = new GlideSysAttachment();
    attachment.write(

        'sys_email',

        emailSysId,

        'Monthly_RITM_Report.csv',

        'text/csv',

        csvData

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

5. One thing to verify

Your property:

gs.getProperty('ritm.monthly_report');

should return a query that specifically identifies last month's RITMs.

For example:

sys_created_onONLast month@javascript&colon;gs.beginningOfLastMonth()@javascript&colon;gs.endOfLastMonth()

I would keep this in a system property rather than hard-coding the date logic in the Scheduled Job.

*************************************************************************************************************************************

If this response helps, please mark it as Accept as Solution and Helpful.

Doing so helps others in the community and encourages me to keep contributing.

Regards

Vaishali Singh

Servicenow Developer
Linkedin - https://www.linkedin.com/in/vaishali-singh-2273361bb