Search API Connector Documentation

Print

Import Jira Data to Google Sheets

Last Updated On
byAna
< Knowledge Base

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.

Contents

Before You Begin

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

Part 1: Get Your Cloud ID

If you're connecting via OAuth, you will need your cloud ID for all requests. Here's how to get your cloud ID.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • Application: Custom
    • Method: GET
    • Request URL: https://api.atlassian.com/oauth/token/accessible-resources
    • OAuth: Jira
  3. When you select Jira from the OAuth menu, you'll see a blue Connect button if you haven't already authorized the connection. Click through to enable the connection.
  4. Create a new tab and click Set current to use that tab as your data destination.
  5. Name your request and click Run. You'll see your cloud ID in cell A2.


jira-cloudid

When connecting via API token, you don't need a cloud ID in the URL. See example URLs below.

Part 2: Pull Jira API Data into Sheets

Now let's get some data about your projects. Here's an example request setup. Plug in your cloud ID in the request URL where it says your-cloudId:

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request
  2. In the request form enter the following:
    • Application: Custom
    • Method: GET
    • Request URL: https://api.atlassian.com/ex/jira/your-cloudId/rest/api/3/project/search
    • Headers:
      • Content-type: application/json
    • OAuth: Jira
  3. Create a new tab and click Set current to use that tab as your data destination.
  4. Name your request and click Run. A moment later you’ll see a list of your Jira projects in your Google Sheet:
    jira-results1

Part 3: 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 try out the following request URLs.

The URLs are a bit different depending on whether you're connecting via OAuth or through an API token:

OAuth URLs

https://api.atlassian.com/ex/jira/your-cloudId/rest/api/3/dashboard
https://api.atlassian.com/ex/jira/your-cloudId/rest/api/3/issue/picker
https://api.atlassian.com/ex/jira/your-cloudId/rest/api/3/users/search
https://api.atlassian.com/ex/jira/your-cloudId/rest/agile/latest/board

API Token URLs

https://your_domain.atlassian.net/rest/api/3/project/search
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 4: Handle Pagination

  • By default Jira limits the number of results in a single response (usually to 50 records) as described here. To get more records, you would use the maxResults and startAt parameters.
  • 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:
    • Pagination type: next page URL
    • Next page path: nextPage
    • Run until: choose when to stop fetching data

      jira-pagination-nextpageurl

Part 5: API Documentation

Official API documentation: https://developer.atlassian.com/cloud/jira/platform/rest/v3/

To view or remove the connection click here: https://id.atlassian.com/manage-profile/apps

Appendix: Connect with a Jira API Token

This section is provided as a free 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-create-token
  2. A modal will appear, prompting you to give your token a name. You can name it anything. Click Create.
    jira-create-token-modal
  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 (and they won't show it to you again).
    jira-create-token-copy
  4. One more step: we need to encode this token to base 64 (aka Basic Authentication). You can do this by entering your your credentials in the format your_email_address:your_token into this form (i.e. your JIRA email address, then a colon, and then the API token you just got).

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

     

  5. Now, when you run your request, enter two sets of key-value pairs under Headers, like this:
    Authorization: Basic your_encoded_token
    Content-Type: application/json
    jira-token-results
  6. URLs are different depending on whether you connect via OAuth or an API token, so make sure to use the URL syntax shown in the list of API token URLs.

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.
        Update: You can now use API Connector's visual field editor to easily re-order columns.

  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