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. 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.
    jira-img3
  4. One last step: 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).

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: PULL JIRA API DATA INTO SHEETS

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. Under Headers, enter a set of key-value pairs, like this:
    AuthorizationBasic YOUR_ENCODED_STRING
    Content-Typeapplication/json

    Replace YOUR_ENCODED_STRING with your encoded string from part 1, step 4.
    jira-img6

  4. 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.
  5. Name your request. Again we’ll call it ‘Jira Projects’
  6. Click Run and 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, 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

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

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