Use 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.
The server IP addresses that Jira for Sheets uses include:
34.86.33.169, 34.86.254.214, 34.86.189.204, 34.86.125.23, 34.86.118.203, 34.86.159.247,34.86.202.157, 34.86.247.165, 34.86.8.82, 35.188.250.227, 34.86.25.26, 34.86.95.48,34.21.30.181, 34.48.4.117, 34.150.203.41, 35.245.37.161, 35.245.163.102, 34.145.231.55
This applies to Jira for Google Sheets, Jira for Excel, and the export to Google Sheets/Excel from Jira.
If you restrict access to your Jira Cloud instance, please add these IP addresses to your allow list.
Install in Google Sheets
From a Google Sheets spreadsheet:
Select Add-ons, then Get add-ons.
Search for Jira Cloud for Sheets. Hover over the card, then select +Free.
Follow the prompts to give the add-on access to your Google Sheets spreadsheet.
Connect your cloud site
Open a Google Sheets spreadsheet and select Add-ons, then Jira Cloud for Sheets, then Open.
Select CONNECT. This will open a new browser window.
Select the site you want to add from the list of available sites, and select Accept.
Once the authorization is successful you’ll be redirected to your Google Sheets spreadsheet.
Import data from Jira
You can now import Jira work items to your spreadsheet. Select Get data from Jira using one of the following methods.
Using JQL
If you don’t write a specific JQL query the system will import all work items from all projects.
Select Add-ons, then Jira Cloud for Sheets, then Open. Choose Get data from Jira.
Specify the JQL for the query you want to run. Select Open in Jira to test the JQL query in Jira.
Select Get data.
Jira will now run the query and return all matching work items in the current sheet.
Using filters
If you have filters defined on your Jira Cloud site, you can use them in the spreadsheet.
We currently support Starred filters. Make sure the filter you plan to use is Starred on your Jira Cloud site.
Select Add-ons, then Jira Cloud for Sheets, then Open. Choose Get data from Jira.
Select the Filters tab.
Choose your filter from the list of available filters.
Select Get data. You can restrict the number of rows returned with Max rows.
Jira will run the query and return all matching work items in the current sheet.
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:
Select Add-ons, then Jira Cloud for Sheets, then Open. Choose Settings.
Select Enable =JIRA() function for users of this sheet.
Read the warning, then 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.
Be careful when sharing a spreadsheet with JIRA() custom function enabled. 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:
Scenario
Return the work items in project ProjA that have been created in the last week, using your default set of columns.
Query
=JIRA("project = ProjA and createdDate >= -7d")
Scenario
Return all the work items in ProjA but only the work item key, summary text, the created time, and the reporter.
Query
=JIRA("project = ProjA", "workitemkey,summary,created,reporter")
Scenario
Return the first 10 work items in ProjA but only the work item key, the internal id of the status, and a custom column with spaces in its name.
Query
=JIRA("project = ProjA order by createdDate desc", "workitemkey,status.id,another column", 0, 10)
Scenario
Return all the work items in ProjA but only the work item key, summary text, the created time, and the reporter.
Query
=JIRA("project = ProjA", "workitemkey,summary,created,reporter")
If you want to use multiple words separated by a space in the =JIRA() function, you'll have to enclose the words with single quotes. For example, =JIRA("project=XYZ AND workitemtype='technical task'") will return all work items that are of work type "technical task" in the project XYZ.
Connect additional Jira Cloud sites
To connect an additional Jira Cloud site:
Select Add-ons, then Jira Cloud for Sheets, then Open.
Select the dropdown next to your current site. Choose Add another site.
Follow the prompts to provide access to the add-on for the additional Jira Cloud site.
Now, you can switch between sites during import.
Known problems and workarounds
I enabled the =JIRA() function and it's still showing me an error
Clear the value of the cell where you are using the =JIRA() function, press enter. Enter the query again and it should now work. You will only need to do this once (we’re working on fixing a bug causing this behavior).
The add-on returns an error when I want to query a large number of work items
The add-on should work fine with queries of up to 10,000 work items; however, this number may vary depending on the complexity and size of your data and setup. If you notice errors trying to load 10,000 work items at once, try decreasing this number.
If you need to return more work items, you should use the =JIRA() function which lets you paginate through results. Make sure to sort the results in the query.
For example, to return 20,000 work items, put this on one cell to return the first 10,000 work items:
=JIRA("project = ProjA order by updated desc", "", 0, 10000)
And this in another cell to return the following 10,000 work items:
=JIRA("project = ProjA order by updated desc", "", 10000, 10000)
The add-on returns empty cells for user data
The add-on respects individual users' privacy settings for profile data. If a user has made their profile information private then the add-on will return a blank value for this user in columns like assignee or reporter.
The add-on duplicates some work items in the export
When attributes for fields are selected, such as Comment IDs or Status Transition Dates, and there is more than one value associated with the work item (e.g. more than one status transition, comment, etc.), Excel will expand the work item row for each attribute.
This behavior may lead to timeouts, mainly when selecting many work items at a time, as more data needs to be retrieved and added to the sheet. Additionally, if a work item limit is set, the rows returned may exceed this limit as each work item could have more than one line.
Common questions
What functionality does Jira Cloud for Sheets provide?
Jira Cloud for Sheets is an add-on you can use within Google spreadsheets to import data from Jira Cloud sites:
based on a JQL (Jira Query Language)
based on starred/pre-defined filters on Jira Cloud
How do I specify which columns are populated in the spreadsheet?
The add-on uses your column settings on Jira Cloud while importing data. You can have different column settings for your account and also for filters. If there are no user/filter specific columns configured on Jira Cloud, the add-on uses the default system columns.
You can specify different columns by using the =JIRA() function, for example:
=JIRA("project=MYP","workitemType,reporter,another column")
You can also extract subfields if you know the structure of the data returned by Jira Cloud API. For example, if you are interested only in importing the work item ID and the reporter’s display name, you can use the following arguments to the JIRA() custom function.
=JIRA("project=MYP","workitemType.id,reporter.displayName")
How do I uninstall this add-on?
You can uninstall the add-on by selecting Add-ons, then Manage Add-ons. Choose Manage, then Remove.
Can I use this add-on if I don't have a licensed Atlassian Product?
No, you need a license/subscription for Jira Cloud to use this add-on.
Can I use this add-on with Jira Server?
No, the add-on only works with Cloud sites for Jira and Jira Service Management. Check with your Jira administrator if you are not sure about cloud vs server.
How do I disconnect the spreadsheet from any connected Jira Cloud instance(s)?
You can disconnect all your configured Jira Cloud sites from the add-on. From a spreadsheet:
Select Add-ons, then Jira Cloud for Sheets. Choose Open.
Select Disconnect site.
You’ll be redirected to your Atlassian Identity profile page where you can disconnect the add-on/app by selecting Remove access.
Is this add-on GDPR compliant?
Yes, and we do not store any personal user information. Read the privacy notice.
Was this helpful?