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

Question

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

Currently I'm doing this by calling the API twice, with

=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 another.

Is there a way for me to create this display with a single call?

Answer 1

ImportXML supports using the xpath | separator to include as many queries as you like.

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

However it does not expand the results into multiple columns. You get a single column of repeating triplets (or however many attributes you've selected) as shown below in column A.

The following is deprecated

2015.06.16: continue is not available in "the new Google Sheets" (see: The Google Documentation for continue).

However you don't need to use the automatically inserted CONTINUE() function to place your results.

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

Placed in B2 that should cleanly fill down and right to give you sane column data.

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