Visual Controls for Spreadsheets

Some time ago, Paul Walk remarked that “Yahoo Pipes [might] do for web development what the spreadsheet did for non-web development before it (Microsoft Excel has been described as the most widely used Integrated Development Environment)”. After seeing how Google spreadsheets could be used as part of quick online mashup at the recent Mashed Library, Paul revised this observation along the lines of “the online spreadsheet [might] do for web development what the spreadsheet did for non-web development before it”.

In An Ad Hoc Youtube Playlist Player Gadget, Via Google Spreadsheets, I showed how a Google gadget can be used as a container for arbitrary Javascript code that can be used to process the contents of one or more Google spreadsheet cells, which, combined with the ability to pull in XML content from a remote location into a spreadsheet in real time, suggests that there is a lot more life in the spreadsheet than one might previously have thought.

So in a spirit of “what if” I wonder whether there is an opportunity for spreadsheets to take the next step towards being a development platform for the web in the following ways:

• by offering support for a visual controls API, (cf. the Google Visualization API) which would provide a set of visual controls – sliders, calendar widgets and so on – that could directly change the state of a spreadsheet cell. I don’t know if the Google spreadsheet gadgets have helper functions that already support the ability to write, or change, cell values, but the Google Gdata spreadsheet does support updates (e.g. updating cells and Updating rows). Just like the visualization API lets you visually chart the contents of a set of cells, a visual controls API could provide visual interfaces for writing and updating cell values. So if anyone from the Lazyweb is listening, any chance of a trivial demo showing how to use something like a YUI slider widget within a Google spreadsheet gadget to update a spreadsheet cell? Or maybe a video type, that would take the URL of a a media file, or the splash page URl for a video on something like Youtube, and automatically create a player/popup player for the video if you select it? Or similarly, an audio player for an MP3 file? Or a slideshow widget for a set of image file cells?
• “Rich typed” cells; for example, Pamela Fox showed how to use a map gadget in Google spreadsheets to geocode some spreadsheet location cells (Geocoding with Google Spreadsheets (and Gadgets)), so how would it be if we could define a location type cell which actually had a couple of other cells associated with in “another dimension” that were automatically populated with latitude and longitude values, based on a geocoding of the location entered in to a “location type” cell?
• “real cell relative” addressing; I don’t really know much about spreadsheets, so I don’t know whether such a facility already exists, but it is possible to “really relatively reference” one cell from another; for example, could I create a formula along the lines of ={-1,-1}*{-1,0} that would take a cell “left one and up one” ({-1, -1}) and multiply it by the contents of the cell “left one” {-1, 0})? So e.g. if i paste the formula into C3, it performs the calculation B2*B3?
• Rich typed cells could go further, and automatically pop-up an appropriate visual control if the cell as typed that way? (e.g. as a “slider controlled value”, for example; and a date type cell might launch a calendar control when you try to edit it, for example?

PS for my thoughts on reinventing email, see Sending Wikimail Messages in Gmail ;-)