7 Replies · Latest reply on Jul 21, 2017 8:59 AM by derocheb

    Excel Spreadsheets with Live Connection to ServiceNow

      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.

       

      1. 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.
      2. Open a blank workbook in Excel.
      3. From the ribbon, find the Data tab and choose New Query, From Other Sources, From Web.
        excel1.pngexcel2.png

      4. 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.
      5. 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.
        excel3.png
      6. 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.
        excel4.png
      7. 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.
        excel5.png
      8. 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.
        excel6.png
      9. 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.

        • Re: Excel Spreadsheets with Live Connection to ServiceNow
          mrswann

          indeed the building of filters when you right click the breadcrumbs you get an option to copy query !

           

          one thing which that doesn't hold is the order by parms but there is a nice wiki on querying by URL parameters where you can build a set of ordering so it's not just one column

           

          not sure if it's best to pull down ordered data, or order in excel - if you limit the rows it may be significant

           

          nice tutorial thank you

          • Re: Excel Spreadsheets with Live Connection to ServiceNow
            Casper Thomsen

            Really nice tutorial. When I extract from our Change table I seem to receive a lot of columns where they say 'Record' for example the 'Requested by' field. It appears to be any field, where the Change Record pulls data from another table. Any suggestions how to handle this?

              • Re: Excel Spreadsheets with Live Connection to ServiceNow
                Bradley Ross

                I've occasionally seen it happen that the column expansion button mentioned in step 8 is missing. This appears to be a glitch in the software. I've solved it in two ways. First, I've recreated from the query from scratch and, for unknown reasons, the expansion button will be there the second time. I've also edited the query in the advanced editor. (There is a button in the ribbon to do this.) You have to mimic the way the query works when you were successfully able to expand a column. This is tricky to get right, but is the only workaround I've found in those few cases where the expansion button won't appear.

              • Re: Excel Spreadsheets with Live Connection to ServiceNow
                Bill Leahy

                Hi..do you or anyone know if there are "Security" or "Compliance" concerns with have access to the ServiceNow back end via Excel?  I already have access to reporting for the tables that I wish to have access to, but my administrator is restricting access to this and ODBC, because he claims:

                 

                "This may pose a security or compliance concern due to sensitive data that may be accessible through this type of integration."

                 

                I think this is a made up excuse....any thoughts?

                 

                Thanks,

                 

                Bill

                 

                  • Re: Excel Spreadsheets with Live Connection to ServiceNow
                    Bradley Ross

                    The ServiceNow web services rely on the same security controls as the rest of the system. If the user account you are using to pull data into Excel has rights to see the data through a web service call, it can be accessed through the web user interface as well.

                     

                    If you have access to the web user interface, you probably have access to use the built in reporting tools and the built in exporting tools (Excel, CSV, PDF). Thus, using the web service to access the data doesn't change your ability to pull the data into Excel. This method merely makes it more convenient to have fresh data with fewer steps.

                     

                    I recognize that there are different levels of sensitivity around different sets of data. .We've decided that our ITSM data in ServiceNow is too valuable to keep it locked up inside the tool. We encourage our users to build interesting reports and alerts so that we can use the data to improve our services. Otherwise, why pay for the platform in the first place?

                  • Re: Excel Spreadsheets with Live Connection to ServiceNow
                    derocheb

                    Great tutorial.

                     

                    I had a question. I'm connecting to a ServiceNow table succesfully, but it's only exporting out the 20 line items. How do I get it to export the entire table of data or the first X number of lines?