Google Sheets QUERY 无法解析查询字符串：原因与修复

Tested in: Live formula checks in Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

Google Sheets only. Excel has no QUERY function (see the Excel section).

Example data

All formulas below use this table in A1:D7:

Column A holds real dates (not text), and D holds numbers. Put the criteria in F1 (Region), F2 (minimum sales), and F3 (start date).

How to read the error message

Hover over the #VALUE! cell. The message usually has two parts:

• Unable to parse query string for Function QUERY parameter 2: (always the same)
• a code plus details, for example NO_COLUMN: East or PARSE_ERROR: Encountered " "where" "where "" at line 1, column …

NO_COLUMN: something means QUERY treated something as a column name. PARSE_ERROR ... at column N points to the character position in your query string where the parser gave up. Count to that position and look just before it.

Cause 1: Text values without quotes

Symptom: NO_COLUMN: East

=QUERY(A1:D7, "select A, D where B = East", 1)

Without quotes, East looks like a column identifier. The query language reference says string literals must be enclosed in single or double quotes.

Fix:

=QUERY(A1:D7, "select A, D where B = 'East'", 1)

Remember that string matching is case-sensitive in the query language. 'east' won’t match East. For a case-insensitive match, use lower():

=QUERY(A1:D7, "select A, D where lower(B) = 'east'", 1)

Cause 2: Cell references typed inside the string

Symptom: NO_COLUMN: F1, or no error but zero rows.

=QUERY(A1:D7, "select A, D where B = F1", 1)

QUERY can’t see cell references inside the quotes. You have to close the string, join the cell’s value with &, and reopen it.

Fix for text criteria (single quotes inside, double quotes outside):

=QUERY(A1:D7, "select A, D where B = '"&F1&"'", 1)

Fix for number criteria (no single quotes):

=QUERY(A1:D7, "select A, D where D >= "&F2, 1)

Tip: if you’re building a long query, put it in a helper cell first and look at the result:

="select A, D where B = '"&F1&"' and D >= "&F2

When you see the finished string, missing spaces and quotes jump out. Then use =QUERY(A1:D7, H1, 1) with H1 as the query.

Cause 3: An apostrophe inside the value

Symptom: PARSE_ERROR only for certain values, e.g. the rep “O’Brien”.

where C = 'O'Brien' ends the string after O, so the rest is garbage to the parser.

Fix: use double quotes around the literal inside the query. The query language reference allows both quote styles. In a formula, each literal " must be doubled:

