Generate Excel file and attach in email notification

[Bharat Tagad]

Hi community,

I have a scenario where we are moving some records based on flag values. If flag value is given to be yes then record will move to next bucket. At the same time users in the assignment group of that bucket receives the email notification and the total count of records with clickable link. 

But now our client is asking like we want these records in Excel file and it should be attached to the email notification as an attachment.

So is there any way to achieve this...?

Experts, please share  your insights over here...

Thanks in advance 🙂 

[dgarad]

HI @Bharat Tagad 

1) attach the excel of record based on some update etc to current record before the notification is sent

2) trigger notification by event queue() method once file is inserted into sys_attachment table

3) your event, notification would be on your table

Using CSV would be easier instead of Excel

Generate CSV file through script

[Bharat Tagad]

Hi @dgarad ,

Thank you for early response.

We have already an email script and notifications created.

Can we achieve this by adding the required script in email script itself?

And how I can add the records in excel file from table based on some condition, is there any sample script for reference?

CSV will be easier but again from our client perspective it will be complicated as they have to upload this excel file back into the ServiceNow instance by modifying some field values.

[dgarad]

you can use an email script.

please refer to the code change as per your re

var recGr = new GlideRecord('<Table_Name_0f_RecorId_goes_here>');
recGr.get("'sys id ");

//Prepare CSV data
var csvHeader = ['Request No.', 'Price'],
csvData = '';
fileName = 'Request Information.csv',
theTable = "sc_request";  //  change table table name

//Prepare CSV Header
for(var i = 0; i < csvHeader.length; i++) {
        csvData += '"' + csvHeader[i] + '"' + ',';
    }

    csvData += '\r\n';

//Prepare records
var reqInfo = new GlideRecord(theTable);
reqInfo.addEncodedQuery('active=true');
reqInfo.query();
while (reqInfo.next()) {
    csvData += '"' + reqInfo.getValue('number') + '"' + ',' + '"' + reqInfo.getValue('price') + '"' + '\r\n';
}
//gs.print(JSON.stringify(tableau));

writeAttachmentFile(recGr, fileName, csvData);

function writeAttachmentFile(recObj, fileName, data) {
    var grAttachment = new GlideSysAttachment();
	grAttachment.write(recObj, fileName, 'application/csv',data);
}