1

When importing chat transcript by importXML, e.g.,

=IMPORTXML("http://chat.stackexchange.com/transcript/7/2015/8/13", "//div[@class='content']")

I find that some messages split across several columns:

+-------------------+---------------+----------------+
| "The tag-wiki ... | google-search |  tag instead.  |
+-------------------+---------------+----------------+

I understand this happens because "google-search" is within its own HTML element:

... use the <a href="//webapps.stackexchange.com/questions/tagged/google-search"><span class="ob-post-tag" style="background-color: #E2EDFA; color: #5D7A9C; border-color: #5D7A9C; border-style: solid;">google-search</span></a> tag instead. 

But I don't want to have multiple columns like that. I want all chat message in the same cell:

The tag-wiki contains the sentence: If you are talking about search, use the google-search tag instead.

Is there a way to either prevent importXML from using breaking text into columns, or to join them back? join("", importxml(...)) throws an error because the range is two-dimensional.

1
  • Try //div[@class='messages']/... This will return a 2X2 array. The first column will contain the usernames of the authors and the second column will contain the messages, preceded by the timestamp. May 30, 2016 at 2:58

1 Answer 1

1

Short answer

=TRANSPOSE(
   QUERY(
    TRANSPOSE(
      IMPORTXML(
         "http://chat.stackexchange.com/transcript/7/2015/8/13",
         "//div[@class='content']"
      )
    ),
    ,
    1000
   )
 )

Explanation

IMPORTXML is returning an array having one column by each inner span tag. There is one span tag by each smart link included in the chat message.

"It's known" that QUERY joins the headers rows. As in this case the OP wants to join rows, the formula includes TRANSPOSE two times, the first to be able to use the QUERY behavior to join columns and the second to return the array to the original shape.

Reference

2
  • It's strange how query works with empty query parameter. Is it interpreted as "select *"?
    – user79865
    Mar 19, 2016 at 20:50
  • @404: I agree that it's strange because the documentation doesn't mention that it's an optional parameter and the default is "select *". By the other side, the documentation doesn't mention that it joins the headers. Mar 19, 2016 at 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.