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.
There are 2 ways to connect to the Jira API:
- Preset “Connect” button (OAuth) premium
- Personal API token. Please check the appendix for detailed instructions to retrieve your token.
- 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:
Part 3: Pull Jira API Data into Sheets
Now let’s grab that data.
- 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):
(replace’DEMO-1′ with your own issue ID).
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
maxResultsparameter 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
startAtparameters, like this:
- 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_tokeninto 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
Replace YOUR_ENCODED_STRING with your encoded string from above.