The Spreadsheet as Mashup Fabric

Joe Gregorio

I recently pointed to a cool thing Jon Udell did with my sparklines generator. In that article Jon asks:

How can more people be empowered to do such redesigns, for print and for the web?

My answer is based on my opinion that any solution needs to be Document Centric. I believe an online spreadsheet is an excellent starting point for building a mashup fabric: a document into which a range of online services can be combined. To that end I downloaded and installed wikiCalc and tried to integrate my sparklines service into a spreadsheet.

Now Dan Bricklin has done a huge amount of work, and the wikiCalc wkcHTTP() function is almost exactly what I needed to start. Almost, but not quite. The default behavior of wkcHTTP() when given: 

=wkcHTTP("http://bitworking.org/projects/sparklines/wikispark.cgi", 10, "error", "GET", A1:A9)

generates a URI that looks like:

http://bitworking.org/projects/sparklines/wikispark.cgi?V1=N10,V2=N20,V3=N23,V4=N40,v5=N70,V6=N80,V7=12,V8=N87

Note what wkcHTTP() is doing to the args, the range A1:A9 is broken out so that the value of each cell is a separate query parameter. What I would need to make this work for the sparkline generator is that A1:A9 gets lumped into one query parameter, and I get to choose that query parameter's name. In other words I'd like to be able to call: 

=wkcHTTP("http://bitworking.org/projects/sparklines/wikispark.cgi", 10, "error", "GET", "d", A1:A9)

and have it generate a URI of the form: 

http://bitworking.org/projects/sparklines/wikispark.cgi?d=1,10,20,23,40,70,80,12,87

Where I come from code speaks louder than words, so here is the patch to wikiCalc 0.95: 

--- orig_WKCSheetFunctions.pm   2006-11-14 22:40:18.000000000 -0500
+++ WKCSheetFunctions.pm        2006-11-20 20:55:54.000000000 -0500
@@ -2648,14 +2648,16 @@
    my %chartotos = (N => "n", T => "t", H => "th", E => "ewkcHTTP $WKCStrings{sheetfuncwkchttperr}");
    my $pcount = 0;
    my $separator;
+   my $queryparameter = scalar @$foperand ? lc operand_as_text($sheetdata, $foperand, $errortext, \$tostype) : "d";
+   $paramstr = $queryparameter . "=";
    while (scalar @$foperand) { # output args
       $value1 = operand_value_and_type($sheetdata, $foperand, $errortext, \$tostype);
       $tostype = $tostochar{substr($tostype,0,1)} || "T";
       $pcount++;
-      $paramstr .= $separator . "V$pcount=" . $tostype . url_encode_plain($value1);
-      $separator = "&";
+      $paramstr .= $separator . url_encode_plain($value1);
+      $separator = ",";
       }
-   if ($requestmethod eq "GET") {
+   if ($requestmethod eq "GET" && $paramstr) {
       $requesturl .= "?$paramstr";
       $paramstr = "";
       }

And here is the output of a sample spreadsheet successfully including a sparkline with the values driven off the sheet itself: http://bitworking.org/projects/wikicalc/test1.html.

Observations

Quality
This is just a kludge to show my ideas, a real patch would handle multiple parameters, error checking, etc. My Perl skills are currently way too rusty to do it justice.
Typing
In order for this to really work I had to write a special version of the sparkline web service, wikispark.cgi, that created a snippet of HTML to wrap the sparkline image, since wikiCalc can't natively import an image. That's not too bad, but I also had to prepended that HMTL snippet with a single H character as it appears that wikiCalc requires the result of the HTTP response body to begin with a single character which it uses to determine its type and how to handle it. Those of you who know me can well imagine my apopleptic fit. I am willing to over look that for now, but if wikiCalc is to become the mashup fabric that I believe it can be, then it should become a little more aware of it's 'context' and start using the content-type of the response to determine the right representation.
comments powered by Disqus