Integration between Targetprocess and Google Spreadsheets within REST API

Using built-in Targetprocess REST API it is possible to fetch data from your Targetprocess account to Google Spreadsheets document. The guide below helps to set up basic integration step by step.

The integration works with Targetprocess sites hosted in the cloud. It also works with on-premise (on-site) Targetprocess sites when the site is accessible via external Internet connections.

Open your Google spreadsheet or create a new one.

Press Tools -> Script editor…

Script editor

Rename the newly created project:

Rename

Copy the following source code to the editor area for Code.gs file:

function fetchTargetprocessData() {
var payload = ''; // No payload is needed by default for GET REST API v1 queries
var headers = {
 //No headers are needed by default for GET REST API v1 queries
};
 
var hostname = 'https://myaccount.tpondemand.com';
var authenticationToken = 'MTpFQUVXMHdRVGRMN0x1OXJPYWRXZWZaRkc3aDJaSkRyVWdyWm9rK2tFcldBPQ==';
var isTokenSetFromUserProfileTab = true;
//use 'true' if token is issued from User Profile > Access Tokens tab
//use 'false' when token is issued from /api/v1/authentication API endpoint
 
var takeCount = 10;
//can be increased up to 1000, to retrieve more than 1000 entities read page by page in a loop using &skip= parameter
var entityTypeResourceName = 'userstories';
//'bugs', 'tasks', 'features', 'epics', 'requests' and other resources are also supported
var filter = '';
//set custom data filter here if needed
var includeFields = '';
//set custom list of included fields if needed
var dateFormat = 'yyyy-MM-dd';
//to see times with dates, use 'yyyy-MM-dd HH:mm'

var url = hostname + '/api/v1/' + entityTypeResourceName + '?format=json' + '&take=' + takeCount + (filter.length > 0 ? '&where=' + filter : '') + (includeFields.length > 0 ? '&include=' + includeFields : '') + '&' + (isTokenSetFromUserProfileTab ? 'access_token' : 'token') + '=' + authenticationToken;
var options = {
 'method': 'get',
 'headers': headers,
 'payload': payload
};
var response = UrlFetchApp.fetch(url, options);

var json = response.getContentText();
var data = JSON.parse(json);
var entities = data.Items;

var sheet = SpreadsheetApp.getActiveSheet();
 
//initial cleanup of the sheet
sheet.clear();

//creates column names row
var entity = entities[0];
var columnNames = Object.keys(entity);

var customFieldsColumnIndex = 0;
for (var i = 0; i < columnNames.length; i++) {
 if (columnNames[i] == 'CustomFields') {
 customFieldsColumnIndex = i;
 }
} 

if (customFieldsColumnIndex > 0) {
 var entityArray = Object.keys(entity).map(function(k) { return entity[k] });
 var customFieldsData = entityArray[customFieldsColumnIndex];
 for (var k = 0; k < customFieldsData.length; k++) {
 var fieldsPairData = customFieldsData[k];
 columnNames.push(fieldsPairData.Name);
 } 
}

sheet.appendRow(columnNames);

//appends data line by line
for (var i = 0; i < entities.length; i++) {
 var entity = entities[i];
 var entityArray = Object.keys(entity).map(function(k) { return entity[k] });
 
 if (customFieldsColumnIndex > 0) {
 //format custom fields
 var customFieldsData = entityArray[customFieldsColumnIndex];
 var fieldValues = '';
 for (var j = 0; j < customFieldsData.length; j++) {
 var fieldsPairData = customFieldsData[j];
 entityArray.push(fieldsPairData.Value);
 }
 entityArray[customFieldsColumnIndex] = customFieldsData.length;
 }
 
 //add data formatting functions there
 for (var j = 0; j < entityArray.length; j++) {
 var cellValue = entityArray[j];
 if (typeof cellValue == 'string') {
 if (cellValue.indexOf("Date") > -1) {
 var milliseconds = cellValue.substring(6, cellValue.length - 7);
 var originTimeZone = cellValue.substring(cellValue.length - 7, cellValue.length - 4);
 var dateObject = new Date(parseInt(milliseconds) + originTimeZone * 1000 * 60 * 60);
 entityArray[j] = Utilities.formatDate(dateObject, originTimeZone, dateFormat);
 }
 }
 }
 sheet.appendRow(entityArray);
}
}

Replace hostname and authentication token values with the ones specific to your own customer account with Targetprocess:

accountname

FAQ: How to obtain a token?
For isTokenSetFromUserProfileTab parameter, use 'true' if token is issued from User Profile > Access Tokens tab and use 'false' when token is issued from /api/v1/authentication API endpoint.

Select the function named fetchTargetprocessData in the dropdown for execution:

run-function

Press Run:

Run

Grant this script with requested permissions:

Review Permissions

Allow

Success! Your spreadsheet is now filled in with Targetprocess entities:

us-list

Count of entities in responce

In the demo sample we retrieve first 10 matching user stories. Number of entities we query is encoded in the script header:

var takeCount = 10;

It is possible to increate takeCount parameter up to 1000.

If you have more than 1000 entities that match your query, you have to create a script that makes multiple API calls using paging parameters and then merges retrieved data.

Filtering

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 on filters please refer to Targetprocess REST API filters description.

Here are examples of how the source code may be modified to support filters.

On dates range in Custom Field:

var filter = '(\'CustomFields.Next Date\' gte \'2017-01-09\') and (\'CustomFields.Next Date\' lte \'2017-01-16\')';

Further reading

Use Google Apps Script guides or contact our support team for further assistance.

Targetprocess entities can be synchronized with Google Spreadsheets documents within Zapier connector as well.

  • Rico Trevisan

    Hi, just getting started with TP online – awesome tool.

    I just followed the guide and got this error: Request failed for https://fluxys.tpondemand.com/

    More details:

    Request failed for https://fluxys.tpondemand.com/… returned code 401. Truncated server response: One or more errors occurred. (use muteHttpExceptions option to examine full response) (line 17, file “Code”)

    Line 17 is: var response = UrlFetchApp.fetch(url, options);

    Any idea what might be wrong?

  • Julia Pastushenko

    Hi Rico,

    401 error usually means that you’re not authorized.
    Did you specify the token? How did you generate it?

    Please send us the details to support@targetprocess.com for the troubleshooting.

    Best wishes,
    Julia.

  • Gabriel Tendera

    Hello.
    I’m having trouble with connecting to the server with a token issued from User Profile. When following the tutorial and running the fetchTargetProcessData function, google script editor reports an “Unexpected error (…) (line 33, file “Code”)”. The reported line contains “var response = UrlFetchApp.fetch(url, options);”

    However, when I copy the URL that is sent to UrlFetchApp.fetch and paste it to a browser – it requires me to log in with Http Authentication with my TargetProcess username and login, but after the login, a correct JSON is displayed. So I assume, either our TP configuration is off, or I need to put my username and login into the script to use a token from my User Profile.

    Can you give me tip? Do I have to enter my credentials to the script?

  • http://www.targetprocess.com/guide/ Alex

    We’ll convert your question to private ticket in our Service Desk and will continue to communicate there.

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.