Search code examples
xpathgoogle-sheets-formula

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

Thank you guys in advance for your attention 😄

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

For example:=IMPORTXML("https://mg.olx.com.br/belo-horizonte-e-regiao/videogames/xbox-one-controle-hd-externo-975535277, XMLPATH) should return "(31) 98749-2542", which is the complete phone number. Although it has a "login wall" in order to access the complete number, I can see the full number directly on the page source code.

Firefox xPath Finder extension gives me:

/html/body/div2/div/div[4]/div2/div/div2/div2/div[9]/div/div/div2/div/div/div/div/div[3]/div/a/div2

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

Please see the attachment image to see what excatly im looking for.enter image description here

full number on source code

thank you very much!

Solution

  • Try

    =REGEXEXTRACT(importxml(A1,"//script[@id='initial-data']/@data-json"),"(\d+)"",""phoneHidden")

    Explanation

    XPATH is here //script[@id='initial-data']/@data-json and will give you the script that contains all data included in a json

    Then use regular expression to fetch the number before phoneHidden enter image description here