I am making a spreadsheet which requires me to have the number of amazon reviews of a product. I looked up how to dynamically update the reviews as more came in and came across the IMPORTXML formula.

In this specific example I can not find out how to get the information on the sheet as I keep getting the error "imported xml content can not be parsed."

My current formula is =IMPORTXML("http://www. amazon.com/Optoma-GT1080-1080p-Gaming-Projector/product-reviews/B00M9D4CAK/","//*[@id='cm_cr-product_info']/div/div[1]/div[2]/span")

(Space inserted before "amazon" to avoid the autoconversion of the link).

This is the piece i want to get.

this is what i'm trying to get

  • 1
    A couple of problems: (1) the link is a redirect, which importxml doesn't seem to handle; (2) the content is not valid XML (not all HTML is valid XML). I had a bit more luck with the URL amazon.com/gp/aw/cr/B00M9D4CAK/ref=mw_access supposedly a version optimized for screenreaders. Here, importing //* works... but I don't see the number 127 anywhere in the results, unfortunately. – user79865 Jan 2 '16 at 9:32
  • 1
    Related: Is there an Amazon.com API to retrieve product reviews?. You can see that Amazon is putting some effort into making scraping their sites difficult. It may also be illegal, by the way. – user79865 Jan 2 '16 at 9:59

Not every webpage can be scraped with importXML. There are several possible issues:

  1. Some content on the page may be generated with JavaScript, which is not executed by importXML.
  2. The page may be invalid as XML file. A typical HTML page is invalid XML (unless it is an XTHML page). The parser of importXML tries to sort things out, but in the presence of a lot of non-XML-compliant content (e.g., massive inlined scripts served by Amazon) it fails.
  3. The site may forbid bots from accessing certain content.

With Amazon you are facing the second and third obstacles. A way to get around the second issue is to use UrlFetchApp.fetch(url).getContentText() from Apps Script. This gets the entire source of the page, which you can then parse in any way you want, e.g., with regular expressions.

One has to be careful with scraping Amazon. When experimenting, I sometimes got the following message instead of the page content.

To discuss automated access to Amazon data please contact api-services-support@amazon.com.

But here is an Apps Script function that grabs the number of reviews of this product and inserts it into cell A1 of the current sheet.

function reviewCount() {   // remove space before amazon on the next line 
  var url = "http://www. amazon.com/Optoma-GT1080-1080p-Gaming-Projector/product-reviews/B00M9D4CAK/";
  var content = UrlFetchApp.fetch(url).getContentText();
  var match = /totalReviewCount">([0-9, ]+)/.exec(content);

For all I know, this may still be against Amazon's TOS. Related: Is there an Amazon.com API to retrieve product reviews?

  • I had to insert extra space into URL to prevent it from being auto-converted to SE affiliate link. – user79865 Jan 2 '16 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.