Pull multiple xpath values into a single cell using IMPORTXML in Google Sheets

user_19560026 0
Viewed 60

I am trying to pull the price from Sam's Club's website into a spreadsheet (for grocery price comparison purposes). There is no element that contains the full price without additional text.

These are the elements from the website:

<span class="Price-group" title="current price: $22.98">
<span class="visuallyhidden">current price: $22.98</span>
<span class="Price-currency" aria-hidden="true">$</span>
<span class="Price-characteristic" aria-hidden="true">22</span>
<span aria-hidden="true" class="Price-mark">.</span>
<span aria-hidden="true" class="Price-mantissa">98</span>

I know how to pull in a single xpath (see my formula at the bottom of this post), but I cannot figure out how to either A) eliminate all of the text from "Price-group" or B) Combine "Price-characteristic", "Price-mark", and "Price-mantissa" into a single cell so that it appears as a single value (I can handle the formatting at that point).

I tried using a pipe to pull multiple xpaths in, but it resulted in the error "Array result was not expanded because it would overwrite data in B101". This is what I tried (eliminating the spaces before & after the pipe only resulted in the same error, just referencing a different cell): =ARRAYFORMULA(IFERROR(IMPORTXML(TO_TEXT(F100), "/html/body/div[1]/div[2]/div[2]/div[2]/div/div/div[1]/div/div[4]/div[2]/div[1]/div/span[1]/span/span[3] | /html/body/div[1]/div[2]/div[2]/div[2]/div/div/div[1]/div/div[4]/div[2]/div[1]/div/span[1]/span/span[5]","")))

My base formula, which works (The URL for IMPORTXML is stored in another cell, which is referenced): =ARRAYFORMULA(IFERROR(IMPORTXML(TO_TEXT(F100), "/html/body/div[1]/div[2]/div[2]/div[2]/div/div/div[1]/div/div[4]/div[2]/div[1]/div/span[1]/span/span[3]","")))

Here is a copy of part of the spreadsheet that I am working on. On the "HEB" page, the price pulls in because they have it as a single element on their website. On the "Sam's Club" page, I am having trouble getting the same result. https://docs.google.com/spreadsheets/d/1xPRwi0Y0wISclyaVDvCCVEaocTtpzAHu5VmHs-wpn80/edit?usp=sharing

If this was answered sufficiently in a previous thread, I would greatly appreciate a link; I've not been able to find anything that helps. Thanks very much!

google-sheets-formula google-sheets
1 Answers

I believe your goal is as follows.

  • You want to retrieve the value of 22.98 from the URL of https://www.samsclub.com/p/purina-cat-chow-indoor-dry-cat-food-hairball-healthy-weight/P03003663?xid=plp_product_1 using IMPORTXML.

In this case, how about the following sample formula?

Sample formula:

=JOIN("",IMPORTXML("https://www.samsclub.com/p/purina-cat-chow-indoor-dry-cat-food-hairball-healthy-weight/P03003663?xid=plp_product_1", "//span[@class='Price-characteristic' or @class='Price-mark' or @class='Price-mantissa']"))*1
  • In this sample formula, the values of span tag of Price-characteristic, Price-mark and Price-mantissa are retrieved and those are joined.

Testing:

When this sample formula is used, the following result is obtained.

enter image description here

Note:

  • This is a sample formula for the URL of https://www.samsclub.com/p/purina-cat-chow-indoor-dry-cat-food-hairball-healthy-weight/P03003663?xid=plp_product_1. So, when you change the URL, the formula might not be able to be used. So, please be careful about this.
user_71086530
Related