Wednesday, January 02, 2008

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.

4 comments:

  1. This is an awesome mod_rewrite hack, too bad that google's javascript exposes the secure url. You can see a request to http://spreadsheets.google.com/pub?key=pPkcBhkzcZSZC1kj2hgZcQQ&chrome=false&gid=0
    using firebug. Note the gid=0 for which 1 can be substituted to see the other page.

    I was actually expecting the google analytics code to expose the secure url but it reported your modified url.

    ReplyDelete
  2. Hey Jay -

    Thanks for the feedback and finding the potential glitch.

    When I visit: http://spreadsheets.ideas2executables.com/sample/A9941009287 and use FireBug, I don't see any JavaScript requests. When I examine the source of the page, I don't see any JavaScript includes other than urchin and a widgets library.

    What am I missing?

    ReplyDelete
  3. So when I send a student a link to the sheet using the url they can still see all of the other sheets of all the other students. Any ideas how to fix that? When the website opens up the link all the tabs to the other sheets are still there. I am looking for a way to either password protect those sheets from being viewed or to not even appear at all when the student clicks on the link.

    ReplyDelete
  4. Anonymous9:05 PM

    Depending on who your client (or clients) is, you might not need to worry too much about hiding the id completely. A number of people wouldn't even bother.

    I found that you can add any string to the URL as long as its after an ampersand. So you can go from this
    http://spreadsheets.google.com/pub?key=pPkcBhkzcZSZC1kj2hgZcQQ&
    output=html&gid=1&single=true

    to this
    http://spreadsheets.google.com/pub?key=pPkcBhkzcZSZC1kj2hgZcQQ&
    output=html&AWIUROJDGLSJDLFSDJL&gid=1&single=true

    And you can extend that string with whatever you want. You can even include your own parameters. So you could have something like spreadsheetid=2. As long as it doesn't conflict with an existing parameter, you should be fine.

    So what you could do is just send someone an extremely long link with the gid parameter hidden somewhere inside the string (and preferably after the URL bar ends). I doubt many people would bother checking. And if they saw fake id paramters, they'd try those and then give up after a while.

    ReplyDelete