Monday, January 23, 2017

Need to import things from JIRA into Google sheets?

Attach this to a google sheet and then just pass in the filter number.

This should get past both jira's 1000 issue limit and importxml's limit. The only limitation is script runtime which is 5 minutes. Have imported up to 1500 issues with no problem.

function parseXml(searchNumber) {
  var searchURL = 'https://jira.wherever.com/sr/jira.issueviews:searchrequest-xml/' + searchNumber + '/SearchRequest-' + searchNumber + '.xml?tempMax=500&os_username=username&os_password=password';
  var xml = UrlFetchApp.fetch(searchURL).getContentText();
  var document = XmlService.parse(xml);
  
  // get total
  var total = document.getContent(2).getChild("channel").getChild("issue").getAttribute("total").getValue();
  var pages = Math.ceil(total/500);
  
  var sheetData = new Array();
  
  //loop through pages
  for (var i = 0; i < pages; i++)
  {
    var pageURL = searchURL + '&pager/start=' + i * 500;
    var xml = UrlFetchApp.fetch(pageURL).getContentText();
    var document = XmlService.parse(xml);
    var items = document.getContent(2).getChild("channel").getChildren("item");
    
    var xmlElementNames = [
                             "title", 
                             "link",
                             "project",
                             "description",
                             "key",
                             "summary",
                             "type",
                             "priority",
                             "status",
                             "resolution",
                             "assignee",
                             "reporter",
                             "created",
                             "updated",
                             "fixVersion",
                             "timeoriginalestimate",
                             "timeestimate",
                             "comments",
                             "Sprint"
                            ]
    
    sheetData.push(xmlElementNames);
    
    for (var j = 0; j < items.length; j++) 
    {
      var rowData = [];
      for (var k = 0; k < xmlElementNames.length; k++)
      {
        var itemElement = items[j].getChild(xmlElementNames[k]);
        if( itemElement )
        {
          rowData.push(itemElement.getText());
        } 
        else
        {
          // check the customfields
          var customfields = items[j].getChild("customfields").getChildren("customfield");
          var customfieldfound = false;
          for (var l = 0; l < customfields.length; l++)
          {
            if( customfields[l].getChild("customfieldname").getValue() == xmlElementNames[k] )
            {
              rowData.push(customfields[l].getChild("customfieldvalues").getChild("customfieldvalue").getText());
              customfieldfound = true;
            }
          }
          if( customfieldfound == false )
          {
            rowData.push("");
          }
        }
      }
      sheetData.push( rowData );
      //Logger.log(items[j].getChild("title").getText());
    }
  }
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  var sheetRange = sheet.getRange(1, 1, sheetData.length, sheetData[0].length );
  sheetRange.setValues(sheetData);
  
}