Google Spreadsheets Importxml output into one cell

Question

I'm scraping the users from a community using google spreadsheets importxml function.

Cell=A1

http://inbound.org/members/all/top?&per_page=48

Cell=B1

=IMPORTXML(A1,"//div[@class='title name']//a/@href")

When I use the b1 formula to scrape the users, the output is across 48 cells in column B. Is there a way I can customize the output? I have lots of inbound pages on cell A1 that I want to apply the formula to by pulling the drop down tag on B1 to apply the formula to each url on A1, but I can't do this because the B column is already filled with the first scrape.

Is there a way to put all usernames scraped into just B1 separated by commas?

I tried A:A to see if it would scrape all urls from column A in one go, but I guess that doesn't work.

=IMPORTXML(A:A,"//div[@class='title name']//a/@href")

Answer 1

This does not preserve formatting, but this is a super quick fix that you can add to your formula in seconds.

Just wrap your existing function with a textjoin. Here's how it would look:

=TEXTJOIN(",",1,IMPORTXML(A:A,"//div[@class='title name']//a/@href")

This puts everything in the same cell where you have your IMPORTXML. Hope that helps.

Answer 2

Why to write a script function? There are build in JOIN in Google Spreadsheets

=JOIN(CHAR(10), IMPORTXML(A1,"//div[@class='title name']//a/@href"))

CHAR(10) is for new line character

Answer 3

There is no way to have the XPath expression return comma-separated values, because Google Sheets only supports XPath 1.0 (and even there the support is very buggy).

But you can write your own function that takes a range of cells as input and returns a comma-separated list of strings:

function rangeToStringSep(range, sep) {
  return range.join(sep);
}

This is taken from here. Then, in your Google Sheet, use

=rangeToStringSep(B1:B48, ", ")

and all strings will be displayed in a single cell: