google-sheetsgoogle-sheets-formulaflatten

IMPORTRANGE Formula, returns some values in the same cell in Google Sheets


I am trying to make a Google Sheet, where it draws data from others sheets and collects them there. I have many columns and for most of them I used the following formula, which worked flawlessly.

=FLATTEN(
  {
    QUERY(IFERROR(IMPORTRANGE("Google_Sheet_1_URL", "Sheet_Name!C12:F")), "SELECT Col1 WHERE Col4 IS NOT NULL");
    QUERY(IFERROR(IMPORTRANGE("Google_Sheet_2_URL", "Sheet_Name!C14:F")), "SELECT Col1 WHERE Col4 IS NOT NULL")})

But for only 1 out of the 8 columns that I am using this formula on, the formula returns many values in one cell, instead of each value at its own cell and row.

For example the Sheet1 is like this:

| Column A | 
| green    | 
| green    | 
| blue     | 
| green    | 

and Sheet2 is like this:

| Column A | 
| pink     | 
| green    | 
| red      | 
| green    | 

and I want it to return:

| Column A | 
| green    |  
| green    | 
| blue     |
| green    | 
| pink     |
| green    | 
| red      | 
| green    |   

but instead I get:

| Column A | 
| green    |  
| green    | 
| blue     |
| green    | 
| pink green red|
| green    | 

I am confused, since the same formula works fine without any issues like this for the rest of the columns and sheets, it only has an issue on one column in each sheet, the one with the colors.


Solution

  • Try something like this:

    =query(A1:B3,"select *",0)
    
    • set the third parameter [headers] to 0 to tell query there's no headers in that range; set as 1 to attribute first row as headers

    • as per query documentation: If omitted or set to -1, the value is guessed based on the content of data & sometimes results in the data coalesce(as happening in your scenario)