Jira Cloud for Sheets
Jira Cloud for Sheets lets you import Jira data into your spreadsheets using existing Jira filters or custom JQL (Jira Query Language).
Your G Suite administrator might need to enable access to Google Sheets add-ons for all users. Learn more.
Install in Google Sheets
From a Google Sheets spreadsheet:
Click Add-ons then click Get add-ons…
Search for “Jira Cloud for Sheets” then hover over the card and click +Free.
Follow the prompts to give the add-on access to your Google Sheets spreadsheet.
Connect your cloud site/instance
Open a Google Sheets spreadsheet and select Add-ons > Jira Cloud for Sheets > Configure…
Click CONNECT.
This will open a new browser window.Select the site you want to add from the list of available sites, and click Accept.
Once the authorization is successful you’ll be redirected to your Google Sheets spreadsheet and you should see something like the following:
Import Data from Jira
You can now import Jira issues to your spreadsheet using one of the following methods:
Using JQL
If you don’t write a specific JQL query the system will import all issues from all projects.
Click on Add-ons → Jira Cloud for Sheets → Import Jira data…
Specify the JQL for the query you want to run. You can click on the ‘Open in Jira’ link to open and test the JQL query in Jira.
Click on GET DATA.
Jira will now run the query and return all matching issues in the current sheet.
フィルターを使用する
If you have filters defined on your Jira Cloud site, you can use them in the spreadsheet.
Currently, we only support ‘Starred Filters’. Make sure the filter you plan to use is ‘Starred’ on your Jira Cloud site.
Click Add-ons → Jira Cloud for Sheets → Import Jira data…
Select the ‘Filters’ tab
Choose your filter from the list of available filters
Click GET DATA. You can restrict the number of rows returned with ‘Max Rows’.
Jira will run the query and return all matching issues in the current sheet. It should look something like the following figure:
Use the JIRA() custom function with JQL
The add-on also provides a custom Google Sheets function =JIRA(), which you can use to query Jira from a cell in a spreadsheet:
Click Add-ons → Jira Cloud for Sheets → Import Jira Data…
Click “Enable =JIRA() function for users of this sheet”
Read the warning and accept.
Only owners of the document can enable this function. When enabled, this function uses the document owner’s configured site and Jira account to query Jira.
Only owners of the document can enable this function. When enabled, this function uses the document owner’s configured site and Jira account to query Jira.
Be careful when sharing a spreadsheet with JIRA() custom function enabled as anyone who can edit it will be able to use this function and import Jira data using the document owner’s Jira account.
You can use the function from a cell in the spreadsheet:
シナリオ |
クエリ |
---|---|
Return the issues in project ProjA that have been created in the last week, using your default set of columns. |
=JIRA("project = ProjA and createdDate >= -7d") |
Return all the issues in ProjA but only the issue key, summary text, the created time, and the reporter. |
=JIRA("project = ProjA", "issuekey,summary,created,reporter") |
Return the first 10 issues in ProjA but only the issue key, the internal id of the status, and a custom column with spaces in its name. |
=JIRA("project = ProjA order by createdDate desc", "issuekey,status.id,another column", 0, 10) |
Return all the issues in ProjA but only the issue key, summary text, the created time, and the reporter. |
=JIRA("project = ProjA", "issuekey,summary,created,reporter") |
Connect additional Jira Cloud sites (optional)
To connect an additional Jira Cloud site:
Click Add-ons → Jira Cloud for Sheets → Import Jira Data…
Click the MANAGE button then click ‘Add Another Jira’ when the dialog opens.
Follow the prompts to provide access to the add-on for the Jira Cloud site.
Select a site and click on the ‘SAVE’ button to set the site from which data will be imported.