Why

A few months ago I was on an assignment where there was a clear request to use physical cards for their stories.  It helped to give an overview by laying them on a large table. Always good to hear that people are thinking out of the box when searching for ways to visualize their work.

Luckily I had this available in xls format, written in VB. It provided a way to print them nicely on paper.  
However, Excel is not the most handy tool when it comes to compatibility.  Especially when we’re using VB behind the scenes.  
Also for Mac users it isn’t always working as it should.

And it seems I wasn’t the only one facing this issue…

At “http://www.littlebluemonkey.com/blog/” I found a Google sheet version of this card creator which makes it platform independent.  
Moreover, it was capable of connecting to Jira, extract all data from it! 
Exactly what I needed!  
Looking into the code I saw that in order to get this working on our Jira, quite some hard coding was needed to get this working properly.

I refactored it a bit, so you don’t have to be a Javascript wizz to get this working.  

First of all, the solution consists out of javascript code that makes it all working, and the sheet itself.  As the versioning in Google Sheets isn’t quite up to par, I nicely tucked away the javascript part on Github.  
The sheet itself is versioned on Google Drive and you can find a read-only verions here: 
Jira Backlog Card Creator Co-learning. 

Feel free to copy it to your own Google Drive and play around with it. 

This article tells you how to use this Google Sheet. 

What?

The sheet consists of a Backlog tab, which you can edit manually or fill it by extracting backlog items from Jira. 
The sheet has 2 main functionalities.  

• Extracting data into the Backlog tab
• Generating backlog item cards from what’s been found in the Backlog tab.

Both functionalities are kept independent from each other, so you’re not tied up to Jira in order to get the benefits from of the card generator.

The goodies you’ll find in the menu “Jira” and “Story Cards” on top.

Jira

Jira > Configure Jira

To connect to your Jira you need to configure it first. You do this through:

• Jira Config tab:  “Jira host” and select the “JQL query” from the drop down. 
  The JQL drop down is populated with the cells under “JQLlist”
  That’s right, you can give your own custom JQL!
• Backlog tab: 
  Assure that in the header of the Backlog tab existing Jira fields are placed.
• Connecting to Jira with the menu “Jira” > “Configure Jira” menu. 
  You’ll be asked to fill out your user & pass. The format is user:pass
  Note that this user is not your mail address, but the user found in your Jira profile.

Jira > Refresh Data Now

Hit this menu after doing the above to refresh the Backlog tab with the latest data from your JQL query.

Story Cards

Story Cards > Create cards

This populates the Generated Cards tab with data out of the Backlog tab.
In order to get this working you’ll need to assure that in the Card Template tab fields are found that are in the header of the Backlog tab.  

You do this in format.  
The card creator will run over the Backlog header cells, search for header names surrounded by square brackets on the template, and replace them with the backlog values from the corresponding backlog field.

So for the card creator to work the header names of the Backlog tab and Card Template should correspond.
E.g. If you have in the Card Template tab a  field, than you need in Backlog tab header Description field.
If you’re into extracting data from Jira, those header names need to be real Jira fields.

Conditional formatting

The creative minds probably guessed it: With conditional formatting of Google sheets, you can give the cards any format you like.
Depending on the content of the card the possibilities are nearly endless!

The conditional formatting you setup on the Card Template tab; formatting of any kind will be copied over to the Generated Cards tab.

Printing

NO Page breaks?

One wouldn’t believe it:  Google Sheets has NO page break functionality.
This makes printing of cards nearly impossible: cards are partly printed at the bottom of the page, and partly at the top of the following page.  

Conclusion:  I’ve written a solution myself.

The mechanism basically puts cards on a virtual page till the height of the page is reached.
If it’s not possible to add a card because the virtual page is full, the rest of the page is filled with an empty row, high enough to fill up the page.
Like that, the following card is neatly positioned at the beginning of the page.  

Configuring it

Because of this, you need to know the height of a page in pixels on the Jira Config tab
E.g. A4 portrait means 1026 px, A4 landscape means 697px.

This is why you need to select the height of the page in the “Print format” drop down.

But that’s not all:  While printing you need to select the same format you chose in the above configuration. 

Some Limitations

We learned that in your Backlog, when you have a cell filled in a column with a empty header cell, you’ll be presented with an error that leaves you clueless. 
 

That’s it!

Hope you enjoy this tool and feel free to update and experiment your own version.
Comments, feedback are mostly welcome!