Import Data From Jira To Google Sheets

Connect Jira to Google Sheets and improt data from Jira to Google Sheets
Reading Time: 5 minutes

Improve Jira issues tracking and project management by connecting Jira to Google Sheets. We can integrate Jira with Google Sheets and import data from Jira to Google Sheets. Syncing Jira and Google Sheets are then possible and Jira data are automatically imported to Google Sheets.

This can be done with ease with Amigo, a Google Sheets add-on (also available as a web app). Amigo lets us connect Jira to Google Sheets and create a Jira Google Sheets integration. After creating a Jira Google Sheets integration, we can with ease import data from Jira to Google Sheets without hassle and without coding.

To set up a Jira Google Sheets integration and import data from Jira to Google Sheets, we’ll use the following steps:

  1. Install the Amigo add-on
  2. Generate Jira API token
  3. Get the API endpoint URL
  4. Import the data from Jira

How to import data from Jira to Google Sheets

Here is a step-by-step guide on how to create a Jira Google Sheets and connect Jira to Google Sheets and then import data from Jira to Google Sheets.

Step 1: Install the Amigo Data extension

We’ll use the Amigo Data extension in Google Sheets to import data from Jira to Google Sheets. You can get the extension from the Google Workspace Marketplace by clicking the link provided below.

Install Amigo to import data from Jira to Google Sheets

Click here to install.

Alternatively, you can get it from Google Sheets.

  • Open a new sheet
  • Go to Extensions→Add-ons→Get add-ons
  • Search for Amigo and install

Step 2: Create Jira API token

Log in to your Atlassian account and navigate to the account security page by clicking the account icon at the top right corner. Then select Manage account→Security. Click the Create and manage API tokens link given at the bottom of the page.

Jira security page

On clicking the link, we’ll be taken to the API tokens management page. Click Create API token.

API tokens page

Give the token a suitable label and click the Create button.

Labelling the API token

Copy the API token and keep it somewhere safe and accessible. The token will not be found again on the token page. There are however options to revoke the token or create new ones.

Step 3: Get the API endpoint URL

Go to the Jira API documentation. On the left of the page is a list of endpoint categories.

List of endpoint categories

Expand any of the endpoint categories and select the specific endpoint you want to retrieve. We’ll use the Projects category as an example and use the Get all projects endpoint.

Endpoints under Projects category

Scroll to the Example section to find the generic endpoint, and also the authentication type and header.

In the snippet shown below, the parameters --url and --header are the endpoint URL and header respectively; --user signifies the basic authentication. The email id is the username and the API token is the password.

Endpoint URL and headers

Modify the endpoint URL by replacing the your-domain part with your domain.

We can also pass the authentication as a header. For this we need to transform the --user values into a header by encoding the [email protected]:API_token into a base64 text string.

Authorization: Basic XWxhZGRpbjpvcGVuc2VzYW1l

The text after the Basic keyword is the base64 encoded text string of the email:api_token combination provided with the --user parameter

Other endpoints can be got similarly. For example, the endpoint to retrieve all statuses for a project is as under:

https://your-domain.atlassian.net/rest/api/3/project/{projectIdOrKey}/statuses
And for list of all issues events, the endpoint is:
https://your-domain.atlassian.net/rest/api/3/events

The other parameters–user and header–remain the same. And we can either pass them as basic authentication or headers.

Step 4: Import data from Jira to Google Sheets

Open Google Sheets and launch Amigo. To connect to Amigo, click Extensions→Amigo→Connect.

Launch Amigo to connect Jira to Google Sheets

Then select Custom API from the list of sources.

List of sources to connect to with Amigo Data

Paste the endpoint URL without the apostrophes in the API URL field.

Entering the endpoint URL in Amigo

Expand the Authentication tab and select Basic Auth from the drop-down menu. Then enter your Jira email address and the API token created earlier as the username and the password, respectively.

Entering the authentication

Then expand the Details tab and enter the header(s).

Name: Accept, Value: application/json.

And if you’re passing the authentication as a header, then enter the values as a separate header.

Name: Authorization, Value: Basic base64_endoded string

Entering the headers

Click the PREVIEW button. Flatten and expand the nested datasets and delete the columns that are not required. Then click IMPORT.

Preview of the data

The data will be imported into Google Sheets in a few moments.

Data imported from Jira to Google Sheets

If you want to or need to have these data updated automatically, you can do so by setting a refresh schedule in Amigo, and the data will be fetched automatically. Click on this link to learn how to set a refresh schedule and receive the same in your inbox.

Some recommended articles:
Import Mailchimp Data To Google Sheets

Import SurveyMonkey Data To Google Sheets

Import Holidays List From Holiday API To Google Sheets

X
Hire expert data analysts on-demand. Get 30 Days Free Trial