nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

codelab expert

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

All comments

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

codelab expert

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 &lt; markers.length; i++) {
				if (markers[i].id == sprintId)
					return i;
			}
			return -1;
		},
		
		_getIssuesForMarker: function(markerIndex) {
			var startIndex = 0;
			var endIndex = 0;
			
			if (markerIndex &gt; 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);
		}
		
		
	});
	
});

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira-software