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

0
votes

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?

1
xmlxpathgoogle-sheetsgoogle-sheets-formula
Please share a publicly editable sample spreadsheet with data so that contributors can try their formulas. - doubleunary
@doubleunary I would it's just sensitive info and idk what website I can replicate this from - chaimsem
Just put some static data in Sheet1 that looks like your importxml() results, and show in Sheet2 how you would like to organize that data. - doubleunary
@doubleunary I did that, not sure if this is what you're looking for. I would like to add that I found a certain loophole sort of.....I would importxml in sheet1 let's say and it would spit the items in a row and then in sheet2 i would query the items one by one column by column. the only problem is that then the columns don't match, because some items have 8 rows and some have 7 so i need the query to add a blank cell where there is no value from import or when import doesnt find the span to add a blank cell. - chaimsem
Your sample spreadsheet gives "Access denied." To best get help, please share it with "can edit" rights as explained in these instructions. Post back in this thread with the link. - doubleunary

1 Answers

1
votes

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 ) )