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:
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.
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.
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.
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.
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
--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
Other endpoints can be got similarly. For example, the endpoint to retrieve all statuses for a project is as under:
And for list of all issues events, the endpoint is:
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.
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.
Some recommended articles:
Import Mailchimp Data To Google Sheets