How to use Google Spreadsheets for TDD

How to use Google Spreadsheets for TDD

The concept introduced in a talk "Manager’s Introduction to Test-Driven Development," demonstrating TDD with Excel and VBA.

Author builtbright


March 24, 2014

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


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


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.

Implement the function


Let’s get rid of the “#NAME?” so we can continue. Actually hovering over the ce
ll 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.


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


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).


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


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.

Author builtbright


As a digital agency we development digital products for the web. We support companies in all phases of the product development process.

Make your next project a success

Digitize, automate and scale processes with tailor-made web applications.

Personal contact person and individual product development

State-of-the-art technologies for powerful apps

User-centered and intuitive designs

Scalable solutions that grow with your company

Transparent communication and clear processes


Other articles