CODEWITHSUNDEEP
xpathgoogle-sheetsgoogle-sheets-formula
Matthew Simoneau
Matthew Simoneau

Reputation: 6279

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

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?

Upvotes: 8

Views: 19236

Answers (5)

Will
Will

Reputation: 111

This is the best solution (NOT MINE) posted in the comments below. To be honest, I'm not sure how it works. Perhaps @Pandora, the original poster, could provide an explanation.

=ArrayFormula(iferror(hlookup(1,{1;ARRAY},(row(A:A)+1)*2-transpose(sort(row(A1:A2)+0,1,0)))))

This is a very ugly solution and doesn't even explain how it works. At least I couldn't get it to work due to multiple errors, like i.e. to much parameters for IF (because an array is used). A shorter solution can be found here =ArrayFormula(iferror(hlookup(1,{1;ARRAY},(row(A:A)+1)*2-transpose(sort(row(A1:A2)+0,1,0))))) "ARRAY" can be replaced with IMPORTXML-Function. This function can be used for as much XPATHS one wants. – Pandora Mar 7 '19 at 15:51

In particular, it would be good to know how to modify the formula to accommodate more columns.

Upvotes: 1

aknt
aknt

Reputation: 1

I posed this question to the Google Support Forum and this is 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 that works for you. I

Apparently, this attempts to invoke the IMPORTXML function only once. It's a solution for now, at least.

Here's the full thread.

Upvotes: 0

Lake
Lake

Reputation: 1155

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.

example screenshot

  • 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.

Upvotes: 9

Moogle
Moogle

Reputation: 107

Another way to convert the rows of =CONTINUE() into columns is to use transpose():

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

Upvotes: 9

David
David

Reputation: 166

Just concatenate your queries with "|"

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

Upvotes: 6

Related Questions