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 the 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:
https://targetprocess.mycompany.com/api/v1/UserStories
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.
You can use token authentication right in your URL as explained in our Dev Guide
Otherwise, you will be asked to provide credentials in order to access the Targetprocess account from Excel
Integration steps for new Excel UI
Navigate to the Data tab and click From Web:
Enter your REST API URL into the URL bar and click OK.
Click "Transform data" and then expand each column with "Table" value so you should then see the same output as in the browser before. Click "Load more" in order to get all the values:
Then click "Close and load" in order for data to be loaded from the preview. Other options and behavior is pretty the same as in the old Excel UI
Integration steps for oldExcel UI
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 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 columns (like "nil1") or columns without a proper header (like "nameXY"). It is expected because of the existing limitations of the 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.
Still have a question?
We're here to help! Just contact our friendly support team.
Find out more about our APIs, Automation Rules, Mashups and custom extensions.