Workflow process using JXL and Scriptrunner?
Hi,
I want to see if I can get some feedback from the community regarding a process that I’m looking to implement. We have done something similar with a different platform but are looking to see if this is possible in the Atlassian ecosystem. I’ve created a simplified set of tables to help visualize the process:
For context, the green coded cells are the sources of a data point, the yellow cells are inherited from the data source and anything in white just stays within the sheet. Each project in the main sheet requires a score from sections 1-3 in order to generate an overall score and category for the project. The steps are as follows:
A new submission for a project adds an entry to the ‘Main Sheet’ that will include the project name and key, while the remaining data points are blank.
Each section sheet will then have an entry added that inherits the key and project name from the main sheet (Not certain if the key has to be unique across the sheets). The questions for the section entries will initially be blank.
Email forms are sent to retrieve the responses for the section entries.
Once all section questions have been answered, a score is calculated based on the responses (e.g., - Yes = 2, No = 1, N/A = 0).
Once a score is available for the section entry, the value is added to the corresponding section score column in the main sheet.
The categories for each section in the main sheet are derived from the corresponding score (e.g. score of 2 is assigned moderate)
Once all scores are available, then the overall score in the main sheet is generated based on the average of the section scores.
The overall category is determined based on where the average score falls in a range (e.g. Less than 1 equals low, between 1 and 2 equals moderate, greater than 3 equals high).
I have looked at JXL as an app that can help with this use-case since it provides a familiar spreadsheet interface. However, I have come across some limitations related to calculating values for certain fields. This is simple to do in the spreadsheet software but isn’t as straightforward to handle here. I came across another post related to calculations in JXL where Hannes Obweger from JXL mentioned Scriptrunner as a possible solution that is used both internally at the company and by customers. I checked it out and it seems great but I am not sure if it is a viable solution for this use-case. For the question fields in each of the section sheets, I’m using a single custom field called ‘Yes or No’ that is a drop-down containing the values ‘Yes’, ‘No’, ‘N/A’, and then modifying the column name in JXL to closely match the corresponding question that has to be answered (not sure if this an anti-pattern here but I just didn’t want to create a dropdown for each question since there are many) Some initial questions to help me decide if it’s worthwhile to invest the time down this path are the following:
Is the simplified process discussed above feasible to implement with the combination of JXL and Scriptrunner? It doesn’t necessarily have to involve Scriptrunner if there is an easier native solution but I would definitely like to use JXL since I find the native Jira list a bit clunky and tedious to use.
The responses to the questions don’t necessarily have the same score across all questions (i.e., ‘Yes’ in one question might be a score of 2 but in another question it might be assigned a 1). Does the integration of Scriptrunner with JXL allow for differentiating a re-used custom field based on the edited column name in JXL? Basically, identifying a response based on the edited column name to be able to assign an appropriate value for the calculation.
Any feedback would be appreciated, thanks!