Search code examples
google-sheetsgoogle-sheets-formula

Query + Importrange function doesn't work with Contains parameter ( QUERY : PARSE_ERROR)

I would like to import a set of columns from a sheet to another with a "filter" parameter which exclude some value.

My formula look like this :

=Query(importrange("URL";"sheet name!a2:be");"SELECT Col1, Col3, Col4, Col26, Col8, Col30, Col40, Col41, Col44, Col45, Col49 WHERE Col8 NOT CONTAINS 'alc'")

However I came across with this error :

#VALEUR! Unable to parse query string for parameter 2 of function QUERY : PARSE_ERROR: Encountered " "Col8 "" at line 1, column 86. Was expecting one of: "(" ... "(" ... .

I don't know why it doesn't work, CONTAINS is a valid parameter and even after reading documentation at Google Visualization API query language i found no error in my syntax.

Solution

  • Issue:

    The correct syntax is NOT Col8 CONTAINS and not Col8 NOT CONTAINS.

    Solutions:

    =Query(importrange("URL";"sheet name!a2:be");"SELECT Col1, Col3, Col4, Col26, Col8, Col30, Col40, Col41, Col44, Col45, Col49 WHERE NOT Col8 CONTAINS 'alc'")

    Another approach would be to use Col8 <> 'alc':

    =Query(importrange("URL";"sheet name!a2:be");"SELECT Col1, Col3, Col4, Col26, Col8, Col30, Col40, Col41, Col44, Col45, Col49 WHERE Col8 <> 'alc'")