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

Website Link

CodePudding user response：

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.

Pattern 1:

Pattern 2:

Note:

References: