While it can be quick and dirty to export data from ServiceNow into Excel, that data quickly becomes stale and disconnected from its source. If you prefer to have a refreshable connection between Excel and ServiceNow, you can make use of the web services available in ServiceNow to make the connection.
These instructions are written using Excel 2016 and the Geneva edition of ServiceNow.
- Locate the name of the table in ServiceNow that you want to pull data from. If you're a developer, you can look these up directly. Regular users can do this in a couple of different ways. The easiest is to create a new report from the "View / Run" module of the Reports application. There is a field there to select a source table. The name of the table in square brackets is the name of the table you'll use in subsequent steps.
- Open a blank workbook in Excel.
- From the ribbon, find the Data tab and choose New Query, From Other Sources, From Web.
- For the URL, put in https://<instance>/api/now/table/<tablename>?sysparm_limit=10
Replace <instance> with the name of your own instance (e.g. dev01.service-now.com) and <tablename> with the table you want to query (e.g. incident). The first query parameter will just limit your query to 10 rows while you are testing to avoid pounding your server while you learn what you're getting back. You can change to any number, or remove that parameter altogether. - The first time you try to access a particular domain, you will be asked how you want to authenticate to that resource. You'll probably want to choose Basic authentication and put in a username and password. These will be stored within the Excel document, which means they are only as secure as the document itself, so use caution.
- Assuming you can successfully authenticate, you will see the Query Editor. The result in a single object which is a List. Click on the link List to expand it.
- In this example, I used sysparm_limit=10 so that I got 10 rows from my incident table. If I were to click on an individual record in this list, I would see all the attributes of that record. But what I'm really interested in is to see all those attributes as columns in a table view. To get that, I need to convert the query response into a table. Use the To Table button on the ribbon to do this. Just accept the defaults on the dialog box that pops up and click OK.
- Once you've converted to a table, you have a magical button to expand records into columns, to the right of each expandable column. When you click that button, you can select which columns should pop out of the expansion and if they should have a prefix from the old column name.
- Once you have the records you are looking for, you click Close & Load in the Query Editor and you'll be returned to your spreadsheet with all the data in a nice table that you can manipulate in the usual Excel fashion.
You can repeat the query for multiple tables and relate data together in Excel in ways that are hard to do with the normal ServiceNow reporting tools. (I love PivotTables.)
You can explore more ways to manipulate the URL string for your original query by using the Rest API Explorer in ServiceNow if you have enough rights on your instance. There are additional query parameters for the query and for the display values that are handy to play with.
Of particular value is sysparm_query where you can filter the records before you pull them down into Excel. This is especially helpful for very large tables. You can learn the syntax of the sysparm_query parameter by building filters in the regular ServiceNow user interface and seeing how those translate into URLs. For example, sysparm_query=active%3Dtrue will limit to just the active records on the table. The full query URL might look like this:
https://<instance>/api/now/table/<tablename>?sysparm_limit=10&sysparm_query=active%3Dtrue
Anytime you're working with your spreadsheet, you can go to the Data tab on the ribbon and choose Refresh All to get the most current information out of your ServiceNow instance.
I haven't been using this for very long, so I'm open to feedback about this procedure and how it can be improved or better documented.
Hi all,
I tried to set up the connection to my Excel 2016 and it seems I can pass the authentication with Excel however once the query should happen I get this error:
If I use the query in my browser I get an answer, regardless of the query.
I assume it does something to do with the format of the answer or the query type (used Web as proposed) but I'm not that experienced on this field so would need your help to get this sorted.
Many thanks,
Adam
