Channels ▼

Jocelyn Paine

Dr. Dobb's Bloggers

Spreadsheet Components, Google Spreadsheets, and Code Reuse

May 27, 2008

I've finished a demo I'm really pleased with, an experiment in reusable spreadsheet components for Google Spreadsheets, and I can't resist blogging it. 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. Eventually, I'd like to start a library of such components; and, if I can get funding, to make it free. I'll demonstrate with a text-searching component, showing how to slot it into different shaped places in different Google Spreadsheets as if it were a chart. You can try this yourself on this Web form, explained 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?

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.