CODEWITHSUNDEEP
xpathgoogle-apps-scriptweb-scrapinggoogle-sheets
user13115879
user13115879

Reputation:

Xpath notation to get prices from webuy API

I want to web scrapping from Cex to my Google Spreadsheet. I already have some products (example: iphone 7 plus 64GB unlocked B), and I want to get prices from this web site. It is important to mention that I already found the API Documentation API DOCUMENTATION

I also found the function that extracts the data from the web site. It's called ImportJSON ( (credits to Brad Jasper), IMPORTJSON ) and I can use it on google spreadsheets, then it requests with QUERY formula.

However, I have something wrong with the XPATH, because it has an error and returns me #VALUE (Unable to parse query string for the function query parameter 2: NO_COLUMN: Col4 could you please help me? I need the path or any other way to get those prices on my sheet.

E.Wiest told me this: You can import the data with the following script (credits to Brad Jasper) : ImportJSON, then request with QUERY formula. This is an example with "iPhone 8" and "Playstation 4".

This is how it should works!

In column A, you write the product to search. The url to get the JSON data is automatically build in column B with a concat operator.

="https://wss2.cex.uk.webuy.io/v3/boxes?q="&A2

In column C, you have the QUERY formula combined with the ImportJSON data step.

=QUERY(ImportJSON(B2);"SELECT Col4,Col20 WHERE Col4 CONTAINS 'Plus' AND Col4 CONTAINS '64' AND Col4 CONTAINS 'Unlocked' LIMIT 1 label Col4'',Col20''";1)

Col4 : product description, Col20 : price of the product. Since the JSON will return a lot of results (multiple iPhone 8 versions), this is the step where you can refine your search. I've searched for "Plus","64" and "Unlocked" in the product description.

However, when I do it on my google spreadsheet, it appears: Unable to parse query string for the function query parameter 2: NO_COLUMN: Col4. Is there any other way to get this? can you please help me how to fix the path? I would wholeheartedly appreacite your support!

API: Search: Sellprice: I need the "sellprice"

Upvotes: 0

Views: 201

Answers (1)

E.Wiest
E.Wiest

Reputation: 5915

It works. I've shared the sheet :

https://docs.google.com/spreadsheets/d/1Q88_hHKmIWTzikRxY9cM_ZkM4xS4eicfN9C8rqvsunQ/edit?usp=sharing

The price returned is the sell price.

https://i.sstatic.net/heO5y.png

iPhone

Note : iphone 7 plus 64GB unlocked B returns no result.

Upvotes: 1

Related Questions