Google-sheets – Filter the data of two different column

google sheetsgoogle-apps-scriptgoogle-sheets-query

I'm trying to filter down the data of two different columns. I have exported the Time Tracking Report from JIRA which consists of 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 down both Issue Type Key Status.
Key Status is unique to each row e.g. EP-1234

Issue Type includes several value types, but I'm generally interested in three:
story, sub-task and sub-bug.

It would be quite easy for me to narrow down the stories (there are about 10 in a given sprint, but each story has a large number of sub-tasks and sub-bugs directly below in the column Issue Type for example:

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

Both stories and -> Sub-tasks are located in the same column and other than an order there is no connection between the story EP-2643 and it's sub-tasks. Is there any filter or query,or script that would allow me to somehow pair this data automatically? I can do it manually of course, but if I have to do it everyday, it won't be efficient.

Best Answer

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

Before

After

After

Related Topic