Google Apps Hacks

55  - ADD LIVE DATA TO YOUR SPREADSHEET





At the time of this writing, Google allows a maximum of 250 GoogleLookup functions per spreadsheet.

Using the “Publish” tab, you can even turn this data into a web page, or embed the table in your

homepage (Publish→More Publishing Options) .



You can also take results returned from the GoogleLookup function and use them inside other

formulas in your spreadsheet. Sometimes it’s necessary to clean up the values returned by

GoogleLookup, though. In the table shown in Figure 3-4, the following formula—with a hat tip to

the helping soul of the Google Docs groups, André “Ahab” Banen—strips unwanted text out of

population values to get an actual number:

=IFERROR( VALUE(LEFT(B2, FIND(" ", B2))), IFERROR(VALUE(B2)) )

In this formula, the FIND function looks for the rst occurrence of a space character within the text

contained in the A2 cell. That’s because the space character signals the end of the numeric value (in

Figure 3-4, B2 contains “107,449,525 (July 2006 est.)”).

If the space is found, the LEFT function trims the B2 text down to all the characters up until the

space, and VALUE converts the result into a number. If there is no blank available in the number cell,

an error is thrown, which is caught ...