How to add a book title from an ISBN or ASIN in a Google spreadsheet

Having recently created an iMacro to import books from Google spreadsheet to Amazon, I realized that the formula I was re-using to automatically put in the book title was quite a neat feature of Google docs that most people are probably unaware of.

I use the importXML function. This finds data based on an HTML tag in any webpage and you can create that source webpage name as a formula. This gives a really neat way of cribbing data about a book straight from it's Amazon webpage to be used on your own spreadsheet (also known as website scraping).

Here's my example formula (assuming the ISBN or ASIN is at cell A1), read this as one line:

=importXML( concatenate( "http://www.amazon.co.uk/exec/obidos/ASIN/", A1), "//span[@id='btAsinTitle']")

By looking at the webpage source code for any Amazon book you can find HTML tags around all of the product data. In this case the HTML tag <span> with the ID attribute set to "btAsinTitle" surrounds the book title. If you wanted to grab data on the number of pages, publisher or even lowest second hand price, this is all possible in a similar manner.

I have also created neat formulas to grab best match full film title and release date from IMDB.com as a search sheet to support my DVD index so I can quickly cut & paste details based on typing in key words from the film title.

The only limitation is there is a maximum of 50 such formulas in any spreadsheet. However if you are building a catalogue or sales list, it is a fairly simple matter to cut & paste the values over the formulas for books or DVDs already indexed so only new entries use the formula.