ImportXML XPath URL in Google Sheets

1
votes

I just started using google sheets and I wonder if it is possible to fetch a link using importxml XPath

https://prnt.sc/orrxpv

0

here in this screenshot, you can see the clickable link under main tournament sub-menu which will change every week. currently, I am copy-pasting it manually every week you can find it in a16 cell of the sheet linked below

https://prnt.sc/orrx4y

enter image description here

here is the XPath that I am trying to acquire XPath (I am a total noob at this) and trying to frame it into a formula in a14 cell but have failed miserably.

I also wonder that if it is possible to =concatenate something in between the link for eg: 

https://www.tennisexplorer.com/cincinnati/2019/atp-men/
https://www.tennisexplorer.com/cincinnati/2018/atp-men/
https://www.tennisexplorer.com/cincinnati/2017/atp-men/

I want to change the year in between the link but cannot figure how :(

Link to the sheet with edit permission :) https://docs.google.com/spreadsheets/d/16Y6q2tw26c-nbmqIrXiQ_ZhuDc48oAzkXvOl78kVy00/edit?usp=sharing

1
xpathgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-importxml
what exactly you tryin' to scrape? - player0

1 Answers

0
votes

to create selectable year in link use:

="https://www.tennisexplorer.com/cincinnati/"&B1&"/atp-men/"

0

to get Cincinety you can do:

=ARRAYFORMULA(QUERY(TO_TEXT(IMPORTXML(
 "https://www.tennisexplorer.com/cincinnati/2019/atp-men/", 
 "//td[@class='t-name']")), "select Col2 where Col1 is null limit 1", 0))

and then the whole formula would be:

="https://www.tennisexplorer.com/"&
 ARRAYFORMULA(QUERY(TO_TEXT(IMPORTXML(
 "https://www.tennisexplorer.com/live/", 
 "//td[@class='t-name']")), "select Col2 where Col1 is null limit 1", 0))&"/"&B1&"/atp-men/"

0