Greenhoper Sprints Export to Excel

Constantin Scheuermann February 27, 2013

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

4 answers

1 accepted

0 votes
Answer accepted
codelab expert
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 3, 2013

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

0 votes
Constantin Scheuermann March 4, 2013

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 :)

0 votes
Mark DeMichele March 3, 2013

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);
		}
		
		
	});
	
});

0 votes
codelab expert
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 27, 2013

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 February 28, 2013

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 March 3, 2013

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

Suggest an answer

Log in or Sign up to answer