Monday, March 24, 2008

Convert Anything on a spreadsheet

Ok, maybe not 'anything' - but pretty close... It uses a lesser known feature of the Google search box. For example, If you want to know how many feet there are in 12 meters, you type "12 meters in feet" and you get your answer. Or let's say your british friend tells you he has a friend who wants to get to know you better - and you say "describe him" and he says, "right... he's got a good personality, and...well... he weighs 14 stone". Of course, instead of seeming like a naive American (of course not, not you), you quickly go to the google search box and type "14 stone in pounds" and then decide... and maybe then you can use this same method to figure out how many USD (yes, that's dollars) you'll need to buy dinner in GBP (of course, that's pound sterling) - it's not a pretty answer.

Well - now that you know that trick - here's the real trick. In a spreadsheet (yes, on Google, of course) you can send that query to the Google search page in the background, get back the answer, parse it and display the answer in your spreadsheet. All this through the magic of the "ImportHTML()" formula.

I'm not going to go into the details of how to do that here - but I will give you a link to get your own copy of this sample spreadsheet (yes, you'll be asked to login to your google account, if you aren't already, to get this spreadsheet added to your doc list).

I must admit, I've had this fun thing (wha? you don't think this is fun?) lying around for a bit - but I figured I should clear the decks given that I've got some new ideas to post...

6 comments:

Tom said...

God bless that ImportHTML() function. And God bless that wonderful intern who programmed it. Whoever he may be.

JR said...

Yes... god bless him indeed... Maybe one day he'll create a few samples of his own or write a few spreadsheet gadgets or something to remind us of his prowess ;)
Thanks for reminding me, TOM...ahem... I mean, tom...

WFD Reunion 2008 said...

JR thanks for pointing out that the wfd blog I created had too much info that was public. I think I made it so people would not just stumble on it. How did you stumble on it? This is my first attempt . Yvonne

Unknown said...

Hi, JR. Nice work with the Google Spreadsheet gadgets. There's a gadget I wish existed -- are you interested in doing more of them?

Rusharound[at]Gmail[dot calm]

Anonymous said...

I am trying to use your translation gadget to do something it wasn't intended to-- use the Google Dictionary, not the google translator, per se. It seems that this will either not work with your gadget, or require some modification of it.

I am guessing that there's some way to have Google Spreadsheets lookup Column A words and dump the definition from the Dictionary into Column B, but I'm stumped. My e-mail is faddat@gmail.com if JR or anyone else has any clue on how to make this work.

Basically Column A would contain an English word, and Column B would contain the Korean result from the Google dictionary, not the simple translation.

Thanks for any help :)!

-Jake

JR said...

Fixed ! Please give it a try if you still find it valuable... throw out the old one if you made copies - they were based on the XML/HTML returned in a prior version of the Search results page.