2

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.

0
2

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:

1
  • I don't get the error when backquoted, but I also don't get the data from column BY. The rest of the QUERY data does appear. Any suggestions for getting the data from BY to appear? – Mr. B Jan 6 '17 at 21:22
2

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

0

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

You can use an Array Formula to duplicate the entire "BY" column in a different column and use that duplicated column in the Query formula. Check out the following G-Sheet which shows how this workaround can be implemented (it also has the solution)

Screenshot from the G-Sheet below:

Workaround and Solution for "BY" column issue in QUERY formula

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.