CODEWITHSUNDEEP
xmlxpathgoogle-sheetsgoogle-sheets-formula
chaimsem
chaimsem

Reputation: 41

How to use IMPORTXML with Google Sheets for multiple results with each one being in a different column?

I have this code:

=IMPORTXML(CA1,"//div[@class='1a']/div[@class='1b']/div[@class='1c']/img[@class='1d']/@src 
| //div[@class='2a']/span[@class='2b'] 
| //div[@class='3a']/span[@class='3b'] 
| //div[@class='4a']/span[@class='4b'] 
| //div[@class='5a']/span[@class='5b'] 
| //div[@class='6a']/span[6] 
| //div[@class='7a']/span[8] 
| //a/@title")

in 1 line:

=IMPORTXML(CA1,"//div[@class='1a']/div[@class='1b']/div[@class='1c']/img[@class='1d']/@src | //div[@class='2a']/span[@class='2b'] | //div[@class='3a']/span[@class='3b'] | //div[@class='4a']/span[@class='4b'] | //div[@class='5a']/span[@class='5b'] | //div[@class='6a']/span[6] | //div[@class='7a']/span[8] | //a/@title")

The function works and returns the following:

1
2
3
4
5
6
7
8

The issue is that the output is in rows.

I need it to be in columns, something like this:

https://docs.google.com/spreadsheets/d/1rVngBNR6zG1-R1wgT5WADDvT7jmWKICh7odH-47cL8w/edit?usp=sharing

etc...(blank cell is for values that are not there).

I tried TRANSPOSE, INDEX etc...

I also tried to repeat the IMPORTXML in different columns and only call for the div that I need but because the content changes dynamically on refresh, the output is not matched in each column.

How can I call all the parts of the page that I need with IMPORTXML while breaking them down into columns like the table example above?

Upvotes: 0

Views: 1829

Answers (1)

doubleunary
doubleunary

Reputation: 18784

Try this monster:

=arrayformula( query( query( iferror( if( {1,1,0}, floor( mod(row(A:A)-{1,1},{9^9, 8}), {8,1} ),  transpose( split( regexreplace( query( transpose( query( transpose( importxml(CA1, "//div[@class='1a']/div[@class='1b']/div[@class='1c']/img[@class='1d']/@src | //div[@class='2a']/span[@class='2b'] | //div[@class='3a']/span[@class='3b'] | //div[@class='4a']/span[@class='4b'] | //div[@class='5a']/span[@class='5b'] | //div[@class='6a']/span[6] | //div[@class='7a']/span[8] | //a/@title") & char(9)), "", 9^9 ) ), "", 9^9 ), "\s+$", "" ), char(9) & " ", ) ) ) ), "select max(Col3) where Col3 <> '' group by Col1 pivot Col2", 0 ), "offset 1", 0 ) )

Upvotes: 1

Related Questions