How to Import Jira Initiatives and Epics into Google Sheets

🚀 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.

1

2

3

4

5

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

6

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.

7

8

9

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).

10

11

12

13

14

15

Step 3 : Create a timeline into Google Sheets

Once all the objects are imported

16

17

18

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