How to return a CSV file from a Scripted REST API (SRAPI) in ServiceNow

I recently dealt with a requirement whereby I needed to export only certain columns from records on a given table that match a given query, to a CSV file.

“No problem”, I said; “that’s easy. You just add ‘?CSV’ to the end of the table URL.”

Oh, but it’s never that easy, is it? In this case, there were a few odd stipulations:

  • The header names need to be specific strings (not the same as their column names in ServiceNow)

  • Some of the columns need to have calculated or static values; values not in ServiceNow (and we don’t want to make calculated columns for them)

  • The endpoint must not return CSV data embedded in a JSON response body, but must actually return a CSV file. Or to put it more precisely, the endpoint URL must resolve to a CSV file.

Because of the nature of this requirement, I was going to need to create a Scripted REST API (SRAPI); so I did.

Failures

First, I tried simply returning from the SRAPI script, some comma-delimited data like so:

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
var responseArr = [];
var headerArr = ['Assignee name', 'CI name', 'Some random number'];
var grInc = new GlideRecord('incident');
responseArr.push(headerArr.join(','));
grInc.addQuery('some_query_here');
grInc.setLimit(2);
grInc.query();
while (grInc.next()) {
//Add a CSV-joined array of this record's data to the response array.
//These elements will constitute "rows" in the returned CSV.
responseArr.push([
grInc.assigned_to.getDisplayValue(),
grInc.cmdb_ci.getDisplayValue() || '(No CI)',
(Math.floor((Math.random() * 100)) + 1) //Random integer from 1-100
].join(','));
}
//Join the response array with a line-break between each comma-delimited row and return
return responseArr.join('\n');
})(request, response);
view raw non-working with return.js hosted with ❤ by GitHub

When I hit the SRAPI URL, of course it didn’t work. I didn’t really expect it to, but figured it worth a try.
Instead, I saw the following:

Pro-tip: You can hit a SRAPI from your browser using basic auth, like so:
https://user_name:pass_werd@myInstance.service-now.com/api/123/my_api/endpoint

So that obviously didn’t work. For some reason, ServiceNow is converting it to an XML file, and putting whatever I return into the response.result node.

It’s a long-shot, but what if I go into the Content Negotiation section of the SRAPI form, check the Override supported response formats checkbox, and set the Supported response formats field to just “text/csv”…

Nope. That did nothing.

Okay, what if I explicitly add a line of code like response.setHeader(‘Content-Type’, ‘text/csv’);?

Nope, still didn’t do anything. Still returning an XML file. Wow, ServiceNow is really aggressive with its XMLization of everything, isn’t it?

The Simple (Insecure) Solution

So I did a little digging, and eventually found the following solution, using response.setHeader(‘Content-Type’, ‘text/csv’) (line 24), and response.getStreamWriter().writeString() (line 27):

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
var writer;
var responseArr = [];
var headerArr = ['Assignee name', 'CI name', 'Some random number'];
var grInc = new GlideRecord('incident');
responseArr.push(headerArr.join(','));
grInc.addQuery('some_query_here');
grInc.setLimit(2);
grInc.query();
while (grInc.next()) {
//Add a CSV-joined array of this record's data to the response array.
//These elements will constitute "rows" in the returned CSV.
responseArr.push([
grInc.assigned_to.getDisplayValue(),
grInc.cmdb_ci.getDisplayValue() || '(No CI)',
(Math.floor((Math.random() * 100)) + 1) //Random integer from 1-100
].join(','));
}
response.setHeader('Content-Type', 'text/csv');
writer = response.getStreamWriter();
//Join the response array with a line-break between each comma-delimited row and write response.
writer.writeString(responseArr.join('\n'));
})(request, response);
view raw working with stream.js hosted with ❤ by GitHub

Hitting this SRAPI endpoint from a browser (or other tool) will now directly resolve to a CSV file.

But wait… What if the retrieved value contains commas? Or new-line characters? Or quote-marks? Those are CSV control characters, so we’ll need to deal with them.

We can handle commas and new-line characters by simply wrapping each cell’s data in double-quotes; but first, we have to escape any double-quote characters already in the original string. While CSV has no strict standard (even RFC4180 “does not specify an internet standard of any kind”), the generally accepted way to escape a quote, is with another quote (““), so we’ll do that.

