How to Import Jira Initiatives and Epics into Google Sheets
Learn how to seamlessly import initiatives and epics from Jira into Google Sheets using the Jira API, creating a project timeline for stakeholders.
🚀 Let's get started
This guide explains how to import Jira initiatives and epics into Google Sheets using the Jira API. It focuses on generating a Jira API token, a key step in accessing project data. The tutorial will then cover using Google Sheets and Apps Script to automate the import of Jira issues, creating a shareable project timeline for stakeholders.
Click on your "profile picture" to access your profile informations
Once that's done, you will be asked to save your token.
Make sure to copy and paste it somewhere safe, as you won't be able to view the token again.
Step 2 : Google Sheet
You can customize these headers based on the information you need for your timeline.
Prepare for Data Import:
The next steps will involve using the Jira API to populate this table.
You'll need a Jira API token (as discussed previously) and will use Apps Script to connect to the Jira API and import the data.
Replace
YOUR_JIRA_DOMAIN, YOUR_API_TOKEN, and YOUR_JQL_QUERY with your actual values.
YOUR_JQL_QUERY is similar as what you can find into a JIRA advanced search and is adapted to match with JIRA API
This URL retrieves data from Jira using its API. Let's break it down:
https://jira.adeo.com/rest/api/2/search: This is the base URL for searching Jira issues using the REST API version 2.
?jql=...: This indicates the start of the JQL (Jira Query Language) query. JQL is used to filter the issues returned.
project=AMSLMPL: This part of the query filters for issues in the project with the key "AMSLMPL".
AND: This is a logical operator that combines multiple criteria. It means all conditions must be met.
status not in (Rejected, Archived): This filters for issues where the status is not "Rejected" or "Archived".
AND: Another logical operator, combining more criteria.
TYPE in (Story, Task, Sub-task): This filters for issues of type "Story," "Task," or "Sub-task."
ORDER BY status DESC, duedate DESC: This sorts the results first by status in descending order (e.g., from "In Progress" to "To Do") and then by due date in descending order (latest due date first).
Click on the "three dots" to open the parameters
You can also change the function name
Step 3 : Create a timeline into Google Sheets
Once all the objects are imported
Click "OK" to proceed.
Annexes
The code I used
YOUR_URL : "https://jira.adeo.com/rest/api/2/search?jql=project=AMSLMPL+AND+status+not+in+(Rejected,Archived)+AND+TYPE+in+(Story,Task,Sub-task)+ORDER+BY+status+DESC,+duedate+DESC"
function MYFUNCTION() {
// URL de base de l'API JIRA avec la requête JQL corrigée
var jiraUrlBase = YOUR_URL ; // Remplacez par votre URL
// Les informations d'authentification
var jiraToken = YOUR_TOKEN ; // Remplacez par votre token API
// Paramètres de la requête HTTP
var options = {
"method": "GET",
"headers": {
"Authorization": "Bearer " + jiraToken,
"Content-Type": "application/json"
}
};
// Variables pour la pagination
var startAt = 0;
var maxResults = 500;
var total = 0;
// Récupère la feuille de calcul active
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Efface les données existantes
sheet.clear();
// En-têtes des colonnes, ajout de "Business Objective", "Type", "Epic Name" et "Jira Link"
var headers = ["Key", "Jira Link", "Summary", "Status", "Assignee", "Start Date", "Due Date", "Resolved", "Epic Link", "Epic Name", "Labels", "Business Objective", "Type"];
sheet.appendRow(headers);
// Stockage des issues par Epic Link
var issuesByEpicLink = {};
// Boucle pour paginer les résultats
do {
// Construire l'URL avec les paramètres de pagination
var jiraUrl = jiraUrlBase + "&startAt=" + startAt + "&maxResults=" + maxResults;
// Effectue la requête HTTP
var response = UrlFetchApp.fetch(jiraUrl, options);
// Parse la réponse JSON
var jsonResponse = JSON.parse(response.getContentText());
// Total des issues (première réponse)
if (total === 0) {
total = jsonResponse.total;
}
// Récupère les issues
var issues = jsonResponse.issues;
// Trie et groupe les issues par Epic Link
issues.forEach(function(issue) {
var labels = issue.fields.labels;
var epicLink = issue.fields.customfield_10008 ? issue.fields.customfield_10008 : "No Epic"; // Epic Link field
var issueType = issue.fields.issuetype.name; // Récupération du type d'issue
// Récupère la valeur de l'objectif commercial ("Business Objective") via customfield_12108
var businessObjective = issue.fields.customfield_12108 ? issue.fields.customfield_12108 : "N/A";
if (!issuesByEpicLink[epicLink]) {
issuesByEpicLink[epicLink] = [];
}
var row = [
issue.key,
'=CONCAT("https://jira.adeo.com/browse/",A' + (sheet.getLastRow() + 1) + ')', // Jira Link formula
issue.fields.summary,
issue.fields.status.name,
issue.fields.assignee ? issue.fields.assignee.displayName : "Unassigned",
issue.fields.customfield_10927 ? issue.fields.customfield_10927 : "N/A", // Start Date (custom field)
issue.fields.duedate ? issue.fields.duedate : "N/A", // Due Date
issue.fields.resolutiondate ? issue.fields.resolutiondate : "N/A", // Resolved Date
epicLink,
'=VLOOKUP(I' + (sheet.getLastRow() + 1) + ',\'First Subjects to work on\'!$L$2:$M$21,2,FALSE)', // Epic Name formula
labels.join(", "),
businessObjective, // Ajout de la colonne Business Objective avec customfield_12108
issueType // Ajout de la colonne "Type"
];
issuesByEpicLink[epicLink].push(row);
});
// Met à jour startAt pour la prochaine page
startAt += maxResults;
} while (startAt < total);
// Ajoute les issues à la feuille de calcul, groupées par Epic Link
var sortedEpics = Object.keys(issuesByEpicLink).sort(); // Tri des Epic Links par ordre alphabétique
sortedEpics.forEach(function(epicLink) {
if (issuesByEpicLink[epicLink]) {
var epicIssues = issuesByEpicLink[epicLink];
epicIssues.forEach(function(row) {
sheet.appendRow(row);
});
// Ajoute une ligne vide entre les groupes
sheet.appendRow(["", "", "", "", "", "", "", "", "", "", "", "", ""]);
}
});
}
Thank you and enjoy !
Gauderic DIAGAMBANA