ServiceNow CSV File Generator

The script is broken down into 4 sections:

Getting the data

Initializing our headers

Adding rows

Attaching the document to a record

Now let's identify some dependencies in our logic. A dependency would be some sort of logic that we would need to add to either perform our action or catch edge cases and prevent any errors.

GlideSystemAttachment - This will be the API we use to write the CSV file to a data record in ServiceNow.

Input Sanitation - We need a mechanism to remove any commas from the input so that it doesn't mess up the column order.

Logging - Not needed but a nice to have mechanism where we can systematically log out any errors.

GlideSystemAttachment is an out of the box API so we won't have to do anything for this just read the docs on how to use it.

Input Sanitation will require us to create an internal function that searches for the occurrences of a comma and removes it. We can perform this action by casting all the inputs to a string and using the JavaScript replace function. If this logic will need to be used multiple times we will want to wrap this in a function to make it reusable. In this case we will make a function called _cleanInput.

Logging we can simply use the gs.log or gs.info, but if we want to force some sort of consistency throughout all of our logs we can make a wrapper function (in this case _log) which will remind us what variables we should always feed to our logger for more consistent debugging methods. It can even allow us to apply logic for logging verbosity which will be covered in another article.

At the end we will have a beautiful reusable script include looking as such:

1/*
2 * Class representing a CSV generator.
3 * @constructor
4 */
5var CSVGenerator = Class.create();
6
7
8CSVGenerator.prototype = {
9    /**
10     * The CSV data.
11     * @type {string}
12     */
13    csvData: '',
14
15
16    /**
17     * Initializes the CSVGenerator with headers.
18     * @param {Array} headers - The header values.
19     */
20    initialize: function(headers) {
21        for (var i = 0; i < headers.length; i++) {
22            this.csvData = this.csvData + '"' + this._cleanInput(headers[i]) + '"' + ',';
23        }
24        this.csvData = this.csvData + "\r\n";
25        this.attach = false;
26    },
27
28
29    /**
30     * Adds a row to the CSVGenerator.
31     * @param {Array} row - The row values.
32     */
33    addRow: function(row) {
34        for (var i = 0; i < row.length; i++) {
35            this.csvData = this.csvData + '"' + this._cleanInput(row[i]) + '"' + ',';
36        }
37
38
39        this.csvData = this.csvData + "\r\n";
40        this.attach = true;
41    },
42
43
44    /**
45     * Attaches the CSV data to a record.
46     * @param {string} sys_id - The sys_id of the record.
47     * @param {string} table - The table name.
48     * @param {string} fileName - The attachment file name.
49     */
50    attachToRecord: function(sys_id, table, fileName) {
51        if (this.attach) {
52            var grRec = new GlideRecord(table);
53            grRec.get(sys_id);
54            if (grRec.isValid()) {
55                var grAttachment = new GlideSysAttachment();
56                grAttachment.write(grRec, fileName, 'application/csv', this.csvData);
57            }
58        } else {
59            this._log("No Records Found Skipping Attachment", "attachToRecord", "34");
60        }
61    },
62
63
64    /**
65     * Cleans the input by removing commas.
66     * @param {string} input - The input value.
67     * @returns {string} - The cleaned input value.
68     */
69    _cleanInput: function(input) {
70        input = String(input);
71        input = input.replace(',', '');
72        return input;
73    },
74
75
76    /**
77     * Logs a message to the console.
78     * @param {string} msg - The message to log.
79     * @param {string} func - The function name.
80     * @param {string} line - The line number.
81     */
82    _log: function(msg, func, line) {
83        var logger = new GSLog("CSVGenerator", "CSVGenerator");
84        logger.setLevel("debug");
85        logger.logDebug(func + " " + line + " " + msg);
86    },
87
88
89    /**
90     * The type of the CSVGenerator.
91     * @type {string}
92     */
93    type: 'CSVGenerator'
94};*

We can call this script include to significantly simplify the problem statement in the post as follows:

1// Create a new instance of CSVGenerator
2var csvGenerator = new CSVGenerator();
3csvGenerator.initialize(["First_name","Last_name","Location"]);
4
5var assetDetails1 = "Steve,Rogers,LA";
6var assetDetails2 = "Tony,Stark,New York";
7var fileName = 'Detail1.csv';
8
9csvGenerator.addRow(assetDetails1.split(','));
10csvGenerator.addRow(assetDetails2.split(','));
11 
12csvGenerator.attachToRecord('3cfba07d1b384010364d32a3cc4bcbca','alm_hardware',fileName);

The beauty of the script include is we can also call addRow in a while loop where you could join complex data sets together or perform scripted checks on a set of data to determine whether you wish to export it i.e. a regex pattern.

For example, let's say we have a crazy requirement where we need to identify all users whose managers display name does not start with the letter S. In this example we have 2 hard things to solve with a simple export firstly we need to join the user and manager dataset via a dot walk and secondly, we need to run a does not start with operation which does not exist in ServiceNow.

We can export this data via the following script:

1// Create a new instance of CSVGenerato
2var csvGenerator = new CSVGenerator();
3
4
5// Initialize the CSVGenerator with headers
6var headers = ["User ID", "Name", "Email"];
7csvGenerator.initialize(headers);
8
9
10// Query the User table using GlideRecord
11var userGR = new GlideRecord("sys_user");
12userGR.query();
13
14
15// Iterate over the records and add rows to the CSVGenerator
16while (userGR.next()) {
17    //String cannot start with s or S
18    var regex = /^(?!s|S).*/;
19    if(regex.test(userGR.getDisplayValue('manager')))
20    {
21        var userId = userGR.getValue("sys_id");
22        var name = userGR.getValue("name");
23        var email = userGR.getValue("email");
24
25        var row = [userId, name, email];
26        csvGenerator.addRow(row);
27    }
28}
29
30
31// Attach the CSV data to a record
32var sysId = "1234567890"; // The sys_id of the record
33var tableName = "incident"; // The table name
34var fileName = "user_data.csv"; // The attachment file name
35
36
37csvGenerator.attachToRecord(sysId, tableName, fileName);