Integration between Targetprocess and Microsoft Excel via REST API | Targetprocess - Visual management software

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:

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:

Integration between Targetprocess and Microsoft Excel via REST API. Image 1

We are now ready to import this data into Excel.

Within Microsoft Excel, go to the Data tab and click From Web:

Integration between Targetprocess and Microsoft Excel via REST API. Image 2

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:

Integration between Targetprocess and Microsoft Excel via REST API. Image 3

If a window pops up stating that the XML source does not include schema information, just click OK:

Integration between Targetprocess and Microsoft Excel via REST API. Image 4

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.

Integration between Targetprocess and Microsoft Excel via REST API. Image 5

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:

Integration between Targetprocess and Microsoft Excel via REST API. Image 6

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, Plugins, Mashups and custom extensions. Join our community of passionate users and even discuss directly with our developers.