An example Google Docs spreadsheet with dynamic Amazon pricing

I posted about dynamic Amazon prices in Google Docs spreadsheets before.

Here is a complete example of a shopping list created as a Google Docs spreadsheet, that pulls its prices dynamically from Amazon.de. This is a list for my forthcoming new computer, that I’ll be hopefully putting together in the next couple of months.

Here’s the link. You can make your own copies from File->Make a copy… And with your own copy, you can modify it to be your own perfect shopping list for whatever. And this way you can actually mix dynamic prices from different webshops. I’ve added example scripts for two stores, that I will actually be using to get many of the stuff cheaper than from Amazon.

https://docs.google.com/spreadsheet/ccc?key=0Au_LU7_L9JDFdG84dzZ3YXlXOUd2b3NpQndCZVpvdVE&usp=sharing

It’s not easy or totally automatic to create and modify this kind of spreadsheet, but once you understand how it works (and that each store requires its own script), you’ll be able to make budgets that are dynamic. For me this was a must have, as I’ll be making my purchase from several different webstores, and needed to find a way to compare their prices on a daily basis. Atleast Amazon prices tend to change all the time, and the price that is updated dynamically is not actually the cheapest Amazon price. There might be cheaper Amazon offers in the small print, but I haven’t yet looked for a way to fetch those prices dynamically.

And yes. My Amazon.de links in the spreadsheet contain sneaky affiliate links, that might give me some money, but I doubt that will actually happen. Feel free to replace the links with any Amazon.de product page links.

Advertisements

15 comments

  1. Hello, did you try with the new google docs spreadsheet ? No more limit using the XML import but I need help as it is no more working for amazon.com 😦 Any help ? Thanks for this amazin trick !

    • Thanks for letting me know, that they are making a new version. I just tried it and importXml seems to be broken in the new version. I tried to get it working on very simple sites, but none of them worked. Apparently some people have gotten it to work on some small Xml files.
      There’s a discussion about it not working anymore in [url]https://productforums.google.com/forum/#!topic/docs/yWPaNDK0Kpg[/url]

      I believe it is possible to return to using the old google docs spreadsheet by switching the new speadsheets of from the Google Docs settings. And also your old spreadsheets are still supposed to be using the old version, so they should work ok.

  2. Thank for you reply ! Yes it’s still working on small websites with the new google spreadsheet but no more on website like amazon it’s strange… I will continue with the old version untill is working and I hope they will update this feature. Thank for you time 🙂

    • I tried and it seems like you’ll just have to get rid of the first letter which is the pound sign:

      =value(substitute(replace(importXml(F44, "//b[@class='priceLarge']"), 1, 1, ""),",","."))

      The 1,1, “” there will replace the first pound sign with empty (“”). On the euro-version, we used 1,4, “”, which replaced the first four letters with empty. (Oh, and I just realized the end is not needed, where we replace the comma with the dot, but it’s just so messy there, that I don’t want to mess with it anymore, as it works this way too.)

      And there in F44 you’d place an Amazon.co.uk URL like http://www.amazon.co.uk/Jolla-Sim-Free-Smartphone-16GB-White/dp/B00HJA7A3Y/

      (And the F44 is just a cell in your spreadsheet. Ask again if you don’t get it working.)

  3. Thanks for the reply

    Do you mean just paste that formula into the cell?

    I did that (with I1 in place of F44 because my URL is stored in I1 for now), and the cell with the formula returns the #NAME?
    message, i tried formatting the cell as general, number, currency and text, still get the same message

  4. I’ve managed to get this working on Google Docs, I had been trying to do it in Excel but it never worked, It’s not a bad thing though as the workbook that I’ve been making on Excel was going onto google docs once it was finished

    Thanks a lot for the help =)

  5. Do you ever find that the price returns “N/A” with some products even although their is a price on the page and they are in stock? and some products will return a price for so long then start showing “N/A”?

    • Oh. Yes. I just checked my old lists, and they’re mostly N/A now. I don’t know what is causing this. Maybe Google or Amazon changed something? All the Amazon pages that are N/A still have the “priceLarge” tag in their HTML-source, so it SHOULD still be working, but it is not. I think this is probably Google’s fault. I can’t do anything about it.

      It is a shame really. It was a nice way to create shopping lists and compare them over time. I hope it starts working reliably again.

  6. I think Amazon must have changed the way their site works, along with all of the tags and classes. I cannot get this to work from Amazon but it works perfectly from other sites. If anyone has any ideas how to get it working it would be greatly apreciated.

  7. I have never had success with Amazon consistently. With the same scripts I can get different results within minutes of each other. I now employ a best effort algorithm to try to capture the Amazon price (excluding marketplace) by scraping the html source and targeting specific keywords. If it can’t get the value, it simply won’t overwrite the previous price.

    I see this importxml method much better for less dynamic sites with more consistent structure. Amazon is a mess and always changing everyday.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s