Calling Amazon Associates/Ecommerce Web Services from a Google Spreadsheet

[UPDATE: Note that since Amazon Product Advertising API, or whatever it’s called now – the thing that was the Amazon E-commerce API – started requiring signed calls (August 2009), this trick has stopped working…]

I’ve never really been one for using spreadsheets – I’d rather write code in a text environment than macros and formulae in a Microsoft environment (because Excel is the spreadsheet you’re likely to have to hand in most cases, right?), but over the last week or so, I’ve really been switched on to how we might be able to use them as a scribble pad for playing with web services…

So for example, in Viewing Campaign Finance Data In a Google Spreadsheet via the New York Times Campaign Data API I showed how to do what it says on the tin…

… and today I had a look at Amazon Associates Web Service (formerly known Amazon ECS (eCommerce webservices)).

Until now, the best way of getting your head round what these services can do has been to use the tools on AWSzone, a playground (or scratchpad) for previewing calls to Amazon web services.

In the case of the REST flavoured web service, the form simply provides a quick’n’easy way of creating the RESTful URL that calls the webservice.

The SubscriptionId is required and can be obtained by registering for access with the Amazon Associates web service.

So just pick the web service/function you want to call (ItemSearch in this case), fill in some necessary details (and some optional ones, if you like…) and view the results:

(You might also notice the scratchpad contains a tab for creating a minimal SOAP request to the web service (and viewing the associated SOAP response) and a tab for creating Java (or C#) code that will call the service). Amusingly, you view the SOAP request and response via a URL ;-)

Whilst the scratchpad makes it easy to construct web service calling URLs, the XML response is still likely to be unmanageable at best (and meaningless at worst) for most people. Which is where using a Google spreadsheet as a display surface comes in.

How so? Like this: take a URL for a (working) webservice call constructed in the AWZ Zone REST scratchpad and paste it into cell B3 in a new Google spreadsheet (enter “URL” as a label in cell A3).

In cell D3 enter the following formula:
=importXML(B3,”//Item”)

This calls the Amazon Associates web service via the RESTful URL in cell B3, and then attempts to display the XML for each results “Item”.

Compare this with the actual XML results file:

The spreadsheet has loaded in the ASIN (the ISBN for each book result) and the DetailPageURL, but the ItemAttributes are not loaded in (or if they are, they aren’t displayed because a single cell can’t display more than a single XML attribute, and it would have to display the Author(s), Manufacturer, ProductGroup and so on).

(Hmm, I wonder what a Treemap spreadsheet would look like? How would it handle the display of XML subtrees?!)

Tweak the formula in D3 so that it says:
=importXML(B3,B4)
in cell A4 enter Path and in B4 enter //Item.

Hopefully results table will remain the same, only now you can experiment with the path setting easily.

Inspect the ItemAttributes by setting the path (cell B4) to //ItemAttributes

A single result can be obtained by specifying which result to display. For example, set the path to //Item[1]/ItemAttributes to display the ItemAttributes for just the first ([1]) results Item.

By importing several XML results files, you could list just the results for the first, second and third results, for example. By loading formulas with different paths into different cells, you can force different results attributes into particular cells.

For example, set the path to //Item[2]/ItemAttributes to display the ItemAttributes for just the second ([2]) results Item.

It’s also possible to craft changes that will apply to the web service URL. In cell A2 enter the label Search and in cell B2 a single word search term, such as google.

Cut the URL from cell B3 and replace it with the formula =CONCATENATE(“”), then paste the URL back in between the double quotes of the CONCATENATE formula.

Now go into cell B3, and find the part of the URL that encodes the search keywords:

In the example above, I was searching for the keyword mashup – replace the keyword with “,B2,”.

What this will do is add the search term in cell B2 into the URL that calls the Amazon web service. So now you can use cell B2 as a search box for a search on the Amazon Associates web service.

Note that you can only use single word search terms at the moment – if you want to use multiple search words, you must use + instead of a space between each word.

So – that’s how to build a search (of sorts) using Amazon Associates web services in a Google spreadsheet. :-)

PS Now I know that that for webservices to count in an academic environment, you’ve got to use SOAP (this counts for teaching computing just as much as it counts in JISC funded projects!), so I don’t expect any of this to count in that environment. But for “mortals”, this way of accessing webservices and then doing something useful with the results may actually be a way forward? ;-)