Icon

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.

By Gauderic D

🚀 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
Step 1 : Jira Token

Click on your "profile picture" to access your profile informations
Step #1: Step 1 : Jira Token Click on your "profile picture" to access your profile informations
2
Click on "Personal Access Tokens"
Step #2: Click on "Personal Access Tokens"
3
Click on "Create token"
Step #3: Click on "Create token"
4
Fill the "Token Name"
Step #4: Fill the "Token Name"
5
Click on "☑ Automatic expiry" if you want your token to be available all the time. And then click on "Create"
Step #5: Click on "☑ Automatic expiry" if you want your token to be available all the time. And then click on "Create"

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
Open a "New spreadsheet" and add your headers.
You can customize these headers based on the information you need for your timeline.
Step #6: Open a "New spreadsheet" and add your headers.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.

7
Click on "Extensions"
Step #7: Click on "Extensions"
8
Paste the provided Apps Script code (see example below).
Replace YOUR_JIRA_DOMAIN, YOUR_API_TOKEN, and YOUR_JQL_QUERY with your actual values.
Step #8: Paste the provided Apps Script code (see example below).Replace YOUR_JIRA_DOMAIN, YOUR_API_TOKEN, and YOUR_JQL_QUERY with your actual values.
9
Focus on the URL QUERY :

YOUR_JQL_QUERY is similar as what you can find into a JIRA advanced search and is adapted to match with JIRA API
Step #9: Focus on the URL QUERY : 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).

10
Back into Google Sheets - Apps Script

Click on the "three dots" to open the parameters
Step #10: Back into Google Sheets - Apps ScriptClick on the "three dots" to open the parameters
11
Click on "Rename" and rename the query

You can also change the function name
Step #11: Click on "Rename" and rename the queryYou can also change the function name
12
Make sure that the mouse is placed on the first cell
Step #12: Make sure that the mouse is placed on the first cell
13
Go back to the "Apps Script tab" and click on "Run"
Step #13: Go back to the "Apps Script tab" and click on "Run"
14
You can have the message to "Review permissions" to authorize the script to access your Google Sheet and Jira.
Step #14: You can have the message to "Review permissions" to authorize the script to access your Google Sheet and Jira.
15
The script will run and import data from Jira
Step #15: The script will run and import data from Jira

Step 3 : Create a timeline into Google Sheets

Once all the objects are imported

16
Click on "Insert" menu and then "Timeline"
Step #16: Click on "Insert" menu and then "Timeline"
17
Select the desired range of data for your timeline. This data will be used to generate the visual representation and must include the start date and due date.

Click "OK" to proceed.
Step #17: Select the desired range of data for your timeline. This data will be used to generate the visual representation and must include the start date and due date.Click "OK" to proceed.
18
Your timeline is ready! Customize it using the settings panel.
Step #18: Your timeline is ready! Customize it using the settings panel.

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

How to Import Jira Initiatives and Epics into Google Sheets