CODEWITHSUNDEEP
xpathweb-scrapinggoogle-sheetsgoogle-sheets-formula
steveorg
steveorg

Reputation: 91

importxml function in Google Sheets returns #N/A

I'm trying to import a list of seller ratings on Amazon using a div id. This is the code:

=importxml("https://www.amazon.com/sp?_encoding=UTF8&marketplaceID=ATVPDKIKX0DER&seller=A2JWZOZUWVCPDC", "//[@id='feedback-content']")

An #N/A is returned. I confirmed that the content is not created by Javascript. I'd appreciate any guidance.

EDIT

The solution from @player0 works well on Amazon US, but on other Amazon marketplaces the 2nd column has errors.

On Amazon CA, only 3 out of 5 feedbacks appeared in the wrong row.

Amazon UK has the same issue. A seller response ("We shipped...") is included. It is unneeded, but would be nice in a 3rd column.

The image below displays the actual results on the left, and the expected results on the right.

Valid XHTML

CA formula in B4:

=ARRAYFORMULA(REGEXEXTRACT(QUERY(ARRAY_CONSTRAIN(IMPORTDATA(
 "https://www.amazon.ca/sp?_encoding=UTF8&marketplaceID=A2EUQ1WTGCTBG2&seller=A1FLFF2FVHQO18&sshmPath="), 3000, 1), 
"where Col1 contains 'a-text-quote' and Col1 matches '.*</span>$'", 0), ">(.*)</"))

UK formula in B12:

=ARRAYFORMULA(REGEXEXTRACT(QUERY(ARRAY_CONSTRAIN(IMPORTDATA(
 "https://www.amazon.co.uk/sp?_encoding=UTF8&marketplaceID=A1F83G8C2ARO7P&seller=A10B9ILJZU6LK2"), 3000, 1), 
 "where Col1 contains 'a-text-quote' and Col1 matches '.*</span>$'", 0), ">(.*)</"))

I tried several other divs and ids for the Col1 contains and Col1 matches but always received errors. Any additional help would be highly appreciated.

Upvotes: 0

Views: 323

Answers (1)

player0
player0

Reputation: 1

A1:

=ARRAYFORMULA(REPT("★", REGEXEXTRACT(QUERY(ARRAY_CONSTRAIN(IMPORTDATA(
 "https://www.amazon.com/sp?_encoding=UTF8&marketplaceID=ATVPDKIKX0DER&seller=A2JWZOZUWVCPDC"), 3000, 1), 
 "where Col1 matches '.*a-icon a-icon-star a-star-\d+ feedback-stars.*'", 0), "\d+")))

enter image description here

B1:

=ARRAYFORMULA(REGEXEXTRACT(QUERY(ARRAY_CONSTRAIN(IMPORTDATA(
 "https://www.amazon.com/sp?_encoding=UTF8&marketplaceID=ATVPDKIKX0DER&seller=A2JWZOZUWVCPDC"), 3000, 1), 
 "where Col1 contains 'a-text-quote' and Col1 matches '.*</span>$'", 0), ">(.*)</"))

enter image description here

Upvotes: 2

Related Questions