function getEscapedFieldVal(fieldVal) {
//Escape quotes.
if (fieldVal.indexOf('"') >= 0) {
fieldVal = fieldVal.replaceAll('"', '""');
}
//Wrap field value in quotes (eliminates need to escape commas/new-line characters).
fieldVal = '"' + fieldVal + '"';
return fieldVal;
}
view raw escape_csv.js hosted with ❤ by GitHub

Alright, that’s solved. But wait! By default, anyone that isn’t an external user (with the snc_external role) can hit a REST API endpoint and get what it returns… Because GlideRecord ignores ACLs, this would allow any internal user to access pretty much any data in the entire system! We certainly don’t want that!

Let’s add some logic to check that the API user can actually see the specified record, and every column we’re exporting.
If they can’t see the record, we’ll skip it and try the next one while logging a warning. If they can see the record (meaning that they have table and record-level access) but not a specific requested column, then we’ll log a warning, set that column’s value to a blank string for that record, and continue to the next column.

Finally, we’ll push the escaped field value into our currentRow array, for inclusion in the final CSV.

The Robust Solution

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
var i,
streamWriter,
grComputer,
tableName,
columnHeaders,
tableQuery,
queryLimit,
currentRow,
snFieldName,
snFieldVal;
var responseArr = [];
var fileName = 'sn-data-export.csv';
var mapObj = getMapObjectFromURL(request);
if (
!mapObj.hasOwnProperty('table_name') ||
!mapObj.hasOwnProperty('columns')
) {
throw new Error(
'Unable to parse map object. Map must be provided as a URI parameter with the ' +
'name "map". Map URI parameter must be equal to a JSON object with a node ' +
'called "table_name" with the name of the table from which you\'d like to pull ' +
'the returned data, and an a node called "columns" which consists of an array ' +
'of objects, each with two nodes: "destination_column_name" and ' +
'"source_column_name".'
);
}
tableName = mapObj.table_name.toString();
columnHeaders = getHeadersFromMap(mapObj);
//Push the column headers into the response array as the first element, in CSV format.
responseArr.push(columnHeaders.join(','));
if (!gs.tableExists(tableName)) {
throw new Error(
'Table with name ' + tableName + ' does not exist in ServiceNow, or is not ' +
'accessible. Are you sure you\'ve entered the table name and not the label?'
);
}
grComputer = new GlideRecord(tableName);
tableQuery = (mapObj.hasOwnProperty('table_query')) ? mapObj.table_query.toString() : '';
queryLimit = parseInt(mapObj.query_limit);
//If query is provided, add it. Ditto for record query limit.
if (tableQuery) {
grComputer.addEncodedQuery(tableQuery);
}
if (queryLimit && queryLimit > 0) {
grComputer.setLimit(queryLimit);
}
grComputer.query();
//Here be dragons
while (grComputer.next()) {
//Check if user can read the found record
if (!grComputer.canRead()) {
gs.warn(
'CSV Export: Record with sys_id ' + grComputer.getValue('sys_id') +
' in table "' + tableName + '" cannot be read by the current user (' +
gs.getUserID() + '). Skipping this record, and continuing to the next one.'
);
continue;
}
currentRow = [];
//For each column in the map object...
for (i = 0; i < mapObj.columns.length; i++) {
//If map for this column is malformed, log a warning and continue.
if (!mapObj.columns[i].hasOwnProperty('source_column_name')) {
throw new Error(
'CSV Export: One or more columns in the export map object is mising the ' +
'"source_column_name" property. The export process must abort.'
);
}
//Get the next field name to work on
snFieldName = mapObj.columns[i].source_column_name.toString();
//If user can read the value of the specified column, get it. If not, log a warning
// but continue anyway with a blank value for this column.
if (grComputer[snFieldName].canRead()) {
snFieldVal = grComputer.getValue(snFieldName);
} else {
snFieldVal = '';
gs.warn(
'CSV Export: API user ' + gs.getUserID() + ' is unable to read the ' +
'column ' + snFieldName + ' from the record in table ' + tableName +
' with sys_id ' + grComputer.getValue('sys_id') + '. Exporting a ' +
'blank value for this field, and continuing with export.'
);
}
//If all is well, push the escaped value of the specified field into currentRow.
//currentRow will be joined at the end of this for loop, at the bottom of the while.
currentRow.push(
getEscapedFieldVal(snFieldVal)
);
}
//Join the current row into csv format, and push that value into the response array.
responseArr.push(currentRow.join(','));
}
response.setHeader('Content-Type', 'text/csv');
response.setHeader('Content-disposition', 'attachment; filename=' + fileName);
streamWriter = response.getStreamWriter();
//Join the response array with a line-break between each comma-delimited row and write response.
streamWriter.writeString(responseArr.join('\n'));
//Helper functions
function getEscapedFieldVal(fieldVal) {
//Escape quotes.
if (fieldVal.indexOf('"') >= 0) {
fieldVal = fieldVal.replaceAll('"', '""');
}
//Wrap field value in quotes (eliminates need to escape commas/new-line characters).
fieldVal = '"' + fieldVal + '"';
return fieldVal;
}
function getMapObjectFromURL(request) {
var mapObj;
try {
mapObj = request.queryParams['map'];
mapObj = mapObj[0];
mapObj = JSON.parse(mapObj);
} catch (ex) {
throw new Error(
'Unable to parse map object. Map must be provided as a URI parameter with the ' +
'name "map". Map URI parameter must be equal to a JSON object with a node ' +
'called "table_name" with the name of the table from which you\'d like to pull ' +
'the returned data, and an a node called "columns" which consists of an array ' +
'of objects, each with two nodes: "destination_column_name" and ' +
'"source_column_name". \n' +
'Error: ' + ex.message
);
}
return mapObj;
}
/**
* Parse out an array of headers from the map object passed into this SRAPI from the
* URI parameter "map".
* @param {Object} mapObj
* @return {*[]} An array of Strings representing the names of the columns in the CSV that
* should be output from this SRAPI.
*/
function getHeadersFromMap(mapObj) {
var i;
var headersArr = [];
var columns = mapObj.columns;
for (i = 0; i < columns.length; i++) {
headersArr.push(columns[i].destination_column_name.toString());
}
return headersArr;
}
})(request, response);
view raw robust_solution.js hosted with ❤ by GitHub

