Filter data that has relationships based solely on relative row position

Question

I'm trying to filter exported JIRA Time Tracking Report data in Google Sheets. The exported data includes the following columns:

Issue Type	Key Status	Priority	Summary	Original Time Estimated (min) Σ	Estimated Time Remaining (min) Σ	Total Time Spent (min) Σ	Accuracy Σ	Accuracy (%) Σ

I'm interested in filtering by Issue Type and Key Status:

• Key Status contains unique values such as EP-1234
• Issue Type values can be duplicates and I'm generally interested in the following three:
  1. Story
  2. Sub-task
  3. Sub-bug

I'm having a problem filtering because in JIRA the Sub-task and Sub-bug values are associated with a Story but in the exported data this relationship is communicated solely by relative row position as Sub-task and Sub-bug belong to the Story that precedes them.

For example, in the table below all the Sub-task values are associated with the same Story EP-2643:

Issue Type	Key Status
Story	EP-2643
-> Sub-task	EP-2717
-> Sub-task	EP-2716
-> Sub-task	EP-2715
-> Sub-task	EP-2714
-> Sub-task	EP-2713
-> Sub-task	EP-2712

It isn't practical to associate the values manually. Is there a formula or script that automatically associate the Sub-task and Sub-bug values with the appropriate Story?

Answer 1

You have Jira data that does not provide a link between each story, and their "sub-Story"s and "sub-Bug"s. You need to create a link that will enable you to filter the data but retain the relationships between the data.

There may be many ways to resolve your problem. Please consider this answer just one solution.

In this example, the script works on the Active Sheet. Each row containing a "sub-Story" or Sub-Bug" is assigned a sequence number for their relevant Story, and also the Key Status for that story. The script assumes that there are headers in row 1, and that the Issue Type are in Columns A and B respectively, and that data commences on row#2. The link number(sequence#) and link key are inserted into columns C and D respectively, but this can be easily changed.

The script can be run from the Editor, or can be built into custom menu.

---

function wa14539802() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet()
  
  var key = "Story";
  
  var sheetLR = sheet.getLastRow();
  var range = sheet.getRange(2,1,sheetLR-1,2)
  Logger.log("DEBUG: the range = "+range.getA1Notation())
  var values = range.getValues();
 
  // create a temporary array and variables to track the link and key 
  var link=[];
  var storyKey= "";
  var keystatus=0;
  
  // Loop thouggh the rows
  for (var i=0;i<sheetLR-1;i++){
    // test if the Issue Type = "Story"
    if (values[i][0] == key){
      // this row is a "Story, do some stuff
      var keystatus = keystatus+1;
      var storyKey = values[i][1]
      link.push([keystatus,storyKey]);

    }
    else{
      // this row is not a story, do some other stuff
    link.push([keystatus,storyKey])  
    }
  
  }
  
  // create the output range
  var linkRange = sheet.getRange(2,3,sheetLR-1,2);
  // update the range with the temporary array
  linkRange.setValues(link);
  return;
  
}

---

Before

After