Unable import text using importxml and xpath inside div

0
votes

i'm Using Google Sheets with IMPORTXML to scrape a download count information from a japanese website via XPath in google sheet. I want to save the number/text inside this red box

here's the link https://www.photo-ac.com/main/detail/4465781?title=%E3%82%A2%E3%82%B2%E3%83%8F%E8%9D%B6%E3%81%A8%E3%83%92%E3%83%A3%E3%82%AF%E3%83%8B%E3%83%81%E3%82%BD%E3%82%A6

here's my function

=IMPORTXML("https://www.photo-ac.com/main/detail/4465781?title=アゲハ蝶とヒャクニチソウ", "/html/body/div[17]/div/div/div/div[2]/div[7]/div[1]/div[1]/div/div[3]/div[2]/div[1]//text()")

the function doesn't work? why?

thank you

1
google-sheetsxpathgoogle-sheets-formula

1 Answers

1
votes

When I tested your formula, I confirmed that an error of Could not fetch url: occurred. But, fortunately, when Google Apps Script is used, I confirmed that the URL can be requested using UrlFetchApp. So, in this answer, I would like to propose to use Google Apps Script. The sample script is as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet, and save it, and put a formula of =SAMPLE("URL") to a cell. If the function name is not found, please reopen the Google Spreadsheet and test it again. This script is used as the custom function.

function SAMPLE(url) {
  const value = UrlFetchApp.fetch(url).getContentText().match(/ダウンロード：.+/);
  if (!value) throw new Error("Value was not retrieved.");
  return value;
}

Result:

When above script is used, the following result is obtained.

enter image description here

Note:

  • This sample script is for the current HTML of the URL of https://www.photo-ac.com/main/detail/4465781?title=アゲハ蝶とヒャクニチソウ. And, when the structure of HTML of the URL is changed, above script might not be able to be used. Please be careful this.

References: