Greenhoper Sprints Export to Excel

[Constantin Scheuermann]

Hi there,

I am doing a EXCEL Export of my JIRA Issues. Some of these issues are already assigned to an activated(!) Sprint. I can see the Sprintname in the Sprint Column within the export.

But my Problem is, that in case i assign an issue to a Sprint, that is has not been started yet, i can see it in the export but not the Sprint it is already assigned.

Is there a way to find out, to which Sprint a Issue is assigned even if the Sprint has not been started yet?

Thanks for you help in advance

[codelab expert · Accepted solution]

Hi Constantin,the response object has an attribute named issues. This attribute contains all issues of the backlog of your current rapidview in plan mode. Now go to the end of this list and fetch an item after another item until the issuekey match your afterissuekey of the desired sprint. This is your first issue! Now fetch on until new afterissuekey is detected or you reached issue 0. Hope this helps!Best regards, Mathias

[Constantin Scheuermann]

Nice, i think i found a similar solution to the problem

Wrote a php script that does the job.

First i get all Elements from greenhopper (all but the ones that are already closed and/or are assigned to a closed Sprint)

rest/greenhopper/1.0/xboard/plan/backlog/data?rapidViewId=

I look for the Markers and use the Rest of JIRA to get more issue details

rest/api/latest/issue/

The runtime of the script is horrible but it works :)

[Mark DeMichele]

I'm not sure if you are familiar with dojo, but I have a (dojo) js class file that get's that list of issues. It has a hard-coded rapid board Id right now, but if you follow the code, it should be obvious. Look at the routine names _getIssuesForMarker, where markerIndex is the index into that markers array.

For some reason, it doesn't look like my attachment is uploading. Here's the code.

define([
	"dojo/_base/declare",
	"dojo/_base/xhr",
	"dojo/string",
	"dojo/when",
	"dojo/_base/array"
], function (
	declare,
	xhr,
	string,
	when,
	array
) {

	return declare("xl.RapidBoardPlanning",null, {
	
		_rapidBoardId: 39,
		
		_baseUrl: "/rest/greenhopper/1.0/xboard/plan/backlog/data.json?rapidViewId=${id}",
		
		_issueLookup : null,
		
		constructor: function(params) {
		},
		
		_getUrl: function() {
			return string.substitute(this._baseUrl,{id:this._rapidBoardId});
		},
		
		_data: null,
		
		invalidate: function () {
			this._data = null;
		},
		
		_getData: function () {
			
			if (this._data)
				return  this._data;
				
			var this$ = this;
			
			return xhr.get({
				url:this._getUrl(),
				handleAs: "json" ,
				headers: { "Content-Type": "application/json"}
			}).then(function(data) {
				this$._setData(data);
				return data;
			});;
		},
		
		_setData: function (data) {
		
			this._data = data;
			
			//Build a lookup table for accessing issues.
			var lookup = {};
			
			array.forEach(data.issues,function(item,index) {
				lookup[item.key] = index;
			});
			
			this._issueLookup = lookup;
		},
		
		getPendingSprints: function () 
		{
			var this$ = this;
			return when(this._getData(),
				function (data) {
					var d = this$._data;
					return d.markers;
				}
			);
		},
		
		_getMarkerIndex: function(sprintId) {
			var markers = this._data.markers;
			for(var i=0; i < markers.length; i++) {
				if (markers[i].id == sprintId)
					return i;
			}
			return -1;
		},
		
		_getIssuesForMarker: function(markerIndex) {
			var startIndex = 0;
			var endIndex = 0;
			
			if (markerIndex > 0)
				startIndex = this._issueLookup[this._data.markers[markerIndex-1].afterIssueKey]+1;
			endIndex = this._issueLookup[this._data.markers[markerIndex].afterIssueKey];
			return this._data.issues.slice(startIndex,endIndex+1);
			
		},
		
		_calcLoadingData: function (issues) {
		
			var assignees = {};
			
			array.forEach(issues, function(value, index) {
				var id = value.assignee;
				//Attempt to look up assignee
				var assignee = assignees[id];
				//if none found initialize record and add it.
				if (! assignee) {
					assignee = { id: id, name:value.assigneeName, storyPoints: 0, timeRemaining: 0, noStoryPointsCount: 0, noTimeRemainingCount:0};
					assignees[id] = assignee;
				}
				var sp = value.estimateStatistic.statFieldValue.value;
				if (sp)
					assignee.storyPoints += sp;
				else
					assignee.noStorePointsCount++;
					
				var tr = value.trackingStatistic.statFieldValue.value;
				if (tr) {
					//decode to days here.
					tr = tr / 3600 / 5.5;
					assignee.timeRemaining += tr;
				} else {
					assignee.noTimeRemainingCount++;
				}
				
			});
			
			var data = [];
			for(var assignee in assignees) {
				data.push(assignees[assignee]);
			}
			return data;
		},
		
		getMarkerLoading: function(sprintId) {
			var markerIndex = this._getMarkerIndex(sprintId);
			var issues = this._getIssuesForMarker(markerIndex);
			return this._calcLoadingData(issues);
		}
		
		
	});
	
});

[codelab expert]

You can use the REST API to receive the backlog. In the JSON data you get a list of sprint marker (inactive sprints). With this list you can can fetch the issues of the backlog and receive the desired sprint issues.

[Constantin Scheuermann]

Hi Mathias, thanks for your answer. Do you have an example of how i can get all issues assigned to one specified project via REST? And do you know a way to convert this JSON to Excel? Thanks in advance

[Constantin Scheuermann]

Hi Mathias,

I managed to get the sprint markers array. But i dont get the connection.

"markers": [
{
"afterIssueKey": "MAT-8826",
"name": "Sprint 4",
"id": 22
},
{
"afterIssueKey": "MAT-8797",
"name": "Sprint 5",
"id": 23
}

What does the afterIssueKey mean? And how can i access/receive all issues assigned to the Sprint 4 (inactive) with id 22?

Thanks in advance