The Andertons.co.uk Project Sprint Google Drive Spreadsheet

Quick Start: Using the spreadsheet

If all you want to do is start using the sheet to manage a Scrum sprint, just follow the instructions below…

Note: You will be asked at some point to “authorise” the application to allow it to make modifications to the spreadsheet. You’ll need to say ‘yes’ for things to work as described. Don’t worry: the script can only modify this particular sheet.

• A: Open our demo Scrum spreadsheet file in Google Drive
• B: Make your own copy by clicking File > Make A Copy
  (You’ll need to be logged in to a Google account)

Then…

1. Go to one of the Project Sheets. Type a new task into column B.
   You should see a new task number appear and some of the columns will auto-populate. Well done. You have created a task.
2. Fill in other data. Who knows the requirements of the task? Who is responsible for delivery? When should it be done by?
3. You’re ready to add the task to your sprint. Change its status to In Progress. Now look at the Sprint Dashboard. The task should be there (because: magic).
4. Your task is roadblocked (oh no). Let everyone know by changing its status to Roadblocked on the dashboard.
5. Head back to the project sheet. The task’s status has automatically been updated to Roadblocked here too!
6. Jon removed your roadblock. YES JON. Your task is complete. Change its status to Completed and buy Jon lunch.
7. Your sprint is over. Head over to the sprint sheet, and click the Clean Dashboard button. Concluded tasks disappear from the dashboard, but remain on the project sheets in case you wish to reinstate them later.
8. You can create as many new sheets as you want. They will all be considered “Project Sheets”. You’ll need to keep the column headers the same for all sheets so that task data is copied into the correct columns on the dashboard. Need different headers? Read the Editing The Code section below.

That’s all you need to know for basic use to run your sprints. If you’re not going to read the next bit. PLEASE BACK UP YOUR WORK REGULARLY.

The application is powerful enough to go around deleting stuff you don’t want it to if you try and do anything beyond the basic use steps above.

Advanced: Customisation, Code, Nerd Stuff.

Some boring stuff about how it works…

1. There are 3 “types” of sheet: the dashboard, project sheets, and the data sheet. The project sheets are where you can enter new tasks, and the Sprint Dashboard is your ‘view’ of tasks that are currently in progress (in your sprint). The Data sheet contains some predefined lists for dropdowns etc. and the “next task number” so that every task has a unique number.
2. The unique task number is important. Because the code references it to be able to find the correct row to change on the dashboard when you make a modification on the project sheet (and vice versa).
3. Column positions are also important. Don’t add or remove columns without reading the Editing the Code section below.
4. Don’t rename the Dashboard or the Data sheet. At least not without addressing the references to those names in the Javascript code.

Stuff you might want to change

• Head over to the Data sheet. Put your own team’s usernames into the list. The dropdowns in the project sheets will take values from here.
• The Fibonacci scoring hasn’t really been working for us because we use this system to manage tasks that span more than ten projects at once. You could use any ratings you wanted (fish, dog, elephant!) by changing the list in the Data sheet.
• The available statuses (and their colour formatting) is also determined by the list on the Data sheet. If you want more statuses, you can append the list. Note that most of the statuses are referenced by name in the code (e.g. In Progress is the status that triggers the auto-copy-to-dashboard function) so you may break stuff by changing them. Make sure you’ve read the code before doing so!

Stuff we want to improve / add

• Start Sprint Button — We want to create a button to search for tasks with a target date that falls between the sprint dates, and automatically pull it into the dashboard.
• Velocity — We’d like to keep historical records of the sprint “scores” (the ‘judging velocity’ idea in Scrum)
• Friendly names — I’d like to use friendlier team member names in the dropdowns (but we additionally use Zapier and rely on those usernames to tie this all up with Slack).
• Overdue — Create a button that sets tasks with target dates in the past to Overdue status.
• Don’t allow task numbers to be edited by users — Editing the task number throws the “please don’t edit” alert, then wipes the cell meaning the task no longer has a number. Need to somehow capture original value and revert the cell to that. Don’t think undo is available via API?
• Minify the google apps script code — At least for our in-production version

Editing the code

Let’s be realistic — if you’re interested in using this sheet, you’re probably going to want to modify it to suit your needs. To dive in, click “Tools” then “Script Editor”.

If you know a little Javascript, you’ll be able to customise the sheet, even you aren’t familiar with the Google Apps Script API. (But hey, it’s cool. Read it anyway!)

It’s slightly different to coding JS for the browser as your context is not the window object, but once we got going it wasn’t hard to pick up. Here are some tips:

onEdit(e)

You’ll notice that a lot of the work spins off the onEdit() event handler function. It runs every time the user makes a change to the spreadsheet.

It’s worth noting that onEdit() is a special function and the e argument is an event, passed by Google sheets.

If you end up in a situation where you can’t change a value on the sheet because the script is taking over a cell you want to edit manually, the quickest thing to do is to temporarily return; the onEdit function before it does anything i.e.

function onEdit(e) {
  return;
  ...

}

Fixed References (Column Indexes & Spreadsheet names)

We opted to reference column index numbers (rather than header names), which means that a change in the column order, or number of columns may affect the operation of the sheet.

For example, check out line 93:

// If the user is editing a status on a project task sheet..

else if(e.range.getColumn() === 11 && isTaskSheet(thisSheetName())) { ... }

This is special handling for onEdit if the user is changing a task status. If the task status is no longer in column 11, it won’t work the same! You’ll need to look for references like this if you’re changing the column order at all.

Debugging

We left an alert() function in the script as it was useful for quick debugging. It instantiates a new UI window which displays the content in the function argument.

alert("Hello. The column number is: " + e.range.getColumn());

It’s worth noting that there are objects that can’t be called in this context; if you aren’t getting the result you’re expecting using alert(), check the logger by selecting View > Logs from the Script Editor window.

BYE

I really hope you can find value in using this sheet. Feel free to drop me a line on andrewc@andertons.co.uk if you have any questions. I’ll try to help.