=importxml, Website to Google Sheets - getting #N/A every time

Question

Website Link

https://redacted

xml options I have tried so far

<span aria-labelledby="amount">722</span>

//*[@id="amount"]/h3/span[2]

/html/body/div[3]/main/div/span/div/div/div[2]/div/div/div[2]/div/div[2]/div[3]/div/div/div/div[2]/div[1]/h3/span[2]

None working

Trying to =importxml from here @ a value of "722" this is value on 5/5/22 anyway.

TIA!

Answer 1

Unfortunately, it seems that your expected value cannot be directly retrieved using the XPath. Because the value is put to the HTML using Javascript and IMPORTXML cannot analyze the result of Javascript. But, fortunately, it seems that your expected value is included in the HTML as the JSON data. So, in this answer, I would like to retrieve the value from the JSON data.

Pattern 1:

In this pattern, IMPORTXML and REGEXEXTRACT are used.

=ARRAYFORMULA(REGEXEXTRACT(IMPORTXML(A1,"//script[@data-component-name='GetOfferWrapper']"),"defaultEstimatedValue"":(.+?)}"))

• The URL https://www.gazelle.com/iphone/iphone-13-pro-max/other/iphone-13-pro-max-1tb-other/498082-gpid is put in the cell "A1".

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

  

Pattern 2:

In this pattern, a custom function created by Google Apps Script is used. When the value is retrieved from JSON data, Google Apps Script is useful. When you use this script, please copy and paste the following script to the script editor of Spreadsheet and save the script. And, please put a custom function of =SAMPLE("https://www.gazelle.com/iphone/iphone-13-pro-max/other/iphone-13-pro-max-1tb-other/498082-gpid") to a cell.

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url).getContentText();
  const data = res.match(/<script.+data-component-name="GetOfferWrapper".+?>([\w\s\S]+?)<\/script>/);
  if (!data || data.length == 0) return "No data";
  const obj = JSON.parse(data[1]);
  return obj.initState.defaultEstimatedValue;
}

• The URL https://www.gazelle.com/iphone/iphone-13-pro-max/other/iphone-13-pro-max-1tb-other/498082-gpid is put in the cell "A1".

• When this formula is used, the value of 722 is retrieved.

Note:

• The formula and custom function can be used for the current HTML. So, when the specification of HTML is changed, those might not be able to be used. Please be careful about this.

References:

• IMPORTXML
• REGEXEXTRACT
• Custom Functions in Google Sheets
• fetch(url)
• JSON.parse()

Answer 2

you will need to find another site with intel you attempting to scrape. the #N/A error is the result of google sheets not supporting the import of JavaScript elements. you can always check for compatibility by disabling JS in site settings and only what's left can be usually scrapped. in this case its nothing: