Integration between Targetprocess and Microsoft Excel via REST API
Data from Targetprocess can be extracted and displayed in Microsoft Excel. The integration is based on REST API queries. Excel gives lots of further options to report on the data.
Before you start building your first integration, please learn basic principles of Targetprocess REST API usage. They are described on our Developers portal: REST API v.1 Getting Started.
The integration is rather a workaround than a fully fleshed export solution. You can use our natively integrated export functionality instead to get a proper file export.
For example, to get a report for User Stories, you should use the following query:
That’s the generic formula for such queries:
[your full Targetprocess address]/api/v1/[entity type]
Your next step would be to add some REST API filters, for example to select which projects you wish to report on, which states you're interested in, and so on. If you want to retrieve data from all of your projects, just skip this step.
Find more about filters in REST API: Filtering.
For example to get User Stories for Project #1736 you'll use an URL like this:
https://targetprocess.mycompany.com/api/v1/userstories?where=(Project.Id eq 1736)&take=1000
You can get up to 1000 items in a single request. Should you require to load more entities, please see how: Paging.
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.
You may experience some strange additional colums (like "nil1") or columns without a proper header (like "nameXY"). It is expected because of existing limitations of built-in Excel XML import wizard.
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:
It is also possible to include more advanced REST API techniques (such as filtering, appended fields and collections) when integrating with Excel. For more information please refer to Targetprocess REST API documentation.
If you prefer cloud-based solutions, learn how to fetch Targetprocess data to Google Spreadsheets document.