How to return multiple columns of data using ImportXML in Google Spreadsheets?

Question

I am using ImportXML in a Google spreadsheet to access the user_timeline method in the Twitter API . I would like to extract the created_at and text fields from the response and create a two-column display of the results.

I am currently doing this by calling the API twice,

 =ImportXML("http://twitter.com/status/user_timeline/matthewsim.xml?count=200","/statuses/status/created_at") 

in the cell at the top of one column and

 =ImportXML("http://twitter.com/status/user_timeline/matthewsim.xml?count=200","/statuses/status/text") 

in a different.

Is there a way to create this screen with a single call?

Answer 1

Just connect your queries with "|"

 =ImportXML("http://twitter.com/status/user_timeline/matthewsim.xml?count=200","/statuses/status/created_at | /statuses/status/text") 

Answer 2

ImportXML supports the use of the xpath delimiter | to include as many queries as possible.

 =ImportXML("http://url"; "//@author | //@catalogid| //@publisherid") 

However, it does not extend the results to multiple columns. You get one column of repeating triplets (or how many attributes you choose), as shown in column A below.

Invalid value

06/15/16: continue not available in the "new Google Sheets" (see the Google documentation for continue ).

However, you do not need to use the automatically inserted CONTINUE() function to post the results.

 =CONTINUE($A$2, (ROW()-ROW($A$2)+1)*$A$1-B$1, 1) 

Posted in B2 , which should cleanly fill left and right to give you reasonable column data.

• ImportXML is in A2 .
• A3 and below - how CONTINUE() functions are automatically populated.
• A1 is the number of attributes.
• B1:D1 is the attribute index for its columns.

Answer 3

Another way to convert rows in = CONTINUE () to columns is to use transpose ():

 =transpose(importxml("http://url","//a | //b | //c")) 

Answer 4

I asked this question to the Google Support Forum, and it was a solution that worked for me:

 =ArrayFormula(QUERY(QUERY(IFERROR(IF({1,1,0},IF({1,0,0},INT((ROW(A:A)-1)/2),MOD(ROW(A:A)-1,2)),IMPORTXML("http://example.com","//td/a | //td/a/@href"))),"select min(Col3) where Col3 <> '' group by Col1 pivot Col2",0),"offset 1",0)) 

Replace the contents of IMPORTXML with your data and query and see if this works for you. I

Apparently, this is an attempt to call the IMPORTXML function only once. At the moment, this is the solution, at least.

Here is the full thread .