How to use Google Spreadsheets for TDD

The idea is from 2008 by Dave Nicolette and Karl Scotland and they first presented it in their talk “Manager’s Introduction to Test-Driven Development” where they are showing managers how to do TDD with Excel and VBA. Inspired by this talk see how to do it with Google Spreadsheet and JavaScript.

In the following I am just showing how to do it. It’s not a Kata I am going to cover here. Though I use Roman Numerals, which makes it very easy here. You can find the spreadsheet here (and clone it).

Defining the requirements

TDD_with_GSpreadhseet__uxebu_blog__-_Google_Drive-300x144

The first step as it should be in TDD is that we define the requirements for our task. No special knowhow needed yet, just create a simple spreadsheet, with a nice headline (makes it more readable) and one column with the arabic and one more with the roman numerals. We get out requirements that we start with from wikipedia’s page about roman numerals.

Use the JavaScript function

TDD_with_GSpreadhseet__uxebu_blog__-_Google_Drive4-300x109

The language we do TDD here, is JavaScript, logically if the spreadsheet runs in the browser (maybe Dart will be available some day too :)).

It is as simple as using a built in spreadsheet function, just write “=arabicToRoman(A3)” where “A3″ is the cell referred to. As long as the function is not defined it will result in an error, when executed.

TDD_with_GSpreadhseet__uxebu_blog__-_Google_Drive5-300x91

Implement the function

TDD_with_GSpreadhseet__uxebu_blog__-_Google_Drive6-300x78

Let’s get rid of the “#NAME?” so we can continue. Actually hovering over the cell will tell us that the function “arabicToRoman” doesn’t exist. This is our first thing the test tells us to do. So let’s implement this function.

TDD_in_GSpreadsheet__uxebu_blog_-300x121

The function we need is written in pure JavaScript. Click in the menu “Tools / Script editor…”, a new browser tab will open up. Click in the left column (“Create script for”) on “Spreadsheet”. It opens with two functions pre-implemented.
We don’t need them now, so let’s just remove them. And implement the JavaScript function “arabicToRoman”, see the screenshot.

Show the test result

TDD_with_GSpreadhseet__uxebu_blog__-_Google_Drive7-300x66

In order to have red/green which is an essential part of TDD, we are going to use the simple spreadsheet functionality (IF) to show the test result (“PASS” and “FAIL”) in the fourth column. Once this content is written into the cell we use simple “conditional formatting” in order to change the background color red or green (you can reach “Conditional formatting…” via the column’s drop down menu).

TDD_with_GSpreadhseet__uxebu_blog__-_Google_Drive8-300x83

Now we get a red cell. The awesome thing about the spreadsheet solution here, is that we can simply copy the two cells of column C and D onto the next row that we want to implement next. This allows us to write the requirements without the need to test them right away. It’s pretty comfortable, try it out. You see your to dos and as soon as you work on them you fill in column C and D.

Let’s make it pass

TDD_in_GSpreadsheet__uxebu_blog__und_TDD_with_GSpreadhseet__uxebu_blog__-_Google_Drive-300x78

Now we can simply switch back to the other tab, edit the function and just wait until the spreadsheet picks it up and renders the table again. For doing that I found it quite practical to put the two windows beside one another. Then you don’t have to reload manually. Just wait a second or so and the spreadsheet updates automatically as soon as you have saved the source code.

If you thought TDD was hard to setup, now there is no argument that backs this up :). Have fun.

PS: I made the spreadsheet public here, though this might not be very useful since I wasn’t able to publish the macro / JavaScript function.

Update: J.B. Rainsberger also has a nice video on it.