How to figure out proper xpath for IMPORTXML in Google Sheets - Receiving An Error?

0
votes

I'm trying to use the IMPORTXML function on Google Sheets.

For example:=IMPORTXML("https://exolyt.com/user/amazonprimevideo/full, XMLPATH) should return "1.8K", which is the Avg. Video Shares

I used the Chrome inspector to copy the xpath, which gives me:

/html/body/div[1]/div[1]/div/div/div/div[3]/div[1]/div/div[2]/div/div[1]/div[3]/div[6]/div[3]/div[2]

When I try this in Google Sheets it returns an error: #N/A (Import Content is Empty).

P.S. I'm open to other ways to get the data I need into the google sheet, it doesn't have to use the IMPORTXML function.

1
xmlgoogle-sheetsgoogle-sheets-formulagoogle-sheets-importxml

1 Answers

2
votes

I believe your goal as follows.

  • You want to retrieve the value of "Avg. Video Shares".
  • For example, in this case, 1.8K is the value you need.

For this, I would like to propose the following modified formula.

Modified formula:

=IMPORTXML(A1,"//div[../div[text()='Avg. video shares']][2]")
  • In this case, the URL of https://exolyt.com/user/amazonprimevideo/full is put to the cell "A1".
  • I used //div[../div[text()='Avg. video shares']][2] as the xpath.

Result:

enter image description here

Note:

  • As other xpath, in your case, you can also use /html/body/div[1]/div/div/div/div/div/div[1]/div/div[2]/div/div[1]/div[2]/div[5]/div[3]/div[2] as the xpath like below.

      =IMPORTXML(A1,"/html/body/div[1]/div/div/div/div/div/div[1]/div/div[2]/div/div[1]/div[2]/div[5]/div[3]/div[2]")

  • From I'm open to other ways to get the data I need into the google sheet, it doesn't have to use the IMPORTXML function., if you want to achieve your goal using Google Apps Script, you can also use the following script as the custom function. In this case, please copy and paste the following script to the container-bound script of Spreadsheet, and put =customFunction() to a cell. By this, the value of 1.8K is retrieved.

      function customFunction() {
    const url = "https://exolyt.com/user/amazonprimevideo/full";
    return UrlFetchApp
      .fetch(url)
      .getContentText()
      .match(/Avg\. video shares<\/div><div .+?>(\w.+?)<\/div>/)[1];
  }

  • At above modified formula and sample script, when the URL is changed, the result you expect might not be able to be retrieved. So please be careful this.

Reference: