Greenhoper Sprints Export to Excel

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

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

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.

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

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

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

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

Suggest an answer

Log in or Join to answer
Community showcase
Teodora [Botron]
Published Thursday in Marketplace Apps

Jira Inferno: The Nine Circles of Jira Administration Hell

If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...

590 views 2 15
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot