[ Jocelyn Ireson-Paine's Home Page | Publications | Dobbs Code Talk Index | Dobbs Blog Version ]

Spreadsheet Components, Google Spreadsheets, and Code Reuse

I've just finished a demo I'm really pleased with, an experiment in reusable spreadsheet components for Google Spreadsheets, and I can't resist blogging it here. Spreadsheets, to borrow a metaphor from Douglas Adams, do code reuse in the same way that bricks hang in the sky. I want to give bricks wings, by providing spreadsheet components you can insert to calculate things you'd find hard to program yourself. Eventually, I'd like to start a library of such components; and, if I can get funding, to make it free. I'm going to demonstrate with a text-searching component I've written, showing how to slot it into different shaped places in different Google Spreadsheets as if it were a chart. You can try this yourself by using the Web form I explain below.

Getting straight to the point, three screenshots of the demo are shown below. These are scaled-down images: clicking one will bring up its full size counterpart in a new browser window.
The first image is one of my Google Spreadsheets, with sample data in column A. The middle image is the above-mentioned form on my Web server, after I filled it in and submitted it. This tells the server to insert the component into my spreadsheet. And the third image is the updated spreadsheet. Even without enlarging the images, you can see new values in columns B and C. These are calculated by formulae that the server has inserted.

In the form, the bottom row identifies the spreadsheet to Google (I've scrubbed out my Google Docs email address and password). The second row tells the server which cells to search for the pattern specified in the first row: in this case, it searches cells A2:A10 for "x". The third row tells it where to copy matching strings to: in this case, B2:B10. And the fourth row is a working range for the offsets of matching cells: in this case, C2:C10.

So the form acts like Excel's Chart Wizard or the chart-insertion form Google describe in their Charting: Creating a chart page. Chart forms or wizards specify where a chart gets its data from; my form specifies where a component gets its data from and sends its results to.

Because I need a snappy name, let me call my components "spreadlets", by analogy with applets and servlets. A crucial point is that spreadlets are not restricted to one shape or location of cell range. In the demo screenshots below, I've inserted the spreadlet into a new spreadsheet, this time telling it to arrange the output and working ranges horizontally, with outputs in C2:K2 and the working range in C4:K4.

Likewise, we can have different-sized cell ranges. In the screenshots below, I took inputs only from cells A4:A8, ignoring A2:A3 and A9:A10 used in my first three. I asked for outputs and the working range to go vertically again, in B4:B8 and C4:C8 respectively.

Such searches, where gaps between matching cells are closed up, are hard to program in Excel. This illustrates why I believe spreadlets will be useful: to do calculations that many spreadsheet developers lack time or knowledge to do for themselves. The text-searching component arose from such a need, in an Excel spreadsheet that modelled the finances of house-building projects. Users could enter lists of housing categories, and there were several places where we had to find all categories beginning with strings such as "Bungalow" or "Flat", for use as options in dropdown menus. I say more about this in Spreadsheet Components For All, a paper submitted to the July 2008 conference of the European Spreadsheet Risks Interest Group.

Incidentally, this original version of the spreadlet used wildcarded patterns: for example, "Bungalow*" to find all strings starting with "Bungalow". You can see this in an Excel version of the spreadlet, here. In Google Spreadsheets, wildcards don't seem to work, which is why the outputs are always equal to the pattern.

How are spreadlets implemented? My Web server machine holds a file containing the spreadlet source code. This is written in Excelsior, the same language used for the science-fiction generator spreadsheet I recently blogged. Excelsior describes spreadsheet components using named tables, abstracting away from cell names and layout on worksheets. The spreadlet has three such tables, as shown in this source listing and in Appendix 1 to Spreadsheet Components For All. These are elements_to_search, matching_elements, and the_index. The first two are inputs and outputs; the third is the working range.

When you submit the spreadlet form, a server script extracts the sheet and cell names and rewrites them into Excelsior statements specifying how each table maps to a worksheet. It appends these statements to the spreadlet source code, then invokes Excelsior to "compile" the resulting file. Excelsior performs the coordinate transformations needed to replace table names by cell addresses, which gets it formulae customised to your spreadsheet. Finally, the script passes these to Google's Spreadsheets Data API (GData) library, which stuffs them into your Google Spreadsheet.

From the point of view of programming-language design, spreadlets are "abstractions" of spreadsheets, in the sense that a function like dollar_value(s) = s*1976.6 is an abstraction of the formula 1000*1976.6. The formula calculates only one thing; but the function can calculate many different things in a program, simply by using it with a different s each time. Moreover, we can put it in a library for use by many different programs. That's why it's valuable: because it helps code reuse.

Similarly, spreadlets can be parameterised with different shapes, sizes, and locations of cell range; and can be used in different spreadsheets. Moreover, I hope, we can build up libraries of spreadlets. What might these look like? Well, for the online spreadsheets — Google's, and others such as EditGrid, Numbler, Num Sum, and Zoho Sheet — perhaps it might be a Web interface like that shown in the Google Gadgets posting Google Spreadsheets Adds Gadgets, a Directory of Features. I've some thoughts on that which I'll blog in a future article.

What winged bricks spreadlets would you like for your spreadsheets?