Jira Cloud for Google Sheets: How do I import a sprint into sheets through a JQL Script

Frans Baud October 2, 2019

I'm trying to link my Google Sheets to Jira and as an output I'm trying to get a script going for an output of the values of a sprint. But I'm new on this so no clue on the Syntax?

1 answer

0 votes
Earl McCutcheon
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
October 3, 2019

Hi Frans,

Thanks for reaching out to the community, and with the new "Jira Cloud for Sheets" integration you pull the data across using Jira Query language (JQL) and overall you can get as elaborate as you want using these data import options, but there is going to be a lot of cross over with Jira JQL and the needed formatting using Google Formulas and Functions to be aware of to get you pointed int he right direction.

First calling the data for a sprint in JQL to get issues in a sprint you could use something like:

sprint in (1,2,3)

With the numbers representing the rapidView ID of the desired sprint that can be pulled from the URL if the sprint

OR something like the following to get data for issues in the current active sprints for a specific project

PROJECT = EXE AND sprint in (openSprints())

Additional details on JQL Syntax can be found in the following Documentation, but a good approach is to test the JQL in the issue navigator first to verify the formula works in Jira then copy the syntax to the sheet integration for data import:

Inputing and pulling the data into sheets can be done in 2 ways, with the full details on the setup options found in in the documentation here:

The first option to import data is in the sidebar of the application in your sheet, using the JQL input.  To get there select Add-ons > Jira Cloud for Sheets >> Open >> Get Data From Jira.  from here simply input the JQL and import the data to the current sheet.

The second option is to enable the "Jira Function" option that will enable you to use a formula in cell on the sheet directly to pull the data across, this is where you can encounter hiccups in formatting where google sheets requires something specific to get the input variables correct, this option is enabled from the same screen where you enter the JQL mentioned above, via a toggle option directly under the JQL input dialogue box.  once enabled you input the JQL statement into the formula using the format:

=jira("jql", "columns", offset, limit)

The JQL and column listing sections need to be in quotes, to offset and limit are in place incase you have performance concerns on the data pull,  so you can pull multiple data sets i.e. pull the first 1000 with offset/limit "0,1000" then pull the following 1000 with offset/limit "1000,1000", noting that by default Jira only allows exporting 1000 issues at a time from the issue navigator to CSV as the combination of search and export plus the creation of a file causes a lot of system overhead, but the communication to Google sheets is dramatically reduced as the data storage is handled Google side so only the search takes place Jira side, But caution is still needed here as you can eat up the system memory and kill DB performance really fast especially if you are pulling in large data sets with multiple updates simultaneously (exe doing multiple =jira() formula data pulls on a single action) 

So as an exe for the formulaic approach to pull the JQL above would translate to the following to get the columns issue key and sprint name, with a zero offset and a limit of 500 issues pulled.

=jira("PROJECT = EXE AND sprint in (openSprints())","sprint,issuekey",0,500)

Screen Shot 2019-10-03 at 11.49.54 AM.png

Also some tips on getting this to work out the best for you is to look at the use cases for each method. 

When using the direct JQL import method the approach is best suited for when you have a static report with formulas in an alternate sheet calling the cells in the imported data set and that Jira data needs a periodic refresh to the data set the reports are pulling the data from. Something like a weekly or monthly report. Basically any report that requires a period of time snapshot of the data set.
EXE on a simple setup: Sheet 1 has the data set pulled from jira via direct JQL, sheet 2 has all the reports with formulas referencing columns from sheet1, the data remains static until you select the Refresh option and a new data set is acquired the report runs the calculations on the updated data set and the report is updated with the updated values.

For the "=jira()" formula, it becomes really powerful when you are looking for a dynamic data entry points directly on the report to update the report ad-hoc and get live updates on the current data set, creating a report on a secondary sheet where the =jira() formula references a cell on the report page so that an update to the Cell refreshes the query and the report with live data.   
EXE on a simple setup: using the JQL examples above with a slight edit, if you have cell A1 on Sheet1 set as a select list to choose a preset list of projects and cell B1 also on Sheet1 set as the formula "=COUNTUNIQUE(Sheet2!B:B)" (column B is referencing the issue key column) and then sheet2 having the =jira() formula with the following update to the formula:

=jira("project="&Sheet1!A1&" AND sprint in (openSprints())","sprint,issuekey",0,500)

Whenever you choose a new project in Sheet 1 cell A1 the data set will update on sheet 2 to reflect the project selected in Sheet 1 and the count of unique issue keys will update per the changes on sheet 2.  This will give you an ad-hoc method of getting number of issue in the current active sprint across a set of projects on the fly as the formulas would adjust accordingly to update the count variable.  This gives you the option to rapidly cycle through different variable inputs and live update the data set used in the report.

Regards,
Earl

Suggest an answer

Log in or Sign up to answer