Automating data extraction from JIRA with Google Apps Script

Note: This post was originally published in our community forum.

Leveraging Google Apps Script to pull data from JIRA into Google Sheets is a powerful technique for teams looking to automate their workflow and enhance project management. This integration simplifies tracking issues, tasks, and progress directly within a familiar spreadsheet environment.

Quick setup guide

1. Create a Google Sheet as your data repository.

2. Open Script Editor from Google Sheets to write custom functions.

3. Utilize JIRA’s API within your script to fetch data such as issue details, statuses, and updates.

4. Write a Script in Apps Script to parse and insert the JIRA data into the Google Sheet.

5. Set Triggers for automatic updates, ensuring your data remains fresh without manual intervention.

Benefits at a glance

• Streamlined Project Tracking: Centralize your JIRA data in Google Sheets, making it easier to monitor project timelines, task assignments, and statuses.

• Improved Data Analysis: With data in Sheets, leverage built-in tools for deeper insights and reporting, identifying bottlenecks or areas for improvement.

• Enhanced Team Collaboration: Share your data-rich Google Sheets with team members, fostering a collaborative environment where information is transparent and accessible.

This approach not only saves time but also bridges the gap between complex project management data and actionable insights, leading to more informed decision-making and streamlined operations.

Here a code sample for google app script

Note: Please replace yourDomain, yourEmail, yourApiToken, and yourProjectKey with your actual JIRA domain, email, API token, and project key. API tokens can be generated from your Atlassian account security settings.

function fetchJiraIssues() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  var url = 'https://yourDomain.atlassian.net/rest/api/3/search?jql=project=yourProjectKey';

  

  var options = {

    'method' : 'get',

    'contentType': 'application/json',

    // Replace yourEmail and yourApiToken with your actual email and API token

    'headers': {

        'Authorization': 'Basic ' + Utilities.base64Encode('yourEmail:yourApiToken')

    },

    'muteHttpExceptions': true

  };

  

  var response = UrlFetchApp.fetch(url, options);

  var jsonResponse = JSON.parse(response.getContentText());

  

  // Clear existing content

  sheet.clearContents();

  

  // Set headers

  sheet.appendRow(["Issue Key", "Summary"]);

  

  // Loop through each issue and append data to the sheet

  jsonResponse.issues.forEach(function(issue) {

    sheet.appendRow([issue.key, issue.fields.summary]);

  });

}