Auto-applying Jira Status Updates from a central sheet, back to a specific Project Plan sheet

Garo Stone-DerHagopian

We use a standard project plan (~100 tasks/steps) as a template for our team, and it gets copied for each new project. There are ~6 tasks on the project plan that drive Jira task ticket creation. Instead of adding extra columns to the standard project plan sheet (since they'll be blank for most rows), we link the task on the standard project plan sheet to another SmartSheet, where the user selects/completes the details needed for the Jira ticket. The 2nd SmartSheet is shared/linked to all the project plans (master/aggregated sheet), so that we have a centralized sheet with all the Jira requests/status updates in one place for ALL projects.

What I'm trying to figure out is a way to auto-link/auto-update from the centralized Jira sheet of ALL projects, back to the specific project plan (please see image below).

Is this possible? Or is there another way to accomplish something similar without having to recreate the workflows every time we copy over the standard project plan to use with a new project?

Peter Harink

Hi Garo,

We did something similar like this also with Jira for a big bike company in the Netherlands, but we need to see some more details to be able to effectively help you in the right direction. If you can share some sheets (of some copies in a separate workspace) we can have a quick look with you (without any commitments). Probably we can help you out using some of the more complex formulas. But if not I'm sure it could be solved using API and webhooks in which we could also help.

Kind regards,

Peter Harink | 4EF.nl | Consultant & Managing Partner | peter.harink@4ef.nl

Nathan Lloyd

Hi Garo,

I'm not sure I completely understand the intention, but it sounds like you're essentially wanting some way to automate cell linking of values from a project sheet into another sheet that would be used for Jira Issue creation. At the moment there's no way to automate cell linking. Something worth noting also is that it's possible that linked in values would be overwritten by the Jira Connector once the sync occurred (i.e. the cell would go from a value populated by an inbound cell link to a static value populated from Jira).

As Peter above suggested, you may be able to find a way to achieve this using the Smartsheet API and webhooks. Alternatively, you could consider using a Copy Row automation triggered based on some kind of "Ready for Jira" value (checkbox, dropdown list value, etc.). Another option would be to use the DataMesh application for Smartsheet to sync data between source and target sheets.

You can read more about Smartheet API / Webhooks, Copy Row functionality, and DataMesh at the links below:

• Smartsheet API: Intro to Webhooks
• Copy Rows to Another Sheet
• Smartsheet DataMesh: Eliminate Typos, Avoid Duplicative Data Entry, and More

Hope that helps!

Best,

Nathan L.

Garo Stone-DerHagopian

Thank you Nathan.

I will try something along the lines of the DataMesh approach, but maybe just utilizing two SmartSheets, the Jira Connector on each, and a vlookup. I'm not sure it'll be successful, but I'll reach out either way. If I solve it, I think others may find it helpful.

Garo Stone-DerHagopian

Getting closer, but not totally solved. Would love some guidance on this approach, or another, if people have any.

I have two SmartSheets: The individual Customer Tasks Template (CTT)/SmartSheet; and a centralized Jira ticket (JT)/SmartSheet.

The CTT contains extra fields to characterize and initiate a Jira ticket, and successfully uses a Connector to receive the Jira Key in return. I do not have any of the other Jira status/dates/etc coming back via the Connector on the CTT.

The JT uses a Connector to load all the Jira tickets and their updates onto it.

The CTT uses vlookup to pull updates from the JT (based on the Jira Key/Ticket).

The problem: I still get a "Filtered Out by Connector - Not Synced" message on the CTT, and it pulls my task line up to the top of the SmartSheet. I'm not asking for or wanting any updates from the Connector after the initial Jira Key/Ticket is created (on the CTT).

How do I set that up so that no sync is attempted after the initial Jira Key is returned? (Thereby eliminating the “Filtered Out By Connector – not synced” error and its effects on my task order). Or is there another way to do this?

Thank you!

Garo Stone-DerHagopian

My solution:

No JIRA integration on the CTT sheet. The JIRA integration is only on the JT sheet.

The CTT sheet has all the JIRA related data to generate a ticket, and the fields I want populated in return (e.g. Jira Create Date, Status, Status Date); AND a unique row ID I generate. However, the JIRA returned data fields rely on a vlookup against the JT sheet (with the unique row ID) instead of the actual JIRA key/ticket.

How to do it:

Create a column with a unique row ID in your project sheet (the row number). Then create another column that uses the sheet description/name (something unique) plus the unique row ID. e.g. ="[" + [Description]$1 + [Row ID]3 + "]"

Use the Copy Row function (only on the rows you want) to trigger and copy the specific row data from the CTT sheet, to the JT sheet.

Let the JIRA integration do its thing with the JT sheet.

Then set your CTT JIRA returned value fields to a vlookup. e.g. =IFERROR(VLOOKUP($[UniqueID]3, {JIRA Tickets Range 1}, 2, false), "")

The IFERROR is there to remove the #NO MATCH value returned for all tasks that haven't been submitted yet.

I'm still testing, but it appears to work.