Customizing the Google Spreadsheet Story Card Generator

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

At my current project we use a Google spreadsheet to manage our backlogs. This works really well for storing and sharing the backlog, but it’s not very good for visualizing it. So we print out the stories on cards by copying and pasting each row into a document table cell and reformatting, adding extra labels, and manually inserting priority. Well, that’s what we did the first couple of times, until I found David Vujic’s fantastic Index Card Generator for Google spreadsheets (http://davidvujic.blogspot.se/2011/06/visa-vad-du-gor-eller-dude-wheres-my.html).

Except, we have multiple backlogs in one sheet, our column names aren’t the same, and we use a different layout for the cards. Here’s how we customized David’s script!

Setting up the script

David’s spreadsheet contains everything you need (including resource links and instructions) https://docs.google.com/spreadsheet/ccc?key=0AiK_4OSo0f4LdFVOY3hXclRZcjdYQ1I2Q1VodElUdnc&hl=sv#gid=9. Make a copy of the spreadsheet, then go to tools-> Script editor:

This opens a new view with the script code. Copy the source code from this sheet into your own backlog spreadsheet, by copying the source and opening the script editor (select blank project, or just close the dialog) in your own document and pasting the code.

Setting up your spreadsheet

The card generator spreadsheet has 3 important sheets.

In order to run the script, you will need to have a sheet named Backlog, with the following columns: Id, Name, User story, How to test, Importance, Estimate:

The name and case of the columns are important, as is the name of the sheet. Update your backlog sheet to match the column names, or copy over the template sheet to your spreadsheet for testing.

The Cards sheet is generated by the script, so you don’t need to copy it over.

The Template sheet determines the formatting of the generated cards, make sure to copy that over:

Deploying the script

Now that you have copied over the setup to your own spreadsheet you need to publish the script, then update the spreadsheet (refresh the page), and finally run it for the new menu option.

To publish the script go to the script viewer tab/window and select publish:

Set a product version, then deploy, while you’re testing it’s ok to just give access to yourself, you can always redeploy later on to change the access rights. You’ll also need to redeploy every time you make changes.

Running the script

Now you’re set to run the script.

Refresh your document, and you’ll see that you have a new menu item called Story Cards, try generating some cards:

The first time you run the script you’ll be prompted to authorize the script for the Google account that you’re using with this script. Go ahead and authorize, then run create cards again. If you don’t have the Cards sheet, that will now be created, and you’ll have to run the script again. Finally you should see your cards in the Cards sheet!

Understanding the Script

Now that the script is tested and running you can start making changes. The script places data using the card template by using a grid location system. If you would like to change the way the template looks, and make modifications to the placement of the items, you’ll need to change both the template and the script. The cell values in the script are the 1 based location in the template (row,column). The index names are the actual names of the columns from the backlog sheet:

There are two functions that start the card generating process, one will generate cards for all the rows in the sheet called Backlog, the other one will generate cards for only the selected rows:

Modifying the script

Since our spreadsheet has more than one backlog, we removed the ability to create all cards from a sheet called backlog. Instead, the script now generates cards for the selected rows in the active sheet, as long as the sheet name contains the word backlog:

<pre>function createCardsFromSelectedRowsInBacklog() {
if (!assertCardSheetExists()) {
return;
}

var activeSheetName = getActiveSheet().getName().toLowerCase();
if (activeSheetName.indexOf("backlog") === -1) {
Browser.msgBox("A Backlog sheet needs to be active when creating cards from selected rows. Please try again. (Make sure the sheet name contains the word backlog for it to be recognized as a backlog sheet)");
return;
}</pre>

The redesigned the template so that there was less white space, and more space for text:

We renamed and modified the column names: Estimate, Title, User story, How to test

The script now also auto calculates the order (which in our case is the inherent priority), and prints that out. This works well for us as we only use the physical cards for our sprint backlogs, so we always print the cards in the priority order. The new code is as follows:


function setCardOrder(cardOrder, card) {
card.getCell(8, 1).setValue(cardOrder);
}

function setCardName(backlogItem, card) {
var max = 49;
var storyName = backlogItem['Title'];

if (storyName && storyName.length > max) {
storyName = storyName.substring(0, max) + '...';
}
card.getCell(2, 1).setValue(storyName);
}

function setUserStory(backlogItem, card) {
card.getCell(4, 1).setValue(backlogItem['User Story']);
}

function setHowToTest(backlogItem, card) {
card.getCell(6, 1).setValue(backlogItem['How to Test']);
}

function setEstimate(backlogItem, card) {
card.getCell(7, 1).setValue("Estimate:" + backlogItem['Estimate'] );
}

The complete modified script can be expanded below:

function getTemplateArea() {
return "A1:B8";
}

function setCardOrder(cardOrder, card) {
card.getCell(8, 1).setValue(cardOrder);
}

function setCardName(backlogItem, card) {
var max = 49;
var storyName = backlogItem['Title'];

if (storyName && storyName.length > max) {
storyName = storyName.substring(0, max) + '...';
}
card.getCell(2, 1).setValue(storyName);
}

function setUserStory(backlogItem, card) {
card.getCell(4, 1).setValue(backlogItem['User Story']);
}

function setHowToTest(backlogItem, card) {
card.getCell(6, 1).setValue(backlogItem['How to Test']);
}

function setEstimate(backlogItem, card) {
card.getCell(7, 1).setValue("Estimate:" + backlogItem['Estimate'] );
}

function getTemplateStartColumn() {
return getTemplateArea().substring(0,1);
}

function getTemplateStartRow() {
return parseInt(getTemplateArea().substring(1,2), 10);
}

function getTemplateLastColumn() {
return getTemplateArea().substring(3,4);
}

function getTemplateLastRow() {
return parseInt(getTemplateArea().substring(4), 10);
}
// END: Template functions

// START: Get sheets
function getSpreadsheet() {
return SpreadsheetApp.getActiveSpreadsheet();
}

function getActiveSheet() {
return getSpreadsheet().getActiveSheet();
}

function getBacklogSheet() {
return getSpreadsheet().getSheetByName("Backlog");
}

function getTemplateSheet() {
return getSpreadsheet().getSheetByName("Template");
}

function getCardSheet() {
return getSpreadsheet().getSheetByName("Cards");
}

function getPreparedCardSheet(template, numberOfItems, numberOfRows) {
var rowsNeeded = numberOfItems * numberOfRows;

var sheet = getCardSheet();
sheet.clear();

setColumnWidthTo(sheet, template);

var rows = sheet.getMaxRows();

if (rows < rowsNeeded) {
sheet.insertRows(1, (rowsNeeded - rows));
}

setRowHeightTo(sheet, numberOfRows, numberOfItems);

return sheet;
}
// END: Get sheets

// START: Get range within sheets
function getTemplateRange() {
return getTemplateSheet().getRange(getTemplateArea());
}

function getHeadersRange(backlog) {
return backlog.getRange(1, 1, 1, backlog.getLastColumn());
}

function getItemsRange(backlog) {
var numRows = backlog.getLastRow() - 1;

return backlog.getRange(2, 1, numRows, backlog.getLastColumn());
}

function getSelectedItemsRange(backlog) {
var range = getSpreadsheet().getActiveRange();
var startRow = range.getRowIndex();
var rows = range.getNumRows();

if (startRow < 2 ) {
startRow = 2;
rows = (rows > 1 ? rows-1 : rows);
}

return backlog.getRange(startRow, 1, rows, backlog.getLastColumn());
}
// END: Get range within sheets

function setRowHeightTo(cardSheet, numberOfRows, numberOfItems) {
var templateSheet = getTemplateSheet();

for (var i = 0; i < numberOfItems; i++) {
for (var j = 1; j < (numberOfRows+1); j++) {
var currentRow = (i*numberOfRows)+j;
var currentHeight = templateSheet.getRowHeight(j);
cardSheet.setRowHeight(currentRow, currentHeight);
}
}
}

function setColumnWidthTo(cardSheet, templateRange) {
var templateSheet = getTemplateSheet();
var max = templateRange.getLastColumn() + 1;

for (var i = 1; i < max; i++) {
var currentWidth = templateSheet.getColumnWidth(i);
cardSheet.setColumnWidth(i, currentWidth);
}
}

/* Get backlog items as objects with property name and values from the backlog. */
function getBacklogItems() {
var backlog = getActiveSheet();

var rowsRange = getSelectedItemsRange(backlog);
var rows = rowsRange.getValues();
var headers = getHeadersRange(backlog).getValues()&#91;0&#93;;

var backlogItems = &#91;&#93;;

for (var i = 0; i < rows.length; i++) {
var backlogItem = {};

for (var j = 0; j < rows&#91;i&#93;.length; j++) {
backlogItem&#91;headers&#91;j&#93;&#93; = rows&#91;i&#93;&#91;j&#93;;
}

backlogItems.push(backlogItem);
}

return backlogItems;
}

function assertCardSheetExists() {
if (getCardSheet() == null) {
getSpreadsheet().insertSheet("Cards", 0);
Browser.msgBox("The 'Cards' sheet was missing and has now been added. Please try again.");
return false;
}

return true;
}

function createCardsFromSelectedRowsInBacklog() {
if (!assertCardSheetExists()) {
return;
}

var activeSheetName = getActiveSheet().getName().toLowerCase();
if (activeSheetName.indexOf("backlog") === -1) {
Browser.msgBox("A Backlog sheet needs to be active when creating cards from selected rows. Please try again. (Make sure the sheet name contains the word backlog for it to be recognized as a backlog sheet)");
return;
}

var backlogItems = getBacklogItems();
createCards(backlogItems);
}

function createCards(backlogItems) {
var numberOfRows = getTemplateLastRow();
var template = getTemplateRange();
var cardSheet = getPreparedCardSheet(template, backlogItems.length, numberOfRows);

var startRow = getTemplateStartRow();
var lastRow = getTemplateLastRow();
var startColumn = getTemplateStartColumn();
var lastColumn = getTemplateLastColumn();

for (var i = 0; i < backlogItems.length; i++) {
var rangeVal = startColumn + startRow + ":" + lastColumn + lastRow;
         var card = cardSheet.getRange(rangeVal);
template.copyTo(card);

setCardOrder(i+1, card);
setCardName(backlogItems&#91;i&#93;, card);
setUserStory(backlogItems&#91;i&#93;, card);
setHowToTest(backlogItems&#91;i&#93;, card);
setEstimate(backlogItems&#91;i&#93;, card);

startRow += numberOfRows;
lastRow += numberOfRows;
}

Browser.msgBox("Story cards successfully created in the sheet named 'Cards'");
}

/* Will add a Cards menu. Runs when the spreadsheet is loaded. */
function onOpen() {
var sheet = getSpreadsheet();
var menuEntries = &#91; {name: "Create cards from selected rows", functionName: "createCardsFromSelectedRowsInBacklog"} &#93;;

sheet.addMenu("Story Cards", menuEntries);
}
<pre>

That’s how you do it!

You can find the modified spreadsheet and script here: https://docs.google.com/spreadsheet/ccc?key=0At-EEnHR4tuzdFFuV1c4U3JEcXhzYTNpdzJWWFFjYWc&usp=sharing

Feel free to try it out and extend it!

3 Comments

  • 1
    2015-12-28 - 10:23 | Permalink

    Great post! IDG has unforgettable steely removed their entire blog section and posts are no longer accessible. Here’s the post about Index Card Generator, can you update your post with this link?

    http://davidvujic.blogspot.se/2011/06/visa-vad-du-gor-eller-dude-wheres-my.html

    • 2
      2015-12-28 - 10:26 | Permalink

      Oh no, I didn’t notice the iPad autocorrect before sending 🙂 “IDG has unfortunately removed their entire blog section…” is what I meant to write.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *