Solved - Google Sheets QUERY fails to parse Col references when nesting dynamic INDIRECT and IMPORTRANGE across multiple localized locales

📌 The Problem Explained

Imagine you are a global operations manager trying to consolidate sales data from regional offices in the US, Germany, and Brazil. Each office maintains its own spreadsheet. To automate your reporting, you use a combination of IMPORTRANGE to fetch the data, INDIRECT to switch between spreadsheet URLs dynamically, and QUERY to filter the results.

The frustration begins when you try to use column identifiers like Col1 or Col2. Even though your syntax looks perfect, Google Sheets throws a dreaded #VALUE! error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col1.

This happens because when you nest these functions—especially across different localized locales (where some regions use commas and others use semicolons)—Google Sheets loses track of whether it is dealing with a standard range or a virtual array. Without the correct “array literal” wrapper, the QUERY engine fails to recognize the data as a structured table, breaking your entire automation workflow.

💡 The Quick Solution

To fix this, you must force Google Sheets to treat the result of your INDIRECT and IMPORTRANGE functions as a virtual array by wrapping them in curly braces {}. This enables the Col reference syntax regardless of regional settings.

Use this formula structure:

=QUERY({IMPORTRANGE(INDIRECT("A1"), "Sheet1!A:Z")}, "SELECT Col1, Col2 WHERE Col3 > 100", 1)

Key Rule: Whenever data is fetched from an external source or generated via a nested formula, you must use the Col1, Col2, Col3 notation instead of A, B, C, and the data source must be inside {}.

🛠️ Step-by-Step Breakdown

Follow this table to correctly construct your dynamic, cross-locale query:

Step	Action	Description
1	Prepare Source URL	Place the URL of the external sheet in a cell (e.g., cell A1).
2	Define the Range	In another cell (e.g., B1), type the tab name and range (e.g., Data!A:G).
3	Nest INDIRECT	Use INDIRECT(B1) if the range string needs to be dynamic.
4	Wrap in Array Braces	Wrap the entire IMPORTRANGE function in { }. This is the “Magic Fix.”
5	Write the QUERY	Use SELECT Col1, Col2 (where 1 is the first column of the imported range).
6	Set Header Count	Always include the last argument (usually 1) to define the header row.

The Final Multi-Locale Syntax

If you are in a locale that uses semicolons (like many European countries), your formula will look like this:

=QUERY({IMPORTRANGE(A1; "Data!A:E")}; "SELECT Col1 WHERE Col2 = 'Completed'"; 1)

⚠️ Common Mistakes to Avoid

1. Using A, B, C instead of Col1, Col2: When you wrap a range in curly braces {} or use IMPORTRANGE, Google Sheets no longer sees “Column A.” It sees the “1st Column of the Array.” Always use the Col prefix (case-sensitive in some instances, so use Col1).
2. Missing Permissions: Even with the correct formula, IMPORTRANGE will fail if the sheets aren’t linked. You must manually click the cell and select Allow Access before the QUERY can parse the data.
3. Locale Separator Mismatch: If you are copying a formula from a US tutorial into a localized sheet (e.g., Brazil or Germany), you must swap commas (,) for semicolons (;) for function arguments, but keep the space or comma inside the QUERY string itself consistent with SQL syntax.

By mastering the array literal wrapper, you ensure your google-sheets automation remains robust across different regional settings and complex data structures.