Here is the script to generate a .csv file in ServiceNow.
The example below demonstrates the usage of the script in a Flow Designer -> Action.
Flow Action Script:
(function execute(inputs, outputs) {
outputs.generated_attachment_sysid = ''; //Output
var startDate = inputs.startDate;
var endDate = inputs.endDate;
var grXGlidRec = new GlideRecord('custom_table');
grXGlidRec.addEncodedQuery("accepted_dateBETWEENjavascript:gs.dateGenerate('" + startDate + "','start')@javascript:gs.dateGenerate('" + endDate + "','end')");
grXGlidRec.orderBy('sys_updated');
grXGlidRec.query();
// Initialize a variable to store CSV data
var csvData = '';
// Loop through the records and add data to the CSV
while (grXGlidRec.next()) {
csvData += '"';
// NAME
var frstName = grXGlidRec.applicant.first_name.toString();
var lstName = grXGlidRec.applicant.last_name.toString();
// middle_name - NOT USING
if (frstName && lstName) {
csvData += lstName.toUpperCase() + ", " + frstName.toUpperCase();
} else if (frstName && !lstName) {
csvData += frstName.toUpperCase();
} else if (!frstName && lstName) {
csvData += lstName.toUpperCase();
}
csvData += '",';
csvData += '"';
// ADDR
if(grXGlidRec.getValue('mailing_address')){
// Get the mailing address and escape any double quotes inside it
var mailingAddress = grXGlidRec.getValue('mailing_address').toUpperCase().replace(/"/g, '""'); // Replace double quotes with two consecutive double quotes
csvData += mailingAddress; // Enclose the field in double quotes
}else{
csvData += '';
}
csvData += '",';
csvData += '"';
// STATE
csvData += grXGlidRec.getValue('state');
csvData += '",';
csvData += '"';
// ZIP
csvData += grXGlidRec.getValue('zip_code');
csvData += '",';
csvData += '"';
// BIRTHDATE
var originalDOBDate = grXGlidRec.getValue('date_of_birth').toString();
if (originalDOBDate) {
var formattedDOBDate = originalDOBDate.replace(/-/g, ""); // Remove hyphens from the original date - YYYYMMDD
csvData += formattedDOBDate;
}
csvData += '",';
csvData += '"';
// Gender
csvData += grXGlidRec.getValue('gender');
csvData += '",';
csvData += '"';
// SSN
// Get the 'social_security' value from the GlideRecord
var socialSecurity = grXGlidRec.getDisplayValue('social_security');
var desiredLengthForSSN = 9; // Desired length for the social security value (including leading zeros)
var paddedSocialSecurity = socialSecurity.toString(); // Pad Leading zeros if needed
while (paddedSocialSecurity.length < desiredLengthForSSN) {
paddedSocialSecurity = '0' + paddedSocialSecurity;
}
csvData += paddedSocialSecurity;
csvData += '"';
csvData += '\r\n';
}
//var fileName = 'SAP_ApplicantCreationRequest_ServiceNowToSFTP.csv';
var fileName = 'CSVFileName.csv';
// Attach anywhere for temp
var grRec = new GlideRecord('sys_properties');
grRec.addQuery('sys_id', gs.getProperty(sysProp));
grRec.query();
if (grRec.next()) {
var grAttachment = new GlideSysAttachment();
outputs.generated_attachment_sysid = grAttachment.write(grRec, fileName, 'text/csv', csvData);
}
})(inputs, outputs);