Search API Connector Documentation

Print

Import Jira Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

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 API Connector add-on for Sheets.

Jira’s API requires authentication for access, through either your own API token or an OAuth2 integration. This article primarily discusses connecting through API Connector’s paid OAuth2 Connection Manager, which handles authentication for you. If you’d prefer to manage the process manually for free, please skip to the Appendix for step by step instructions on connecting with an API token.

CONTENTS

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: CONNECT TO THE JIRA API

If you haven’t connected to the Jira API before, you’ll first need to initiate the connection.

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find Jira and click Connect.
    jira-img9
  3. You will see a page asking you to approve the connection. Click Accept.
    jira-img10
  4. You’ll then be returned to your Google Sheet, and can verify that your Jira connection is active in the Connections screen.
    jira-img11

PART 2: CREATE YOUR API REQUEST URL

We’ll first 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: PULL JIRA API DATA INTO SHEETS

We can now import Jira API data into Google Sheets.

  1. In API Connector’s Create screen, enter the Request URL we just created
    jira-img4
  2. We don’t need any headers here so just skip that section.
  3. Choose “Jira” from the drop-down authentication menu.
    jira-img12
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see a list of your Jira projects in your Google Sheet:
    jira-img7

PART 4: MORE EXAMPLE API URLs

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

(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

PART 5: HANDLE PAGINATION

  1. By default Jira limits the number of results in a single response (usually to 50 records) as described here: https://developer.atlassian.com/cloud/jira/platform/rest/v3/#pagination. To get more records, you can add the maxResults parameter to the end of your URL, with a value up to 100. If you still need to retrieve additional records after that, you would page through the records with the maxResults and startAt parameters, like this:
    page 1: https://DOMAIN.atlassian.net/rest/api/3/project/search?maxResults=100&startAt=0
    page 2: https://DOMAIN.atlassian.net/rest/api/3/project/search?maxResults=100&startAt=100
  2. In API Connector you can run these paged requests separately, or loop through them automatically using pagination handling (paid feature). Jira provides the complete next page URL in a field called “nextPage”, so it would be entered like this:
    jira-img8

APPENDIX: CONNECT WITH A JIRA API TOKEN

This section is provided as an alternative to the method described above. Instead of clicking Jira in the Connections manager, you will retrieve your API token yourself. Once you have it, enter it into the Headers section as shown below. Since you’re manually including an API token, leave OAuth2 authentication set to None.
  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.
    jira-img3
  4. Because the Jira API requires Basic Authentication, we need to encode our authentication info to base 64. You can do this by entering your your credentials in the format useremail:api_token into this form. Your encoded credentials will appear underneath.

    The encoding script runs in your browser, and none of your credentials are seen or stored by this site.

     

     

    (If you have any problems with the above, please check this post for some alternate methods of encoding your credentials).
  5. Now, when you run your request, enter a set of key-value pairs under Headers, like this:
    Authorization: Basic YOUR_ENCODED_STRING
    Content-Type: application/json

    Replace YOUR_ENCODED_STRING with your encoded string from above.
    jira-img6

IMPORTANT SECURITY NOTE

Anyone with Owner or Edit access to your Google Sheet can view all the information you’ve saved within API Connector, including API keys and other credentials. Treat these keys as passwords and limit access to your sheet accordingly.

12 thoughts on “Import Jira Data to Google Sheets”

  1. How can I pull all the Jira issues? I can only get 100 results. and If I run it again, it pulls the same data. I tried to setup maxResults, but data keeps repeating. Please help.

    Reply

    • Good question, you need to use the maxResults and startAt parameters. So page 1 would be https://DOMAIN.atlassian.net/rest/api/3/project/search?maxResults=100&startAt=0, page 2 would be https://DOMAIN.atlassian.net/rest/api/3/project/search?maxResults=100&startAt=100, and so on. I’ll update the post with this information.

      Reply

      • Thank you so much Ana! I have been using your project and working amazingly. I have been able to pull jira data with certain fields name like key, assignee, priority, due data. For some reason, every time API scheduler runs, the google sheet column changes. For example, sometimes it column A – AJ and sometimes A- AK. is there is any specific reason for that?

      • I’m glad you like it! API Connector doesn’t do anything with column ordering, it just displays them as they come along. So in the case of JIRA, the issue is that the API itself doesn’t return the same fields each time, most likely depending on which data exists or doesn’t exist. You can try either of the following suggestions to address this:
        1) If you are using overwrite mode, and want to see them in the same order each time, one solution is to create a second sheet containing query functions like this:
        =QUERY(data!$1:$10000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("field_name",data!$1:$1,0),4),1,""),1)
        That would pull in all the data from a sheet called data, column called “field name”. You could repeat this for each field name that you need.
        2) You can use JMESPath filtering (paid feature) to choose which fields you want to retrieve and lock them in place between pulls.

  2. Hi Ana, I am using Overwrite mode. You are right, Jira API field returns are inconsistent. I did try append mode, but it pulls the same data based on limit. Is there way to auto merge the duplicate data? Since I do not know how many new projects will be updates at the given time.

    Reply

    • This article is for JIRA Cloud, though if your on-premise JIRA has an API, there’s no reason you can’t connect to that too.

      Reply

      • Thanks for the response. Could you please help me with the certificate config which need to be done to connect to Jira api.
        We have client cert which need to be configured to access the api. Is there any way we can do this from google sheet?

Leave a Comment