Importing prices from Amazon into a Google Docs spreadsheet

I searched the internets for this specific piece of Google Docs spreadsheet code. And here it is. I wondered how I could create a dynamic shopping list kind of spreadsheet for my new computer that I might afford at some point in my life. The dynamics here would be the ability to get the pricing for all the things in the list from Amazon websites every time I open the spreadsheet. Here’s how I got it working:

This is a way to get dynamic pricing info into a Google Docs spreadsheet from Amazon.de in euros… and below that also how to get it from Amazon.com in dollars.

=value(substitute(replace(importXml("http://www.amazon.de/Samsung-LS27B971DS-LED-Monitor-DisplayPort-Reaktionszeit/dp/B00B2HH53A/", "//b[@class='priceLarge']"), 1, 4, ""),",","."))

The function line starts with a =, but as I have just learned you put that only to the beginning of the line, not in front of each function…

If we start from the inside, we have the most important function importXml(“url”, “xpath thing”). It is used to get the actual data from the Amazon website as text. In this case we have the url of a certain pricy and almost well designed, but still lacking monitor.

The xpath thing, in this case is “//b[@class=’priceLarge’]”, where it gets the data from the html/xml file from a line which has tags like this:

<b class="priceLarge">EUR 806,99</b>

So, there’s where the letters “b”, and “class” come from. And “priceLarge” is the name of the class…

(If you’d have some other website with other tags you can add the like this: “//td/b[@class=’priceLarge’]”, which would also include the “td” tag that is before the “b” tag…)

That will get you the price as text, but we still need to convert it to a number. We do that with first using the replace(“some text”, startnum from 1, nro letters, “replacing text”) function, to get rid of the “EUR ” letters.

Then we still have the european standard decimal comma that didn’t work with my version of Google Docs (this might still work for you). We use substitute(“text,withcomma”, “whatyouwanttoreplace”, “replace it with the dot”) function to change the comma to a dot.

Lastly the value(somenumberwhichistext) will change our text into a real number, so that it can be used to checking how much our shopping list will cost.

For Amazon.com

For Amazon.com we don’t need to use the replace and substitute functions. Google Docs even undestands the raw output of importXml(amazon.com price) as a number with a dollar sign in front of it. But if you want to get rid of the dollar sign, you can just use value() and that will get rid of it. Like this:

=value(importXml("http://www.amazon.com/Nokia-Unlocked-Internal-Memory--International-Version/dp/B005Z32UI2/", "//b[@class='priceLarge']"))

That will give you the price of the Nokia N9 64GB black edition. (That’s my phone! But I got it used for much cheaper.)

It said somewhere, that there’s a limit of 50 importXmls in Google Docs. Haven’t tested that, yet.

Hope this helps.

Update:
Here’s a new post about this subject. The post has an example spreadsheet.
https://sadesaapuu.wordpress.com/2013/11/12/an-example-google-docs-spreadsheet-with-dynamic-amazon-pricing/