DocsAPI ReferenceBlog
Docs

Google Apps Script

Samples of usage of Targetprocess REST API in Google Apps Script code

Suggest Edits

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications.

Using built-in Targetprocess REST API it is possible to fetch data from your Targetprocess account to Google Spreadsheets document. Update action is also supported and described below.

How to build and run your first integration script

Open your Google spreadsheet.

Press Tools > Script editor…

734

Rename the newly created project.

566

Copy the source code to the editor area for Code.gs file. Use Fetch data from Targetprocess example from the article below.

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

703

🚧

How to obtain a token?

For isTokenSetFromUserProfileTab parameter, use one of the two options. Use true if your token is issued from User Profile > Access Tokens tab in Targetprocess. Use false when the token is issued from /api/v1/authentication REST API endpoint.

Select the function named fetchTargetprocessData in the dropdown for execution.

863

Press Run.

782

Grant this script with requested permissions. This action must be done only once.

513 455

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

911

Fetch data from Targetprocess

This script connects to Targetprocess account myaccount.tpondemand.com using token-based authentication and queries list of User Stories. Details of the loaded stories are appended to the initially cleared Google Spreadsheet.

function fetchTargetprocessData() {
  
var hostname = 'https://myaccount.tpondemand.com';
var authenticationToken = 'MTpFQUVXMHdRVGRMN0x1OXJPYWRXZWZaRkc2aDJaSkRyVWdyWm9rK2tFcldBPS==';
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;
var entityTypeResourceName = 'userstories';
var filter = '';
var includeFields = '';
//use the example below to specify exact set of fields to include
//var includeFields = '[ID,Name,Project[Name],EntityState[Name],CustomFields]';

var dateFormat = 'yyyy-MM-dd';
//to see times with dates, use 'yyyy-MM-dd HH:mm';

var dataUrl = hostname + '/api/v1/' + entityTypeResourceName + '?format=json' + '&where=' + filter + '&take=' + takeCount + (includeFields.length > 0 ? '&include=' + includeFields : '') + '&' + (isTokenSetFromUserProfileTab ? 'access_token' : 'token') + '=' + authenticationToken;
var options = {
   'method': 'get'
};
var response = UrlFetchApp.fetch(dataUrl, 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);
     }
   }
   if (typeof cellValue == 'object') {
     var keys = [];
     var resourceType = '';
     var name = '';
     var items = null;
     for(var key in cellValue) {
       keys.push(key);
       if (key == "ResourceType") {
          resourceType = cellValue[key];
       }
       if (key == "Name") {
          name = cellValue[key];
       }  
     };
     if (
            resourceType == "Project" 
         || resourceType == "EntityState" 
         || resourceType == "EntityType" 
         || resourceType == "Priority" 
         || resourceType == "Feature" 
         || resourceType == "Epic" 
         || resourceType == "UserStory" 
         || resourceType == "Program" 
         || resourceType == "Release" 
         || resourceType == "Iteration" 
         || resourceType == "TeamIteration"
        ) {
        entityArray[j] = name;
     }
   }
 }
 sheet.appendRow(entityArray);
}
}

Update data in Targetprocess

This script connects to Targetprocess account myaccount.tpondemand.com using token-based authentication and creates new User Story entity in Project #2. Numeric ID, Name, Description and creation timestamp of the user story are appended to the initially cleared Google Spreadsheet.

function updateTargetprocessData() {
  var payload = {
    "Project" : {"ID" : 2},
    "Name": "Test Google Script Story Name",
    "Description": "Test Google Script Story Description"
  };
  var hostname = 'https://myaccount.tpondemand.com';
  var authenticationToken = 'MTozemxBMjI4dkNPMFVqbmlWV21WUVlSb0RYTTAzbkhQZ1lvaTJKanMvQWFBPS==';
  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 entityTypeResourceName = 'userstories';
  var resultIncludeFields = '[Id,Name,Description,CreateDate]';
  var dateFormat = 'yyyy-MM-dd HH:mm'; //to see no times with dates, use 'yyyy-MM-dd';
  
  var dataUrl = hostname + '/api/v1/' + entityTypeResourceName + '/?format=json' 
  + '&resultInclude=' + resultIncludeFields
  + '&' + (isTokenSetFromUserProfileTab ? 'access_token' : 'token') + '=' + authenticationToken;

  var options = {
   'method': 'POST',
   'payload': JSON.stringify(payload),
   'contentType' : 'application/json',
};
  
var response = UrlFetchApp.fetch(dataUrl, options);
  
var sheet = SpreadsheetApp.getActiveSheet();
  
//initial cleanup of the sheet
sheet.clear();
  
var json = response.getContentText();
var data = JSON.parse(json);
var entities = data;//data.Items; for multiple objects
  
var entity = entities;//entities[0]; for multiple objects
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);

 var entity = entities;//entities[i]; for multiple objects
 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);
     }
   }
   if (typeof cellValue == 'object') {
     var keys = [];
     var resourceType = '';
     var name = '';
     var items = null;
     for(var key in cellValue) {
       keys.push(key);
       if (key == "ResourceType") {
          resourceType = cellValue[key];
       }
       if (key == "Name") {
          name = cellValue[key];
       }  
     };
     if (
            resourceType == "Project" 
         || resourceType == "EntityState" 
         || resourceType == "EntityType" 
         || resourceType == "Priority" 
         || resourceType == "Feature" 
         || resourceType == "Epic" 
         || resourceType == "UserStory" 
         || resourceType == "Program" 
         || resourceType == "Release" 
         || resourceType == "Iteration" 
         || resourceType == "TeamIteration"
        ) {
        entityArray[j] = name;
     }
   }   
 }

 sheet.appendRow(entityArray);
}

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.

📘

Alternate Option: Zapier

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

Updated over 6 years ago