=QUERY(A1:D7, "select A, D where C = """&F4&"""", 1)

With F4 = O'Brien, the query string becomes select A, D where C = "O'Brien".

Cause 4: Dates written the wrong way

Symptom: PARSE_ERROR, or a type mismatch message, or zero rows.

None of these work: where A > 10/15/2026, where A > '2026-10-15' (a string, not a date), or where A > "&F3 (which inserts the date’s serial number).

The query language requires the keyword date followed by a string in yyyy-MM-dd format.

Fix with a fixed date:

=QUERY(A1:D7, "select A, B, D where A >= date '2026-10-15'", 1)

Fix with a date in a cell:

=QUERY(A1:D7, "select A, B, D where A >= date '"&TEXT(F3, "yyyy-mm-dd")&"'", 1)

For timestamps, use datetime 'yyyy-MM-dd HH:mm:ss', e.g. TEXT(F3, "yyyy-mm-dd hh:mm:ss").

If column A contains dates stored as text, the comparison still fails. See Excel Dates Stored as Text: 4 Ways to Convert Them to Real Dates. The same fixes apply in Sheets.

Cause 5: Clauses in the wrong order

Symptom: PARSE_ERROR: Encountered " "where" ...

The reference says clauses are optional, but when you use them they must appear in this order:

select → where → group by → pivot → order by → limit → offset → label → format → options

=QUERY(A1:D7, "select A, D order by D desc where B = 'East'", 1)

Fix:

=QUERY(A1:D7, "select A, D where B = 'East' order by D desc", 1)

Cause 6: GROUP BY and aggregate mistakes

Symptom: ADD_COL_TO_GROUP_BY_OR_AGG: C

The reference states that when you use group by, every column in select must either be in the group by clause or wrapped in an aggregation function.

=QUERY(A1:D7, "select B, C, sum(D) group by B", 1)

Fix: group by both columns, or drop C:

=QUERY(A1:D7, "select B, C, sum(D) where B is not null group by B, C", 1)

Aggregates can’t go in where. The reference says aggregation functions can’t appear in where, group by, pivot, limit, offset or options. So where sum(D) > 2000 fails. To filter on a total, nest a second QUERY. The outer one reads an array, so it uses Col notation (see Cause 7):

=QUERY(QUERY(A1:D7, "select B, sum(D) where B is not null group by B", 1), "select * where Col2 > 2000", 1)

Aggregates only take a column ID. sum(D * 1.1) is invalid; sum(D) * 1.1 is fine.

To rename an aggregate header, the label has to match the expression exactly:

=QUERY(A1:D7, "select B, sum(D) where B is not null group by B label sum(D) 'Total sales'", 1)

Cause 7: Wrong kind of column reference

Symptom: NO_COLUMN: A (or NO_COLUMN: Sales).

There are three rules:

1. Header names don’t work. The reference says you must reference columns by identifier, never by label. In Sheets, identifiers are column letters. select Sales fails; select D works.
2. Letters only work on a plain range like A1:D7 or Sheet2!A1:D.
3. Arrays need Col1, Col2…. If the first argument is {...}, IMPORTRANGE, FILTER or another QUERY, the columns have no letters and are numbered from the left of the array instead:

=QUERY({A1:D7}, "select Col1, Col4 where Col2 = 'East'", 1)

=QUERY(IMPORTRANGE("SOURCE_ID", "Sales!A1:D500"), "select Col1, Col4 where Col2 = 'East'", 1)

If you import Sales!C1:F500, column C becomes Col1. Count from the first imported column, not from the sheet.

Cause 8: IMPORTRANGE inside QUERY isn’t authorized yet

Symptom: the same “Unable to parse query string for Function QUERY parameter 2” message, even though your query looks perfect.

The Google Docs Editors Community troubleshooting guide lists this as a symptom of a missing IMPORTRANGE authorization: the nested formula can’t show the Allow access button. Put a plain =IMPORTRANGE("SOURCE_ID", "Sales!A1:D500") in an empty cell on a desktop browser, click Allow access, then delete it. Full steps: Fix “You Need to Connect These Sheets” IMPORTRANGE #REF! Error.

Cause 9: Curly quotes and copied text

Symptom: PARSE_ERROR on a query that looks right.

If you copied a query from a document, chat or web page, the quotes may be curly (‘East’, “) instead of straight ('East', "). Retype every quote by hand in the formula bar.

A related case: if your spreadsheet locale uses a comma as the decimal separator, a number joined into the query ("where D > "&F2) may come through as 1,5 instead of 1.5, and the query language only accepts a dot. One way around this:

=QUERY(A1:D7, "select A, D where D > "&SUBSTITUTE(TO_TEXT(F2), ",", "."), 1)

Not a parse error, but looks like one: blank results

If QUERY runs without an error but returns empty cells where you expect values, check for mixed data types. Google’s QUERY documentation says that when a column mixes types, the majority type wins and the minority values are treated as null. For example, a few numbers typed as text in a mostly numeric column disappear from the result. Clean the column (see Convert Numbers Stored as Text in Excel and Google Sheets (5 Methods)). Cleaning the data is the real fix; changing the formula won’t bring the minority values back.

Also set the third argument (headers) explicitly. If you leave it out, Google says Sheets guesses the number of header rows, which can merge your first data row into the header.

Excel vs. Google Sheets

Excel has no QUERY function. Similar results come from:

• FILTER + SORT (Excel 365/2021+) for select … where … order by
• GROUPBY / PIVOTBY in newer Microsoft 365 builds, or a PivotTable, for group by
• Power Query for SQL-like transformations

If you’re choosing between QUERY and FILTER in Sheets, see FILTER vs QUERY in Google Sheets: Which One Should You Use?.

Common mistakes

1. Using == for equality. The query language uses a single =. Not-equal is != or <>.
2. Comparing to empty with = '' when you mean empty cells. Use is null or is not null (on text columns, B <> '' also works).
3. Forgetting spaces when concatenating. "where B = 'East'"&"order by D" becomes 'East'order by D. Add a space before each new clause.
4. Using letters after wrapping in { }. Braces turn the range into an array, so switch to Col1….
5. Leaving headers blank and letting Sheets guess.

FAQ

What does “Unable to parse query string for Function QUERY parameter 2” mean?

The second argument of QUERY, the query text, isn’t valid query language. Common causes are missing quotes around text, cell references typed inside the string, wrong date format, clauses out of order, or an unauthorized IMPORTRANGE.

How do I use a cell reference in a QUERY where clause?

Close the string and join the cell with &. For text: “where B = ‘“&F1&”’”. For numbers: “where D >= “&F2. For dates: “where A >= date ‘“&TEXT(F3,“yyyy-mm-dd”)&”’”.

Why does QUERY say NO_COLUMN when the column exists?

Either you used a header name instead of a column letter, or the data is an array (IMPORTRANGE, braces, FILTER), which needs Col1, Col2 notation instead of A, B.

Can I use a WHERE condition on SUM or COUNT in QUERY?

No. Aggregate functions aren’t allowed in the where clause. Wrap the grouped QUERY in a second QUERY and filter with Col notation, for example “select * where Col2 > 2000”.

Is QUERY case-sensitive?

String comparisons are case-sensitive. Use lower() or upper() on the column, for example “where lower(B) = ‘east’”.

Related tutorials

• FILTER vs QUERY in Google Sheets: Which One Should You Use? (cluster hub)
• Fix “You Need to Connect These Sheets” IMPORTRANGE #REF! Error
• Fix “Array Result Was Not Expanded Because It Would Overwrite Data” in Google Sheets
• ARRAYFORMULA vs MAP/LAMBDA vs Fill Down in Google Sheets

Sources

• Google Docs Editors Help: QUERY function (syntax, mixed data types, headers guessed when omitted): https://support.google.com/docs/answer/3093343
• Google for Developers: Query Language Reference (Version 0.7) (clause order, string/date literals, group by rule, aggregation restrictions, identifiers vs labels, case sensitivity): https://developers.google.com/chart/interactive/docs/querylanguage
• Google Docs Editors Community guide: “Resolving IMPORTRANGE Errors & Issues” (unauthorized IMPORTRANGE inside QUERY produces this error): https://support.google.com/docs/community-guide/453661366/resolving-importrange-errors-issues
• Google Docs Editors Community thread on QUERY + IMPORTRANGE needing Col notation: https://support.google.com/docs/thread/258797252