JIRA Integration: automatically fill in "Start Date" when a Status has been changed?

Andy C.

Hi Smartsheet Community,

 

We've integrated JIRA into Smartsheet. We're looking to automate some more fields.

 

When using the gantt charts, some of the field we are manually filling are and Start Date and End Date.

 

Is it possible to accomplish the following?

1) When a JIRA [Status] has been switched from To-Do --> In Progress

2) [Start Date] automatically populates the current day

3) [End Date] automatically populates based on the [Duration]

 

Right now we are doing steps 2 & 3 manually.

 

Thank you,

Andy

Scott Willeke

You can do most of this with a little configuration in JIRA. You can use JIRA's Workflow Transition Post Functions to set the value of a custom field, such as "Actual Start", to the date when the issue transitions to "In Progress". See JIRA's Using a post function to set a field documentation for more info. Once you add the custom field, the field will be available to map to a column in Smartsheet. This solves #1 & #2. Unfortunately, you can't do #3 quite as you described today, but you can get close... 

 

What You Can Do:

What I've seen other customers do is the following: Map the the "Actual Start" (JIRA custom field as described above) to a separate column from the "Planned Start" column that the Gantt view is using. Then add a "Start Variance" column with a formula like =[Planned Start]1 - [Actual Start]1. Then use Conditional Formatting "Start Variance" to highlight rows where the Start Variance is not zero or blank. Then you can easily spot those rows where where the Actual Start wasn't the same as the Planned start (you can also setup a Saved Filter to make those easy to find). While this is manual, some customers actual prefer this because they want to be aware of any changes to their actual plan so that they can take action when things don't go according to plan (e.g. add more resources to keep the project on track, or notify stakeholders).

 

Feedback: 

We are investigating adding support for making this entirely automatic so that changes to Dates in JIRA will automatically update the Start/End/Duration fields in your Gantt view on a dependency-enabled sheet. However, I'd like your feedback on how we're thinking about this...

 

To do this in the future one possibility is that you could map the "Actual Start" to the Gantt Start Date column. Then map the Duration in JIRA to the Duration column in Smartsheet. This has the following somewhat subtle implications:

1. By Mapping Actual Start from JIRA to the Gantt's Start Date column, you'll have to have the "Actual Start" in JIRA have a date /before/ it transitions to In Progress too so that the Gantt view works. Alternatively, you could leave it empty while the task's status is "To Do" but, the Gantt's start date would end up being empty too and the Gantt bar for those "To Do" tasks won't appear. Which option to you prefer here?
2. You would need to make the End Date column in Smartsheet either not mapped back to JIRA or maybe we could let you map it from Smartsheet to JIRA, but not vice versa or bidrectionally.

 

The thinking here, is due to the automatic changes to Start/End/Duration columns in a schedule, we wouldn't want you to let JIRA edit the End /and/ Duration columns in Smartsheet because then it is becomes unclear whether the Duration or the End Date is what you want to take precedence. By forcing you to map one or the other, it is more clear what your intention is.

 

Would this work for you? If not, what is your ideal solution?

 

Andy C.

Scott,

 

Thanks so much for your details, and sorry for my late reply.

 

You can use JIRA's Workflow Transition Post Functions to set the value of a custom field, such as "Actual Start", to the date when the issue transitions to "In Progress"

 

Unfortunately this is not possible. I tried using Post Functions, but it wouldn't support custom fields. JIRA's documentation states it is not possible: "This post function cannot update custom fields and must be positioned after the other optional post functions."

 

However, I can confirm being able to add the custom "Actual Start" field into Smartsheet. We will try manually updating these in JIRA.

 

"Which option to you prefer here? You would need to make the End Date column in Smartsheet either not mapped back to JIRA or maybe we could let you map it from Smartsheet to JIRA, but not vice versa or bidrectionally."

 

Good question! I envision the "End Date" column in JIRA being tied to the "Due Date" field in JIRA. This would be mapped one way from Smartsheet to JIRA, but not vice versa.

 

This way, the fields are:

 

1) Start Date: mapped bidirectionally to the "Actual Start" custom field in JIRA. (This is already available).

2) End Date: mapped one way from Smartsheet to JIRA, to the "Due Date" field in JIRA.

3) Original Time Estimate: mapped to JIRA's "Story Points". Currently this is mapped one way from JIRA to Smartsheet. I hope it can become bidirectional.

 

By implementing #2 and #3, I would use JIRA and Smartsheet to do the following:

Step 1) Using JIRA: define "what" work needs to be done in tickets. In other words, product management backlog grooming.

Step 2) Using Smartsheet: plan sprints, assign work, add estimates, and add dependencies in Smartsheet.

Step 3) Using JIRA: start the sprint with tickets queued in the exact order as defined in Smartsheet gantt charts.

 

Let me know if you have any more questions.

Scott Willeke

Andy,

 

Before I sent this I actually did setup the Workflow Post Function editing a custom field as I explained. I was using JIRA Cloud, so maybe it is possible there but doesn't work in older on-premise versions? Below is the screenshot of the Post Function I still have set up in my workflow on JIRA Cloud:

 

 

 

Thanks for the feedback on your use case and the steps of your process and how you'd use it. The context is very helpful. Lets review your fields/feature requests in order:

 

1) JIRA Actual Start Date Field <-> Actual Start Smartsheet Column - As you noted,this can be done today.

 

2) JIRA Due Date Field <- End Date Smartsheet Column (which is also a Gantt End Date on a dependency enabled sheet). This actually also works today, as long as you only map it one direction *from Smartsheet to JIRA*. You *cannot* map it in the other direction nor bidirectionally at this time.

 

3) JIRA Story Points Field <-> Original Time Estimate Smartsheet Column - This you can do, but the Original Time Estimate column in Smartsheet must not be a "Duration" field used by the Gantt. Since Smartsheet's Gantt is based on time-based duration, it cannot map points to a time-based duration. However, you could map the Story Points field to it's own column, and you could make your own manual estimate of Duration based on the story points, historical data, and your own judgement (e.g. based on past velocity).

 

 

Happy to do a call to discuss this more if it is helpful to you. Feel free to reach out to me via email.

 

-- 

Scott Willeke | Smartsheet

Senior Product Manager, Strategic Integrations

E: Scott.Willeke@Smartsheet.com

 

Andy C.

Sent you an email, thanks Scott.