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

[Felipe Felix Braga]

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]

Hi @Felipe Felix Braga and @Trudy 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 

{{issue.sprint.endDate.max.jiraDate}}

• 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

[Jordan Wilkinson]

{{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]

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

[Trudy Claspill]

Hello @Felipe Felix Braga 

Welcome to the community!

I haven't yet figured out how to solve this for issues in past sprints, but I have an idea that might work going forward.

This requires at least one Automation Rule, and either a second Automation Rule or an update to the workflow.

My solution is for you to have a custom short text field in each issue to store the name of the Sprint in which the issue was completed.

You would use an Automation Rule to set this field for all Done issues when a Sprint is completed.

You would need a second Rule or modifications to the workflow(s) so that if the issue was reopened sometime after sprint closure then the custom field would be cleared.

The trick for issues already Done in a sprint that is already Completed it that the order the sprints print out is not necessarily the order in which they were completed.

JQL is not designed to retrieve information about a sprint, but rather to retrieve issues, so that can't be used to figure out which sprint was Completed last. v3 of the Jira REST API doesn't appear to have an endpoint for pulling information about a sprint. I'm still looking to see if there is a solution in an older version of the REST API.