As you can see, I added a bunch more error detection and handling code in this version, as well as some additional security measures. Thanks to a pro-tip from Ben in the comments below this article, I’ve also added a line to set the resultant file name for the CSV file that this endpoint will resolve to.

This code should work well enough as-is, that you can pretty much just copy-and-paste it into your environment and be good to go!

Constructing the API URI

To construct the API call, you first have to construct the object that’ll go into the URL. This will be a JSON object with the following structure:

{
	"table_name": "some_table_name",
	"table_query": "some_field=some_value^other_field=something_else",
	"query_limit": 100,
	"columns": [
		{
			"source_column_name": "number",
			"destination_column_name": "Ticket number"
		},
		{
			"source_column_name": "assigned_to",
			"destination_column_name": "Assigned to"
		}
	]
}

In the columns array, each object has a source_column_name field which corresponds to the name of the field in ServiceNow, and a destination_column_name field, which corresponds to the name to go into the column header in the resulting CSV.

Once you’ve got the object constructed, take the SRAPI endpoint (like https://INSTANCENAME.service-now.com/api/12345/my-api/export-to-csv), and add the URI parameter ?map=, after which you can just copy-and-paste your JSON object. If you paste the object into your URL bar, it’ll remove all new-lines for you, and flatten it out.

If you want to test it out, you can add the basic auth prefix to the URL after the “https://”. That would look like: 

https://USERNAME:PASSWORD@INSTANCE_NAME.service-now.com/...

Putting it all together, the final URL might look something like this: 

https://USERNAME:PASSWORD@instance_name.service-now.com/api/12345/my-api/export-to-csv?map={%20%22table_name%22:%20%22some_table_name%22,%20%22table_query%22:%20%22some_field=some_value^other_field=something_else%22,%20%22query_limit%22:%20100,%20%22columns%22:%20[%20{%20%22source_column_name%22:%20%22number%22,%20%22destination_column_name%22:%20%22Ticket%20number%22%20},%20{%20%22source_column_name%22:%20%22assigned_to%22,%20%22destination_column_name%22:%20%22Assigned%20to%22%20}%20]%20}
Share