Import Data From Jira To Google Sheets

Install the Amigo add-on to create a Jira and Google Sheets integration and pull Jira data to Google Sheets. Get it from the Google Workspace Marketplace by clicking the link provided below.

Alternatively, you can get it from Google Sheets.

• Open Google Sheets
• Go to Extensions→Add-ons→Get add-ons
• Search for Amigo and install it.

Log in to your Atlassian account and navigate to the account security page by clicking the account icon at the top right corner and clicking Account Settings.

Select Security. Then click the Create and manage API tokens link given at the bottom of the page.

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

Give the token a suitable label and click the Create button to create a Jira 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.

Go to the Jira API documentation. On the left of the page is a list of endpoint categories that you can use to pull Jira data to Google Sheets.

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 to import Jira projects to Google Sheets.

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 basic authentication. The email id is the username and the API token is the password.

Note these values as we’ll use them to import Jira data to Google Sheets.

Modify the endpoint URL by replacing your-domain in the URL with your Atlassian domain.

For example, my Atlassian URL is tryamigo.atlassian.net. So in my case, the endpoint URL would be:

https://tryamigo.atlassian.net/rest/api/3/project

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

Authorization: Basic XWxhZGRpbjpvcGVuc2VzYW1l

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

Other endpoints to import Jira data to Google Sheets 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 the 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.

Open Google Sheets and launch Amigo to import data from Jira to Google Sheets. To connect to Amigo, click Extensions→Amigo→Connect.

Then select Custom API from the list of sources.

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

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.

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]

Click the PREVIEW button to retrieve the Jira data and preview the Jira projects to import to Google Sheets. Flatten and expand the nested datasets and delete the columns that are not required. Then click IMPORT.

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

And so that’s how we pull Jira project to Google Sheets with Jira API.

You can similarly import other types of Jira data to Google Sheets such as Jira tasks, issues, user groups and project types to name but a few.

Visit the Jira API documentation to learn more.

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 and updated at regular intervals. Click on this link to learn how to set a refresh schedule.

You can also add alerts to let Amigo alert you and your team via email and Slack. All your team members will then receive the updated data simultaneously as soon as an update is received. Amigo enables you to take collaboration to the next level.