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) . 

Install in Google Sheets

From a Google Sheets spreadsheet:

1. Click Add-ons then click Get add-ons…

2. Search for “Jira Cloud for Sheets” then hover over the card and click +Free.

3. Follow the prompts to give the add-on access to your Google Sheets spreadsheet.

Connect your cloud site/instance

1. Open a Google Sheets spreadsheet and select Add-ons → Jira Cloud for Sheets → Open…

2. Click CONNECT, this will open a new browser window.

3. 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 (aka the hamburger menu):

Import Data from Jira

You can now import Jira issues to your spreadsheet by clicking on Get data from Jira and then using one of the following methods:

Using JQL

1. Click on Add-ons → Jira Cloud for Sheets → Open … → Get data from Jira

2. 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.

3. Click on GET DATA.

4. Jira will now run the query and return all matching issues in the current sheet.
   

Using Filters

If you have filters defined on your Jira Cloud site, you can use them in the spreadsheet.

1. Click Add-ons → Jira Cloud for Sheets → Open … → Get data from Jira

2. Select the FILTERS tab

3. Choose your filter from the list of available filters

4. 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:

1. Click Add-ons → Jira Cloud for Sheets → Open… → Settings

2. Click “Enable =JIRA() function for users of this sheet”

3. 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. 

You can use the function from a cell in the spreadsheet:

Scenario

Return the issues 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 issues in ProjA but only the issue key, summary text, the created time, and the reporter.

Query

=JIRA("project = ProjA", "issuekey,summary,created,reporter")

Scenario

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.

Query

=JIRA("project = ProjA order by createdDate desc", "issuekey,status.id,another column", 0, 10)

Scenario

Return all the issues in ProjA but only the issue key, summary text, the created time, and the reporter.

Query

=JIRA("project = ProjA", "issuekey,summary,created,reporter")

Connect additional Jira Cloud sites (optional)

To connect an additional Jira Cloud site:

1. Click Add-ons → Jira Cloud for Sheets → Open…

2. Click on the dropdown next to your current site and click ‘ Add another site… ’.

3. Follow the prompts to provide access to the add-on for the additional Jira Cloud site.

4. Now you can switch between sites during import.

Known issues 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 issues.

The add-on should work fine with queries of up to 10,000 issues; 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 issues at once, try decreasing this number.

If you need to return more issues, 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 20000 issues, put this on one cell to return the first 10,000 issues:

=JIRA("project = ProjA order by updated desc", "", 0, 10000)

And this in another cell to return the following 10,000 issues:

=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 issues 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 issue (e.g. more than one status transition, comment, etc.), Excel will expand the issue row for each attribute.

This behavior may lead to timeouts, mainly when selecting many issues at a time, as more data needs to be retrieved and added to the sheet. Additionally, if an issue limit is set, the rows returned may exceed this limit as each issue could have more than one line.

FAQ (References)

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","issueType,reporter,another column")

=JIRA("project=MYP","issueType.id,reporter.displayName")

How do I uninstall this add-on?

You can uninstall the add-on by click on Add-ons > Manage Add-ons > Manage >  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 Jira Cloud sites for Jira Work Management, Jira Software 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:

1. Click on Add-ons → Jira Cloud for Sheets → Open…

2. Click on on the Disconnect site button

3. this will redirect you to your Atlassian Identity profile page where you can disconnect the add-on/app by clicking on ‘Remove access’.

Is this add-on GDPR compliant?

Yes, and we do not store any personal user information. For further information please see the following link:

• https://developer.atlassian.com/cloud/jira/platform/deprecation-notice-user-privacy-api-migration-guide/