Import Jira Data to Google Sheets
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.
CONTENTS
- Before You Begin
- Part 1: Connect to the JIRA API
- Part 2: Create your API Request URL
- Part 3: Pull JIRA API API Data into Sheets
- Part 4: More Example API URLs
- Part 5: Handle Pagination
- Appendix: Connect with an API Token
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.
- Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
- In the list of available connections, find Jira and click Connect.
- You will see a page asking you to approve the connection. Click Accept.
- You’ll then be returned to your Google Sheet, and can verify that your Jira connection is active in the Connections screen.
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.
- In API Connector’s Create screen, enter the Request URL we just created
- We don’t need any headers here so just skip that section.
- Choose “Jira” from the drop-down authentication menu.
- Create a new tab and click ‘Set current’ to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see a list of your Jira projects in your Google Sheet:
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
- 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 themaxResults
andstartAt
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
- 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:
APPENDIX: CONNECT WITH A JIRA API TOKEN
- While logged into your Jira account, navigate to https://id.atlassian.com/manage/api-tokens and click Create API token.
- 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.
- You’ll now see a new modal containing your API token. Copy this to your clipboard as you’ll need it in a moment.
- 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). - 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.
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.
Good question, you need to use the
maxResults
andstartAt
parameters. So page 1 would behttps://DOMAIN.atlassian.net/rest/api/3/project/search?maxResults=100&startAt=0
, page 2 would behttps://DOMAIN.atlassian.net/rest/api/3/project/search?maxResults=100&startAt=100
, and so on. I’ll update the post with this information.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.
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.
There’s no auto-merge, but a few suggestions:
1) limit the fields you pull from the API to start with, to pull only those fields that always contain data. This post has more info on that: https://community.atlassian.com/t5/Answers-Developer-Questions/API-JQL-search-results-provide-way-too-much-information/qaq-p/486398
2) use the formula I provided in a comment above to pull the exact fields you want into a new sheet. Then it doesn’t matter which order they’re in. This is the formula:
=QUERY(data!$1:$10000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("field_name",data!$1:$1,0),4),1,""),1)
3) Use JMESPath filtering (paid feature) to choose which fields you want and fix them in place
Thank you so so much! Option 2 works perfectly for my project! Cant thank you enough!
Nice! Glad it helped 🙂
Will this only work for Jira Cloud or On premised Jira also ( Jira server instances ) ?
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.
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?
Sorry, I don’t know any way to attach a certificate through Google Sheets.