Google Sheets IMPORTXML – Get URL from Web Page

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryimportxml

Though I am new to web scraping, but I have a hand on experience of web scraping using VBA (Excel) I am facing the following problem:

I have tried all methods (ImportXML, ImportHTMP, ImportData, etc.) for importing data from web-page but getting #N/A error. URL from which I am trying to import is
Link and the formula is as following (C3, contains a link):

=importXML(C3,"//td/a/@href")

From this method, I want to import all links available in a table (I want to get cartoon episodes' URL from web-page of Cartoon series).

Please help me to figure out the mistake I am performing.

Best Answer

=ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN(" ", 
 "https://kimcartoon.to"&QUERY(IMPORTXML(C3, "//a/@href"), 
 "select Col1 where Col1 contains '"&("pisode")&"' ")), " ")))

Related Solutions

Google-sheets – Import table using IMPORTXML

You seem to have forgotten to mention what you would consider tidy, but it should be easier to handle your data (assumed to be starting in A1) with a formula such as:

=offset(A1,int(2*((row()-1)/2)),0)

in B1 and the same again in C1 with an extra +1 immediately before the ,0 - both copied down to suit.

OFFSET
INT
ROW

Google-sheets – Unable to importxml from this URL

IMPORTXML only is able to access public web resources but he URL in the formula requires user and password so it couldn't be reached.

Best Answer

Related Solutions

Google-sheets – Import table using IMPORTXML

Google-sheets – Unable to importxml from this URL

Related Topic