Turning a complex spreadsheet into a Jira project

[Deleted user]

I work for the Navy and have been using Jira for about 6 years.  I am a project admin, I do not have full Jira admin permissions.  I use Jira to track our internal control program and limited risk assessments and the workflow associated with these annual assessments.  I know this is not what the Jira platform was originally intended for, but it was better than having people email pdf forms.   It has worked exceptionally well for this application. 

My higher headquarters is rolling out a complex spreadsheet (because everybody in the military loves spreadsheets.) They want to use it to track risk and internal controls across a wide span of the command.  These spreadsheets contain numerous drop downs, and need the ability to list multiple controls for each risk cited.  Jira may not be the platform for tracking this information, but I wanted to see if anyone else had success turning a complex spreadsheet into a Jira project or am I wasting my time. 

[Max Foerster - K15t · Accepted solution]

Hey @[deleted]

I'm pretty sure that you can do it! I was involved in a similar project to bring a really huge spreadsheet into Jira and it worked. The use case was different, but let me tell you the story to encourage you. 😁🤘🏼

The customer was a company from the automotive industry and they used spreadsheets for their project management (prototyping/production of specific parts). So each project was a single huge spreadsheet with multiple project phases, each containing steps (tasks, sometimes parallelized). They were running 100-200 projects like this each year!

Basically a big GANTT chart with lots of formulas and data (project sheets, project plans, risk & design reviews, materials, etc.).

So how did we manage to realize this in Jira?

The new important issue type was "Project" and every task of the spreadsheet was a sub-task. And you can imagine that having an issue type called "Project" in Jira causes lots of funny conversations with people not being used to Jira... 😁

All relevant data needed to be in Jira, so this meant creating a whole bunch of custom fields (the majority was regular built-in Jira custom fields, responsible persons, special options for different kinds of projects) but with a reasonable tabbing on the used screen, it's possible. And that enabled us to use lots of boolean validators when creating a "Project" issue type to ensure high data quality. 

So filling out the screen with the fields was definitely more convenient than the Excel spreadsheet. After that, ~50-60 post functions started to create the sub-tasks.

Descriptions and other data were distributed to the corresponding sub-tasks, start and end dates were calculated (in the correct sequence of with the given time estimates, skipping weekends...) and filled out for every sub-tasks to build the project plan. Everything a bit more special (text composition) was also no problem.

Even building a table for the list of materials (part numbers, EANs, stock, forecasts, etc.) was easily done in a multi-line text field with the help of a post function.

Tasks in different project phases were displayed in special matrices and a single script (the only script in the whole workflow!) at the end of the "Project" creation took all of that and created a GANTT chart in another third-party app to visualize the project. 

No spreadsheets anymore, better user experience, full traceability, comments & notifications, holistic view across all projects, and finally proper reporting capabilities.

The whole thing was realized with only four apps on top of Jira! (Jira Workflow Toolbox for the validators, post functions and calculated custom fields, Issue Matrix for displaying the sub-tasks in different project phases, Scriptrunner for the single script to create the GANTT chart in Big Picture) 

So take this spreadsheet and put it into Jira! 😁

[Deleted user]

Thanks for the feedback.  Thats quite the project.  

[Max Foerster - K15t]

Yep and still possible to achieve and the end-user won't notice the complexity. He'll just notice that it works like charm. Good luck and keep us posted or ask further questions in the community. :)

[Inna Gordin · Accepted solution]

I agree, talk to your Jira Admin - if you have any of the workflow apps installed, you can create more specific workflows. For instance, use workflow conditions and validators in cases like Testing Execution Method: if "Observation" was selected, you can require another field's value added, and ignore this for all other testing methods. You can even create calculated custom fields to display information in reports, especially for things like testing frequency. It’s not just a job, it’s an adventure ;) Good luck Lou!

[Deleted user]

Thank you.  It sure is an adventure.   

[Joe Pitt · Accepted solution]

I don't see why you couldn't turn this into a JIRA project. I've been a JIRA admin since 2007 and I've created several projects with this level of complexity. For instance the Fraud Risk looks like a cascading select field may work for that. Talk to your JIRA admin. Spreadsheets are popular because everyone has Excel on their computer, but they aren't good for running reports. 

[Deleted user]

Thank you. 

[David Pinn · Accepted solution]

Hi Lou,

I think the way forward would be for you to create custom fields for the risk category, fraud risk type, fraud risk factor, and so on; and then add those custom fields to the screens that you use for editing your risks (and risk assessments). There is nothing unauthodox about what you are proposing to do: Jira is the perfect platform for managing data of the kind you've mentioned.

Jira offers many advantages over the beloved spreadsheet approach. Consider the ability to manage workflow, add comments, track changes over time, collaborate with team members, control access very finely, and all within the same environment that you use for other issue-tracking purposes.

Best wishes for your risk management endeavours.

David Pinn
ProjectBalm (makers of Risk Register app for Jira Server and Jira Cloud) 

[Deleted user]

Thank you.  That gives me some hope.