How to integrate with Microsoft Excel using the REST API
REST API provides an easy way to extract data from TargetProcess and to put it to Microsoft Excel. Excel then gives lots of options to report on this data.
For example, to get a report for User Stories, you should use the following query: http://targetprocess.mycompany.com/api/v1/UserStories
That’s the generic formula for such queries:
[your full TP address]/api/v1/[entity type]
Your next step would be to tell the REST API which project you wish to report on. If you want to retrieve data from all of your projects, just skip this step. TargetProcess knows which projects you have selected based on your application context ID. Select your desired projects and browse to the Dashboard. You can then find the application context ID in your URL:
Take this part out of the URL and place it at the end of your REST API query. Following our example, the query should look like this:
http://targetprocess.mycompany.com/api/v1/UserStories?acid=56B47A374989E87FC3C87D817A69C46E
Now as you browse to the newly built REST API URL, that’s about the output you’re supposed to get:
We are now ready to import this data into Excel. Within Microsoft Excel, go to the Data tab and click From Web:
In the New Web Query window, enter your REST API URL into the Address bar and click Go. You should then see the same output as in the browser before. Click the Yellow arrow at the top of the output window and the click Import:
If a window pops up stating that the XML source does not include schema information, just click OK:
Excel will then grab the information from TargetProcess and import it to a table inside your worksheet. A window will pop up asking you where you would like the new data placed. Most often, if this is a brand new Excel worksheet, you can leave the defaults and click OK - though you may wish to place the data elsewhere, if desired.
Your data will now be in Excel, available to build pivot tables, graphs, and more.
Your worksheet can then be saved and re-used every time you need to build your reports. The web query will refresh automatically every time the worksheet is opened. Alternately, you can refresh the data by clicking the “Refresh All” button in the Data tab:
