Google Sheets – Problem Combining LEFT() and QUERY() Functions

formulasgoogle sheetsgoogle-sheets-query

I'm currently working in Google Sheets and I need to import a part of a row from a spreadsheet to another. I'm importing the row with the QUERY() function and "cutting" the data from the row with LEFT() function. Something like this:

=LEFT(QUERY(IMPORTRANGE("1MkAat5Q791H0UKOuGwMIz2d8crocbNecyF4jnXo9K58";
 "Notas 1ºA No Pivoteadas!A1:D527");"select Col1");8)

The problem is that only applies the LEFT() function to the first cell of the row and the whole row taken from the query is filled with the same cell.

Any suggestions?

Best Answer

add continuosity with ARRAYFORMULA():

=ARRAYFORMULA(LEFT(QUERY(IMPORTRANGE("1MkAat5Q791H0UKOuGwMIz2d8crocbNecyF4jnXo9K58"; 
 "Notas 1ºA No Pivoteadas!A1:D527"); "select Col1"); 8))

for number-text combo add TO_TEXT():

=ARRAYFORMULA(LEFT(QUERY(TO_TEXT(IMPORTRANGE(
 "1MkAat5Q791H0UKOuGwMIz2d8crocbNecyF4jnXo9K58"; 
 "Notas 1ºA No Pivoteadas!A1:D527")); "select Col1"); 8))

Related Solutions

Google Sheets Query – How to Reference a Cell Containing a Date

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:

=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))

You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.

That said, I find a solution using filter slightly more readable:

=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))

Query IMPORTRANGE Function to Skip Columns in Google Sheets

No, it's not possible to "skip columns".

Instead you could use two query functions, one to be used to get the columns A - C, the other to get the columns F and following.

First formula (A1):

=QUERY(
   IMPORTRANGE("URL","Sheet Name!A2:P"),
   "SELECT Col1,Col2,Col3
      WHERE Col11 CONTAINS 'West'",
    1
 )

Second formula (F1):

=QUERY(
   IMPORTRANGE("URL","Sheet Name!A2:P"),
   "SELECT Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 
      WHERE Col11 CONTAINS 'West'",
    1
 )

REMARKS:
Note that instead of Sheet Name!A2:P1000 was used Sheet Name!A2:P as this take the whole columns instead of only 1000 rows.

Best Answer

Related Solutions

Google Sheets Query – How to Reference a Cell Containing a Date

Query IMPORTRANGE Function to Skip Columns in Google Sheets

Related Topic