Import Jira Data to Google Sheets

Import Jira Data to Google Sheets

Last Updated On November 06, 2019

Jira is a popular bug tracking and development tool from Atlassian. In this guide, we’ll walk through how to pull data from the Jira REST API directly into Google Sheets, using the free API Connector add-on. We’ll first get an API key from Atlassian, and then set up a request to pull in user data to your spreadsheet.

PART 1: GET YOUR JIRA API KEY

  1. While logged into your Jira account, navigate to https://id.atlassian.com/manage/api-tokens and click Create API token.
    jira-img1
  2. A modal will appear, prompting you to give your token a name. You can name it anything, but for this example we’ll name it ‘Google Sheets’. Click Create.
    jira-img2
  3. You’ll now see a new modal containing your API token. Copy this to your clipboard as you’ll need it in a moment. That’s it, you now have access to the Jira API!
    jira-img3

PART 2: CREATE YOUR API REQUEST URL

We’ll follow the Jira cloud platform documentation to access a list of your projects.

  • API root: https://your-domain.atlassian.net
  • Endpoint: /rest/api/3/project/search

Putting it together, we get the full API Request URL:
https://your-domain.atlassian.net/rest/api/3/project/search

PART 3: ENTER YOUR VALUES INTO API CONNECTOR

We can now enter our values into API Connector and import Jira API data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, enter the Request URL we just created
    jira-img4
  3. Build a string of the form useremail:api_token and encode it to Base 64. So, for example, if your email is info@mixedanalytics.com and your API token is 123ABC, your string would be info@mixedanalytics.com:123ABC.

    You can encode this string in your browser by clicking Ctrl-Shift-J and entering window.btoa(‘info@mixedanalytics.com:ABC123’) in the console.
    jira-img5
    Copy the output, excluding the quotation marks. This is your encoded string.

  4. Under Headers, enter a key-value pair like this:
    Authorization Basic {encoded string}
    Content-Type application/json

    Replace {encoded string} with your encoded string from step 3.
    jira-img6

  5. Create a new tab. You can call it whatever you like, but here we’ll call it ‘Jira Projects’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  6. Name your request. Again we’ll call it ‘Jira Projects’
  7. Click Run and a moment later you’ll see a list of your Jira projects in your Google Sheet:
    jira-img7

PART 4: NOTES AND EXPANSIONS

You can check the documentation for the full list of available API requests,  but if you just want to jump in, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time):
https://your-domain.atlassian.net/rest/api/3/project/search
https://your-domain.atlassian.net/rest/api/3/issue/DEMO-1, where you replace’DEMO-1′ with your own issue ID.
https://your-domain.atlassian.net/rest/api/3/dashboard
https://your-domain.atlassian.net/rest/api/3/issue/picker
https://your-domain.atlassian.net/rest/api/3/users/search
https://your-domain.atlassian.net/rest/agile/latest/board

Comments:0

Leave a Reply

Your email address will not be published.