5 Google Spreadsheet Surprises

OK, I'm on a spreadsheet kick. Along with trying my hand at getting fancy with Excel, I've been playing more with Google Spreadsheets. After poking around their docs I have to say I'm really impressed with the job Google did. Here are 5 surprises I had while playing around with Google Spreadsheets.

1. Real formulas are available. I figured sum() and count() functions would work, but so do a wide variety of others. Vlookup, countif(), and many others are also available.

2. You can lookup live data from the web using Google functions. What to know the longitude of Arlington, VA? Enter the formula =GoogleLookup("Arlington, VA", "Longitude"). How about the stock price of Red Hat Corp? Enter =GoogleFinance("RHT","price"). You can also use functions to pull in external data. Your options include ImportXML(...) to slurp in XML, importData(...) to import .csv or .txt files and importFeed(...) to pull in an RSS feed. They also offer importHTML(...) which I find particularly clever:

=ImportHtml(URL, "list" | "table", index). This imports the data in a particular table or list from an HTML page. The arguments to the function are as follows:

• URL - the url of the HTML page
• either "list" or "table" to indicate what type of structure to pull in from the webpage. If it's "list," the function looks for the contents of <UL>, <OL>, or <DL> tags; if it's "table," it just looks for <TABLE> tags.
• index - the 1-based index of the table or the list on the source web page. The indices are maintained separately so there might be both a list #1 and a table #1.

Example: =ImportHtml("http://en.wikipedia.org/wiki/Demographics_of_India", "table",4). This function returns demographic information for the population of India.

3. You can use an API to programmatically review and update your spreadsheets. From the Google docs on the Spreadsheet API:

The Google Spreadsheets Data API allows client applications to view and update Spreadsheets content in the form of Google Data API ("GData") feeds. Your client application can request a list of a user's spreadsheets, edit or delete content in an existing Spreadsheets worksheet, and query the content in an existing Spreadsheets worksheet.

Here are some of the things you can do with the Spreadsheets Data API:

• Use a spreadsheet as a database and access data for another application via the Spreadsheets Data API.
• Use an external graphing library with the Spreadsheets Data API to add nice looking graphs to your site.
• Implement auto-filtering and other spreadsheet functionality in an application you build for your users.

It's not immediately obvious to me why my spreadsheet would need API access - but still - how cool is that?

4. You can collaborate in real-time on a spreadsheet. Google Spreadsheets not only allows you to publish documents for others to view and edit, but it also gracefully allows for simultaneously editing. Check out the conversation I had with myself:

Notice, not only is there a chat window to discuss the spreadsheet, but there's also a cell that's highlighted in green. That's where the other user's cursor is currently. This way, everyone can literally and figuratively be on the same page.

Combine this chat ability with versioning, and your spreadsheet because a useful on the fly collaboration tool.

5. You can access your Google Spreadsheets with your mobile device. By visiting http://docs.google.com/m on your cell phone, you'll gain access to all your documents, including spreadsheets. Here's what I see on both the desktop and mobile version of the same sheet:

Learn more here and here. Live data on the go, how cool is that?

I'm starting to get convinced that Google Spreadsheet isn't just a lame replacement for Excel - it's a powerful tool in its own right. With a bit of imagination, you can do some remarkable things.