QUERY error for BY column

Posted on

Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common formula parse errors in Google Sheets, like QUERY error for BY column, and how to fix it.

I’m using the following query to pull data from one sheet into another.

=query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, BB, BZ, CA, BA where J = 'Industry Certification'",false)

The above query works perfectly. However, if I change ‘BB’ to ‘BY’. I get the following error.

Error

Unable to parse query string for Function QUERY parameter 2:
PARSE_ERROR: Encountered “BY” at line 1, column 35. Was expecting one
of: “true” … “false” … “date” … “timeofday” … “datetime” …
“timestamp” … “min” … “max” … “avg” … “count” … “sum” …
“no_values” … “no_format” … “is” … “null” … “year” … “month”
… “day” … “hour” … “minute” … “second” … “millisecond” …
“with” … “contains” … “starts” … “ends” … “matches” … “like”
… “now” … “dateDiff” … “quarter” … “lower” … “upper” …
“dayOfWeek” … “toDate” … … …
… … … “(” … “-”

The value of cell ‘BY’ is a simple string (activities).

I’m scratching my head confused. Any help is greatly appreciated.

Answer :

Short answer

According to the documentation, BY is a reserved word, to use it as an identifier, it should be back-quoted.

Explanation

I think that it’s a reserved word because it’s used by two clauses:

use ` instead of ' like:

=QUERY(data!B2:CL, "select B, C, D, E, F, G, H, I, J, `BY`, BZ, CA, BA 
                    where J = 'Industry Certification'", 0)

3

Although the answer provided by user0 is the best solution for this, I also wanted to add an alternative workaround for this.

Leave a Reply

Your email address will not be published.