Google Apps Hacks

74 GOOGLE APPS HACKS

As soon as the external web site’s HTML layout changes, your XPath—and with it, your automated spreadsheet

list—might break. It makes sense to check your list every now and then to see whether it’s still stable. Also, try

to nd an XPath that balances precision (so you don’t nd elements you don’t need) with fuzziness (so that

you don’t nest your structure too deeply and precisely, as that makes the XPath more prone to break when the

external HTML output changes).



Here is an overview of Google Spreadsheet’s data import functions, further described at

http://docs.google.com/support/spreadsheets/bin/answer.py?answer=75507:



=importXml(URL, XPath-query)

Access an external XML or HTML le to return structured data via an XPath query.

The following example code retrieves the book titles as shown previously:

=importXml("http://search.barnesandnoble.com/booksearch/results.asp?WRD=extreme+sports",

"//div[@class='bc-desc']/h2/a/text()")



=importData(URL)

Access an external CSV (comma-separated values) or TSV (tab-separated values) le. In fact, this

works with any arbitrary URL, though the data returned may not be too useful; for instance, using

importData to poll an HTML page might return a list of elements, each lling a single cell.

Here is example code, accessing the output of another spreadsheet (restricted t