Import Data From Jira To Google Sheets

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.

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

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.

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.

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.

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.

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.

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.

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

Then select Custom API from the list of sources.

Paste 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. Flatten and expand the nested datasets and delete the columns that are not required. Then click IMPORT.

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

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.