Objective: In this post, you will learn how to pull Jira data using the Jira API and extract work log data for tasks specifically using Google AppScript.
To know more about Jira data extraction, refer to this community post.
Steps to follow:
Get Jira API Token: Generate an API token from your Jira account.
props.gs
const email = "your-email@example.com";
const API_TOKEN = "your-api-token";
const userCredentials = "Basic " + Utilities.base64Encode(email + ":" + API_TOKEN);
const SHEET_ID = "your-google-sheet-id";
Replace your-email@example.com, your-api-token, and your-google-sheet-id with your actual email, Jira API token, and Google Sheet ID respectively.
main.gs
// Function to retrieve Tickets from Jira
function datapull() {
var query = 'project={{jira_project_name}}';
var startAt = 0;
var maxResults = 100; // Max issues the API allows per call, can be set lower
var total = 0;
var options = {
'method': 'get',
'contentType': 'application/json',
'headers': {
'Authorization': userCredentials
},
'muteHttpExceptions': true
};
var main_array = [];
var labels_array = [];
do {
const url = `https://your-domain.atlassian.net/rest/api/3/search?maxResults=${maxResults}&startAt=${startAt}&jql=` + encodeURIComponent(query);
var response = UrlFetchApp.fetch(url, options); // API call
if (response.getResponseCode() === 200) {
var data = JSON.parse(response.getContentText());
total = data.total; // Total number of issues to fetch
data.issues.forEach(item => {
var issueKey = item.key;
var parentLink = item.fields.parent ? 'https://your-domain.atlassian.net/browse/' + item.fields.parent.key : '';
var labels = item.fields.labels.join(", ");
var worklogUrl = 'https://your-domain.atlassian.net/rest/api/3/issue/' + issueKey + '/worklog';
var worklogResponse = UrlFetchApp.fetch(worklogUrl, options);
var worklogs = [];
if (worklogResponse.getResponseCode() === 200) {
var worklogData = JSON.parse(worklogResponse.getContentText());
worklogData.worklogs.forEach(log => {
var author = log.author.displayName;
var timeSpent = log.timeSpent;
var comment = log.comment;
var commentText = '';
if (comment && comment.content && Array.isArray(comment.content)) {
commentText = comment.content.map(contentPiece => {
if (contentPiece.content && Array.isArray(contentPiece.content)) {
return contentPiece.content.map(innerPiece => innerPiece.text).join(' ');
}
return '';
}).join(' ');
}
var startedDate = new Date(log.started).toLocaleDateString();
worklogs.push(author + ': ' + timeSpent + ' (' + commentText + ') [' + startedDate + ']');
});
}
var bucketType = item.fields.customfield_10102 ? item.fields.customfield_10102.value : 'N/A';
main_array.push([
'https://your-domain.atlassian.net/browse/' + issueKey,
issueKey,
item.fields.summary,
item.fields.assignee ? item.fields.assignee.displayName : '',
item.fields.status.name,
item.fields.parent ? item.fields.parent.fields.summary : '',
parentLink,
item.fields.aggregatetimespent ? item.fields.aggregatetimespent : 0,
item.fields.customfield_10103 ? item.fields.customfield_10103.value : '',
item.fields.created ? new Date(item.fields.created) : '',
worklogs.join('~ '),
bucketType
]);
labels_array.push([labels]);
});
startAt += data.issues.length; // Prepare for the next page
} else {
console.error('Error fetching data. Status code:', response.getResponseCode());
console.error('Response content:', response.getContentText());
break; // Exit loop on fetch error
}
} while (startAt < total);
if (main_array.length > 0) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SL');
sheet.getRange(2, 1, main_array.length, main_array[0].length).setValues(main_array);
sheet.getRange(2, 19, labels_array.length, labels_array[0].length).setValues(labels_array);
console.log(main_array[0]);
} else {
console.log("main_array is empty");
}
}
Replace your-domain with your actual Jira domain and {{jira_project_name}} with actual project name.
Set Trigger for the Script: Go to the Triggers section in AppScript. Set a trigger to run the script datapull as per your business requirements.
Conclusion: By following these steps, you can effectively pull Jira data and extract work log information for tasks using the Jira API and Google AppScript. This automation can save time and improve accuracy in tracking and reporting work logs.
