Need help to generate a csv file and attach it to a record?

Elena10 · ‎05-25-2022

Hello,

I must create a button on the Request form. When you click on this button it should generate an Excel file (.csv format) with some informations from Request (price and request number).

So to do this I created an UI action. I checked it in the background script and I saw that it finds the necessary informations from the array (price, request number), but it doesn't generate an Excel file.

var recordId = "c4c75ae58741c9502e93c8490cbb3529";//Testing on one record but should be dynamic.

var tableau = [];
var theTable = "sc_request"; 
var reqInfo = new GlideRecord(theTable);
reqInfo.addEncodedQuery('active=true');
reqInfo.query();
while (reqInfo.next()) {

    var reqObj = {};
    reqObj.price = reqInfo.price.toString(),
        reqObj.number = reqInfo.number.toString();

    tableau.push(reqObj);

}
//gs.print(JSON.stringify(tableau));

writeAttachmentFile(tableau);

function writeAttachmentFile(data) { 
       var attachment = new Attachment(); 
       var attachmentRec = attachment.write(theTable, recordId, "export.csv", "text/csv", data);
}

Can somebody help me with it? I am new to coding.

Thank you,

Elena

Kartik Sethi · ‎05-25-2022

Hi @Elena

To use Attachment API you need to provide the data in String format and you were sending an object. Since you want a .csv file the below-provided code might help you:

var recordId = "c4c75ae58741c9502e93c8490cbb3529";

var recGr = new GlideRecord('<Table_Name_0f_RecorId_goes_here>');
recGr.get(recordId);

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

//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);
}

Please mark my answer as correct if this solves your issues!

If it helped you in any way then please mark helpful!

Thanks and regards,

Kartik

View solution in original post

Ankur Bawiskar · ‎05-25-2022

@Elena

there is a nice article for this from asifnoor

I would insist to use CSV file as excel cannot be generated directly within ServiceNow platform

Have a look and check

Generate CSV file through script

Regards
Ankur

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

Elena10 · ‎05-30-2022

Hello Ankur,

Thank you for the article, it is super useful.

Best wishes,

Elena