How to create an automation to set the issue's last sprint

Question

Hello!

TL;DR: I need help creating an automation that takes the current Sprint name of an issue that has been moved to Done and inserts that value into another field.

I have some issues that went from one sprint to another. As a result, I'm having trouble generating reports on another platform indicating which issues were completed in each sprint.

For example, when exporting an issue from Jira to Google Sheets that has gone through three sprints, the Sprint field will look like this:

Sprint 1; Sprint 2; Sprint 3

I created a customizable field called Last sprint and tried to create an automation that is activated when moving an issue to Done and takes the value of the field Sprint and inserts this value in Last Sprint field, but in the end, it looked like this:

Sprint 1 Sprint 2, Sprint 3

Can anyone help me create an automation that takes only the current Sprint name of an issue and inserts that value in another field?

Bill Sheboy · Answer

Hi @Jordan Wilkinson

I believe that would be the last sprint added to the field, and not necessarily the last sprint relative to the name, start/end date, etc.

Kind regards,
Bill

Jordan Wilkinson_5zndrw · Answer

{{sprint.last}} or {{sprint.last.name}} will get you the last value's name. 
https://support.atlassian.com/cloud-automation/docs/jira-smart-values-lists/

And if you wanted to add logic to only populate this field if the the ticket is marked done while still in the sprint before it was closed, you could use 
if {{sprint.last.state}} equals "active"

Bill Sheboy · Answer

Hi @Felipe Felix Braga and @Trudy P Claspill

If we assume the sprint an issue was completed in is the most recent one associated, it is possible to use a complicated rule (on a scheduled trigger) to search for the maximum sprint end date, and set the custom field for that value's sprint name.  You could run the rule until the data is caught up and then disable it.

The steps are summarized below:

* Scheduled trigger with JQL for done issues that do not have the custom field set.  This is a variation of the custom field approach Trudy suggested.
* Create a variable to build a delimited list of the sprints' name, endDate, and id.  I added id in the event that is needed for some users.  Let's name it varSprints, and use both !! and ; as delimiters.  For example:

{{#sprint}}!!{{name}};{{endDate.jiraDate}};{{id}}!!{{/}}

* Create another variable with the maximum sprint endDate, named varMaxSprintEndDate, with

* Now the tricky part...create one last variable to build a search string to use the match() function.  Let's name this one varMaxSprintSearch.  The tricky part is to leave off the quotation marks as we are using this variable in a match()

.*(!!.*;{{varMaxSprintEndDate}};.*!!).*

* Finally use that variable in a match() to find the desired sprint's name, extracting off the date and id, as desired.

{{varSprints.match(varMaxSprintSearch).substringBefore(";").substringAfter("!!")}}

I only tested this a bit, so experiment and test further if you want to try this.

Kind regards,
Bill