News

Google Spreadsheet Adds Features, Including Data Import

First Google’s new book search features and now some upgrades to Google Spreadsheet. Like I’m ever going to get seven hours of sleep again in my life.

I have actually been using Google Spreadsheets a fair amount at work. It’s not as full-featured as Excel, but I don’t often create spreadsheets that need so much horsepower. And there’s something handy about knowing your spreadsheet is as close as any Web-enabled computer.

I was very happy to see that Google Spreadsheets now has AutoFill, which is for me the official “feature that most quickly becomes annoying when it isn’t available.” Especially when you’re filling in date ranges. Google has made the best use of its Web connection by hooking in AutoFill to Google Sets. Remember Google Sets? Refresh your memory at http://labs.google.com/sets . With Google Sets you can enter a few items that are similar (colors, candy bars, cities) and Google Sets will try to fill out your list with more similar items.

Enter a few similar items in your Google Spreadsheet, and select that range of cells. Hold down CTRL (if you’re using Linux or Windows) or ALT (if you’re using Mac) while you’re clicking and dragging on the small box in the lower right corner of the cells. Google will try to fill the range of cells you’ve covered with similar items. I found it worked really well for colors, though Google couldn’t cover the entire range I selected. It went a little bonkers when I started entering book genres, and introduced a couple of non-English words. But other than that the Google Sets offering worked pretty well.

Google Spreadsheets has also introduced new data import features which allow you to import RSS feeds, HTML, comma- and tab-delimited files, and text-delimited files. You can get all the skinny on those import functions at the Google Docs documentation. The import function that really caught my fancy was the feed import function.

The syntax is =ImportFeed(URL, [feedQuery | itemQuery], [headers], [numItems]) . This basic query will fill my spreadsheet with a list of recently updated or added items at Project Gutenberg: =ImportFeed(“http://www.gutenberg.org/feeds/today.rss”) . Perhaps I only want the last five items from the feed, and I want to include headers instead of only the data. The syntax would look like this: =ImportFeed(“http://www.gutenberg.org/feeds/today.rss”,,true,5). (The two commas together are because I don’t have a feed or item query in use here.)

Let’s do a project. Say I want I want to keep a tally of new DVD releases, and use it in Google Spreadsheets so I can share it with other people and we can make notes (“Seen it,” “I want to get that one”, “Huh?”) I go poking around for an RSS feed of new DVD releases and find one at Rotten Tomatoes.

I enter this RSS Feed into Google Spreadsheets ( =ImportFeed(“http://i.rottentomatoes.com/syndication/rss/new_releases.xml”, , true, 100) ) and presto, I have a listing of the latest DVD releases. But I only have as many as are in the RSS feed right now. Ideally I’d like to be able to save an accumulate items as they are listed in the feed. Feedcatch might work for that — but I’m not sure if Feedcatch is still functional. Anyone know?

Once I have the data, I can publish it in several ways, including creating an embeddable IFrame for a blog. Further, I can specify a range of information to embed, not the whole feed.


This embedded frame will automatically update as the sheet itself updates.

As I think about it, I think that the ability to import HTML files might be pretty fun too. Say I was Tim Carter of AsktheBuilder.com . I could import a Google search result that gave me the pages indexed in the last 24 hours that contained the string AsktheBuilder and pulled out all links on that page:

=importXml(“www.google.com/search?as_qdr=d&q=askthebuilder&num=100”, “//a/@href”)

There’s crud in these results — it’s ALL the links on the page, after all — but this might be a useful way to monitor for pillaged content, nice comments, etc. THEN you add a column next to the first column that pulls titles from the content you’ve found, giving you a quick way to glance over search results. (Like so, where the column is A35: =importXml(A35, “//title”)) Unfortunately you’re limited to 50 functions per spreadsheet (FIDDLESTICKS!) so there’s a limit to what you can import.

I could and will spend a LOT of time playing with this. If the spreadsheet offered filtering functions, many more amazing things could be done. Who needs sleep anyway?

Categories: News