Using Google Spreadsheets To Make Testing Easier

One of the projects I’m working on has been woefully neglected in terms of building out unit tests. However, I’ve finally had a solution dropped in my lap as to how I can handle it. Alex Russel’s blog directed me to the Uxebu blog and this little gem: JSONP for Google spreadsheets.

So, to state my problem, I didn’t really feel very excited about going in and hand-creating a few hundred QUnit test cases when they’re all very near equivalent. My initial approach was going to be to write all of my scenarios in a spreadsheet and then export it with some sort of string processing to get the code I need, but having seen Uxebu’s approach the gauntlet had been thrown. They were directly using Google spreadsheets to handle code generation and then using a JSONP-style approach to process it. I wanted some sort of easy to use GUI to handle creating my test cases and I wanted an easy way to include it. A perfect match! Making it happen turned out to be quite simple but not without stumbling blocks.

Beginning to work on it I created my spreadsheet, a simple exercise since my data is tabular: for each test I would need a bit of metadata that needed to be included for the sake of QUnit, the module and the test names, as well as the arguments to my function which needed testing. The result of this process was the first worksheet in this spreadsheet.

After creating that spreadsheet I wrote an absolutely absurd formula to handle creating the code conditionally, threw it on a separate worksheet, and called it done. However, I’d included commas directly into the generated code. When exporting it as a CSV in that scenario Google would wrap each cell quotes if it contained a comma, breaking the rendered JavaScript. So, I had to revisit that approach and update it to handle the commas by including the content in different cells. The end result became a formula that spanned 5 columns, each column representing a different argument to a function that would automatically have a comma inserted because of the CSV format. You can see this on the the second worksheet in this spreadsheet.

The last bit of this trick is to grab the CSV (JSONP) and insert it into my test page. I took the example QUnit test code directly from the jQuery docs and removed the entire contents of the $(document).ready() function and replaced it with:


$.getScript('http://spreadsheets.google.com/ccc?key=r6fr5cHDg996-3tXrY87TOw&output=csv&gid=1');

And just like that any time that spreadsheet is updated my test suite is automatically updated as well. I don’t have to write all of the extra wrapper code, and it “just works.” You can count on seeing a lot more of this approach to testing from me.


Wolfram Kriesing, the original author, asked me to post the formula I used on the second worksheet. Right now it doesn’t do everything I want and spans five separate columns… I’ll figure out a way to make this more general purpose in the future. (This code is actually not the completed formula, it shows the elements I used to replace it and the logic in separate places so that it is legible.)


GENERAL
ismodule = and(not(isblank(Tests!A2)), isblank(Tests!B2), isblank(Tests!C2))
istest = and(isblank(Tests!A2), not(isblank(Tests!B2)), isblank(Tests!C2))
isfunction = and(isblank(Tests!A2), isblank(Tests!B2), not(isblank(Tests!C2)))

COLUMN 1
writemodule = CONCATENATE("module('",Tests!A2,"');")
writetest = CONCATENATE("test('",Tests!B2,"'")
writefunction = CONCATENATE("same(jssm.",Tests!C2,"('",Tests!D2,"'")

=if(ismodule,
writemodule,
if(istest,
writetest,
if(isfunction,
writefunction,
CONCATENATE("")
)
)
)

COLUMN 2
writetest = CONCATENATE("function() {")
writefunction = CONCATENATE("'",Tests!E2,"'")

=if(istest,
writetest,
if(isfunction,
writefunction,
CONCATENATE("")
)
)

COLUMN 3
writefunction = CONCATENATE("'",Tests!F2,"')")

=if(isfunction,
writefunction,
CONCATENATE("")
)

COLUMN 4
writefunction = CONCATENATE(if(Tests!G2="false","","'"),Tests!G2,if(Tests!G2="false","","'"))

=if(isfunction,
writefunction,
CONCATENATE("")
)

COLUMN 5
writefunction = CONCATENATE("'",Tests!D2," => ",Tests!E2,"');", if(isblank(Tests!C3),"});", ""))

=if(isfunction,
writefunction,
CONCATENATE("")
)