The Spreadsheet Parts Repository for Excel and Google Spreadsheets

I am declaring my Spreadsheet Parts Repository open. It's a free service for Excel and Google Spreadsheets users, which will provide tested and documented formula groups that you can download into your own spreadsheets. And hence avoid this:

by replacing it with this. While setting it up, I seem to have discovered a new kind of advertising.

I explain how the Repository works in a paper Spreadsheet Components For All. My aim — as regular readers will know — is to bring modularity and prefabrication to spreadsheets, by making it possible to build them from components.

Because spreadsheets are visual programming languages, my components correspond to something visible: cell groups. (Incidentally, this makes it possible to see how components work, by giving them rows or columns that display intermediate results.)

But because more than one spreadsheet may want the same component, components must adapt to different spreadsheets: must reshape and resize themselves to fit different locations and layouts.

This, I do by storing components not as fixed pieces of spreadsheet, but as spreadsheet-valued functions whose parameters you supply to a Web form. These parameters specify a component's location and layout in your spreadsheet. This makes inserting a component like inserting a chart into Excel, except that you do it over the Web by filling in a form. The Repository's server will then send you a link to a component instance customised for your spreadsheet. You can download that, and copy it into your spreadsheet with a program available from the Repository.

That's for Excel. But the Repository also works with Google Spreadsheets. If you type your Google Docs account details into the Web form, and the name of a Google Spreadsheet, the server will copy the component instance into that, as well as sending a version for Excel. So this is like inserting a Google Spreadsheet Gadget into your Google Spreadsheet, except that instead of a graph or chart, you're inserting groups of cells holding useful formulae.

I've put up demonstrations on the Repository's Demonstrations and background page. One of these, the section Try for yourself in my presentation Less Excel, More Components, links to downloads which work with Excel. They include various sizes and shape of component instances for you to copy into a sample spreadsheet, also supplied, plus the program that does the copying.

Another demo, Spreadsheet Components, Google Spreadsheets, and Code Reuse, is a Dobbs blog posting with screenshots showing the Repository working with Google Spreadsheets. (The screenshots in these writeups show a prototype of the Repository; the pages now look rather different.)

The spreadsheet-valued functions I mentioned are specified in a language I call Excelsior. Excelsior modules describe spreadsheets in terms of named constants, tables, the types permitted for the table elements, and equations between these elements. Excelsior allows these descriptions to be parameterised with the tables' layouts and locations. This is equivalent to saying how each table is to map onto a worksheet, and is how the Repository's server generates component instances. I've tried to design the Excelsior language so it's easy to read, and also so that it is easy to comment and convert into nice-looking HTML documentation pages, as in this example. Spreadsheets are notoriously poorly documented, but I hope this will help.

Taking this further, I have experimented with a style of documentation called Literate Programming. You can see this at work in the Excelsior code for a spreadsheet I wrote that writes science-fiction plots. It's a complete spreadsheet, rather than a component, but I show it because I think documentation is important, and it is a good example of Literate Programming applied to spreadsheets. The first!

I said I'd discovered a new kind of advertising. Because I want the Repository to be free, I need some way to pay for admin and programming, so I'm looking for sponsors. I'll put sponsors' logos on the home page. But also, I invite sponsors to send me advertising text similar to what Google Adwords accepts. I realised that I could store these texts with the components. Then, when the Repository generates a component instance to be copied into a user's spreadsheet, it can arrange for these texts to be copied into a Sponsors worksheet there. Advertising in downloadable spreadsheet pieces.