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.

  • As ever, please share a link to a copy of your sheet (or a sample sheet with enough simulated data to make your real-world situation clear). Be sure to set share permissions to "Anyone with the link can edit." It lies somewhere between time-consuming and impossible to write formulas without access to a real sheet and data.
    – Erik Tyler
    Aug 4, 2020 at 17:48
  • Welcome. I think that this is partly a Jira export issue. Isn't there a field that identifies the parent of a sub-task, that field should be part of your export.
    – Tedinoz
    Aug 17, 2020 at 1:34

1 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]

      // this row is not a story, do some other stuff
  // create the output range
  var linkRange = sheet.getRange(2,3,sheetLR-1,2);
  // update the range with the temporary array





Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.