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:

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):

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.

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

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}