How to import data from one sheet to "only one cell" in another sheet

Question

Using google sheets, I am able to transfer data from one sheet to another sheet using

=IMPORTRANGE

but IMPORTRANGE creates more cells depending on how many cells you are copying over. I need to copy all data from sheet1 into only one cell of sheet 2. Is there a way to do that?

Thanks!

Answer 1

After you authorize the access to the external spreadsheet, try

=JOIN(CHAR(10),QUERY(TRANSPOSE(IMPORTRANGE(spreadsheet_key,reference_string)),,2E6))

If the source data is

+---+----+----+
|   | A  | B  |
+---+----+----+
| 1 | A1 | B1 |
| 2 | A2 | B2 |
+---+----+----+

the result will be

+---+---------+
|   |   A     |
+---+---------+
| 1 | A1 B1   |
|   | A2 B2   |
+---+---------+