JIRA smartsheet connector with Formulas

Evan Summerville

Hello,

I'm currently attempting streamline work status changes with Smartsheets as they are integrated with my JIRA instance via cloud connector. The way we currently run our projects within JIRA (that are synced to Smartsheets) is by creating "Epics" that have "Stories" mapped to them(as children). Through using the Smartsheet sync I was able to have all the relevant project info synced from JIRA to smartsheets, and additionally through the following formula I have automated the status change of the Epics to reflect the status of the stories:

=IF(COUNTIFS(CHILDREN(), "To Do") = COUNT(CHILDREN()), "To Do", IF(COUNTIFS(CHILDREN(), "Done") = COUNT(CHILDREN()), "Done", "In Progress"))

Essentially the formula which is based on the row's that have the Epic (where the stories are nested under as children) tells the Epic status to be "To Do" If all stories are "To Do", "Done" if all stories are "Done", and "In Progress" if any of the child stories are "In Progress". This formula has worked great, the issue I'm having now however is with the Smartsheet cloud connector with regard to the following:

1. I've tried plugging that formula into every row that is an Epic under the "status" column which worked great for a short period.
2. As the cloud connector is synced to push/pull bi-directional for "Status" within Jira (Because both applications should always reflect the same status) the formula is overwritten whenever the Epic status changes--this is because on the JIRA side the status is a normal text field while in SmartSheets is a formula
3. I've since created a column "Epic Status"(See attached screenshot) and moved the formula under it instead of "Status" so it won't be overwritten (this new column does not sync)
4. The work around I now have in place is a workflow that alerts me based on the "epic status" when an epic becomes "In Progress" or "Done." From there I review the status change and the fields, and open the JIRA Epic to manually change the status, which isn't ideal.

There has to be a way to fully automate this right? Does anyone have advice on how my approach can be adjusted here?

Any help is appreciated.

Thanks,

Smartsheet Epic status.PNG

Ryan Kramer

@Evan Summerville ,

It sounds like the manual workaround you have right now could be automated.

So basically, instead of you reviewing these updates, you could have a process monitor them and action them accordingly.

Once you start leveraging the APIs, you will have access to a lot more features around reporting and automation as well.

Ryan

Nathan Lloyd

Hi @Evan Summerville ,

Unfortunately I'm not aware of a simple way you'll be able to automate this on the Smartsheet side since the Jira Connector will use Jira as the "source of truth" for populating your issue Statuses.

I think a better approach would be to use whatever options are available on the Jira side so Epic statuses were updated there and reflected as you wanted in the sheet.

There maybe multiple approaches you can take on the Jira side but I found at least one example that uses workflow transitions to update Epic status based on child issues here.

Note that based on the UI from the screenshots in this post it looks like that user was using the Jira plugin here so you may need to loop in a Jira Admin for this option.

Hopefully something like that would be a suitable approach for you! I'd suggest reaching out in the Jira Community for more ideas on the Jira side. Please also feel free to submit an Enhancement Request

Best,

Nathan L.

Evan Summerville

Hey Nathan,

I appreciate the response and thoughts here--and I agree that ideally all components would be configured and pushed from the JIRA side. Unfortunately, my company is fairly strict about making specific configuration changes to our Jira projects, which limits what I can accomplish on that end. My hope was to make more changes on the Smartsheet side, as this is something I can control more.

Thanks again for offering your insight.

Best,

Evan