Google Spreadsheets: Securely Sharing A Single Sheet

I'm a huge fan of Google Spreadsheets, especially their publishing / sharing features. One feature I found that was missing was the ability to securely share just one sheet. In the post below, I describe how I worked around that.

Suppose you have a two tabbed spreadsheet like so:

Suppose further, that you'd like to share the sheet labeled "A" with Alice and the sheet labeled "B" with Bob. Google Spreadsheets offers a neat publishing feature that allows you to share just one sheet. So you could send Alice and Bob the following URLs:

  For Alice:
  http://spreadsheets.google.com/pub?key=pPkcBhkzcZSZC1kj2hgZcQQ&
      output=html&gid=0&single=true

  For Bob:
  http://spreadsheets.google.com/pub?key=pPkcBhkzcZSZC1kj2hgZcQQ&
      output=html&gid=1&single=true

The problem is, the only difference in the URLs is the gid parameter. A curious Alice could change that value to 1 and see Bob's numbers. Not good.

To work around this, I through together a quick hack so that I could send Alice and Bob totally different URLs, and yet still take advantage of Google's publishing feature. The solution makes use of Apache Rewrite Rules. Only the coolest feature to ever be added to an application (followed by elisp and emacs, but I digress).

Here's how the solution works. First, setup a mapping file from gid numbers (that is, the index number in the sheet your sending) to a secret code. Put this in a text file we'll call sample.map. Here's what it might look like:

  A9941009287   0  # For Alice
  B1837730400   1  # For Bob

Note, the first column (beginning with A... and B...) is totally arbitrary. I just picked scary looking numbers to add to the mystique of the link. I could have just as easily put foralice (though, that's a key that would be pretty easy to guess).

Next, setup the following rewrite rules in a your httpd.conf or .htaccess file:

 RewriteMap sample-id txt:/usr/local/i2x/spreadsheets/sample.map

 RewriteCond %{REQUEST_URI} ^/sample/(.*)
 RewriteCond ${sample-id:%1} ^([0-9]+)$
 RewriteRule .* http://spreadsheets.google.com/pub?key=pPkcBhkzcZSZC1kj2hgZcQQ&output=html&gid=%1&single=true&widget=true [P,L]

 RewriteRule ^.*$ notfound.file

This rewrite rule starts off by setting up a map that can be used to resolve the secret ID we'll be handing out our users. Next, comes the magic - we:

1. Grab the secret ID from the URL by looking for a value after: /sample/???
2. We look that value up in the map we previously set up and make sure we found something
3. We use the value we found as the value for gid
4. We then do a proxy request to spreadsheets.google.com, using the URL that we would normally have sent our users

The final rule is a catch all that redirects any unhandled requests to a page that doesn't exist. This will trigger an error if the user attempts to access a page we haven't set up.

The result is that now we can send the following links to Alice and Bob:

 http://spreadsheets.ideas2executables.com/sample/A9941009287

 http://spreadsheets.ideas2executables.com/sample/B1837730400

They'll see the data we want to show them, but no clue to the actual spreadsheets.google.com URL, which would let them access each other's data.