Email on sprint end date

[Eric Meinders]

This seems like it should be super simple, so I'm probably overlooking something, but how can I send email notifications on the date a sprint ends (not when it is closed, but when the sprint end date arrives), either through automation or a filter subscription?  I've almost been able to get this to work in automation by creating a daily trigger, use JQL to search my project and open sprint, then use an issue's max end date {{issue.sprint.endDate.max}} and compare to the current date, if they match send an email....but this will run for EVERY issue that matches the trigger's JQL which is obviously not going to work (is there a way to only run one time or something programmatic like exiting after 1 run?).

In theory it should be something as simple as run a daily trigger, project = 'PROJ NAME' and sprint in openSprints(), then grab the sprint end date, if end date == {{now}} send email.  But it appears everything with automation is issue based.

[Bill Sheboy · Accepted solution]

Hi @Eric Meinders -- Welcome to the Atlassian Community!

You describe you almost got this to work with a rule, and so I will assume some things for your scenario.

Assumptions:

• you want to do this for one project
• that project has one active sprint at a time...
• and so that active sprint has the maximum end date for any sprints assigned to issues
• you do not state what is in this end-of-sprint-day notification; let's assume it is a list of the issues in the sprint when the rule runs

If those assumptions fit for your scenario, such a rule could be this:

• trigger: scheduled, daily and early in the day, with no JQL
• action: lookup issues with JQL of

project = yourProjectName AND sprint IN openSprints()

• smart values condition: to confirm something was found
  • first value: {{lookupIssues.size|0}}
  • condition: greater than
  • second value: 0
• smart values condition: to check if this is the last day of the sprint
  • first value: {{lookupIssues.first.sprint.endDate.max.jiraDate}}
  • condition: equals
  • second value: {{now.jiraDate}}
• action: send email (or some notification), using the Lookup Issues results

The key here is grabbing an example issue in the sprint, and using the maximum endDate value for the comparison to the current date.  You may want to add time zone conversions if you try to run this very early in the day if you are near the UTC+0 location.

Kind regards,
Bill

[Eric Meinders]

Ding ding ding, you nailed it Bill!  The key was the `.first` property for the lookupIssues smart value - that is exactly what I was looking for as it's something very common with programming, but I could not find anything about it in the smart value KBs or community posts.  Thanks!

P.S. I also had no idea about the |0 operator for adding default values in the event it returns null, that's also super helpful for future use cases.

[Bill Sheboy]

Awesome; I am glad to learn that helped!

[Trudy Claspill]

Hello @Eric Meinders 

Welcome to the Atlassian community.

JQL is designed to return issues so if your automation is using a JQL statement then yes it will be issue-oriented.

Also note that sprints are not necessarily associated to a single project. Sprints are created within Scrum boards, and the scrum board filter can reference any number of projects.

If you want to get a list of sprints, you could do that using the Send Web Request action in an Automation Rule, and call the REST API to get sprints for a board.

https://developer.atlassian.com/cloud/jira/software/rest/api-group-board/#api-rest-agile-1-0-board-boardid-sprint-get

That doesn't include any parameters to let you filter the sprints returned based on criteria beyond the active, future, closed state of the sprint. You would have to parse the returned data to find the sprints with end dates that are today.

I don't have any specific guidance on how to parse the data. I haven't worked with the Send Web Request action very much.

There is this community article on using that action, which you might find helpful.

https://community.atlassian.com/t5/Jira-articles/Automation-for-Jira-Send-web-request-using-Jira-REST-API/ba-p/1443828/page/2