Fix Google Sheets QUERY Empty Results for Mixed Data Types

A user in the Google Docs Editors Help forum recently posted: “My QUERY formula is returning empty results even though the data is clearly there. The column I’m filtering is 90% empty, and the few rows with data have mixed numbers and text. Why is Google Sheets ignoring my data?”

This is a notorious “feature” of the QUERY function’s underlying engine (the Google Visualization API). It attempts to guess the data type of a column by looking at the majority of the first 100 rows. If those rows are mostly blank, or if there is a mix of numbers and strings, the engine picks one type and treats everything else—including your valid data—as null.

The Minimal Working Example (The Quick Fix)

To force QUERY to see every cell regardless of its data type or the number of blank rows, you must convert the data range into a uniform format (usually text) before the QUERY function processes it.

The Formula:

=QUERY(ARRAYFORMULA(TO_TEXT(A1:C100)), "select Col1, Col2 where Col3 <> ''", 1)

Tested on Google Sheets as of 2024.

---

Why This Works: The Step-by-Step Breakdown

When you use a standard range like A1:C100, the QUERY function assumes the data type. By wrapping the range in ARRAYFORMULA(TO_TEXT()), you are transforming the data “on the fly” into a virtual array of strings.

1. Standardizing the Input

The TO_TEXT function forces every cell—numbers, dates, and strings—to become a string. This prevents the QUERY engine from discarding “minority” data types.

2. Switching to “Col” Notation

When you pass a standard range like A:C to a Query, you can use column letters (select A). However, when you pass a “virtual array” (the result of our TO_TEXT conversion), Google Sheets no longer recognizes column letters. You must use Col1, Col2, Col3 notation.

3. Comparing the Methods

If your data is strictly numbers or strictly text, you might not need this fix. Use the table below to decide which approach fits your dataset.

Method	Best For	Pros	Cons
Standard QUERY	Clean, uniform data types.	Fastest performance; easiest to write.	Breaks with mixed data or high blank density.
TO_TEXT Wrapper	Mixed text/numbers and mostly empty columns.	Guaranteed to “see” every cell.	You cannot perform math (like sum) on columns converted to text.
FILTER Function	Simple filtering without complex SQL needs.	Does not care about data types; very robust.	Cannot perform complex “Group By” or “Pivot” operations.

---

Adapting This to Your Own Data

If you need to fix your specific sheet, follow these steps:

1. Identify your range: Locate your source data (e.g., Data!A2:Z).
2. Wrap the source: In your formula cell, start with =QUERY(ARRAYFORMULA(TO_TEXT(Data!A2:Z)),.
3. Update your Select statement: Change select A, B to select Col1, Col2 based on the position of the columns in your range.
4. Handle Headers: If you are including the header row in your range, ensure the third argument of your QUERY is set to 1.

---

Frequently Asked Questions

What if I need to perform math on the numbers?

If you use the TO_TEXT method, you lose the ability to use sum(Col1) or avg(Col1) within the QUERY string because the numbers are now strings. In this case, instead of TO_TEXT, you should ensure the source data is cleaned. Go to the source column, select the entire column, and go to Format > Number > Plain Text (or a specific Number format) to force consistency at the source level.

Can I automate this with Apps Script?

Yes, but it is usually unnecessary. Apps Script’s getValues() method naturally returns a 2D array that doesn’t suffer from the “majority type” guessing issue. However, if you are building a dashboard for users, the ARRAYFORMULA method is preferred because it updates in real-time without needing a script trigger.

Does this work with filtered rows?

The QUERY function ignores the UI filters (the green filter icons) on your sheet. It always looks at the raw data in the range. If you want the query to react to what a user has filtered manually, you would need to use a helper column with the SUBTOTAL function, which is a more advanced workaround involving hidden rows.