Fix Google Sheets QUERY Missing Data from IMPORTRANGE

Someone on a Google Sheets community forum recently posted: “I’m using QUERY to pull data from another spreadsheet via IMPORTRANGE. Even though I’ve specified the header count, some columns return blank cells even though I can see data in the source. It seems like if a column has mostly numbers but a few text strings, the text disappears. How do I force QUERY to see everything?”

This is a classic “Data Type Mismatch” error. The Google Sheets QUERY function uses the Google Visualization API, which assigns a single data type (string, number, or boolean) to an entire column based on the majority of the data. If 90% of your column is numeric, QUERY treats the column as numeric and returns null for any text it encounters.

The “One-Click” Quick Fix

The fastest way to fix this without changing your source data is to wrap your IMPORTRANGE in an ARRAYFORMULA that forces every cell into a text format.

The “Magic” Formula:

=QUERY(ARRAYFORMULA(TO_TEXT(IMPORTRANGE("URL_HERE", "Sheet1!A:Z"))), "select Col1, Col2 where Col1 is not null", 0)

Tested on Google Sheets as of 2024.

---

Step-by-Step Breakdown

When you combine QUERY and IMPORTRANGE, the data is processed in a “virtual” space before it hits your sheet. To stop QUERY from ignoring mixed data, you must standardize the data type before the QUERY function analyzes it.

1. Wrap the source in TO_TEXT

By using TO_TEXT, you tell Google Sheets to ignore whether a cell is a date, a number, or a string, and simply treat it as text. This prevents the “majority rule” from discarding “minority” data types.

2. Use Column Indexes (Col1, Col2)

When you wrap an IMPORTRANGE in another function (like ARRAYFORMULA), you can no longer use lettered column references (A, B, C). You must use the Col notation.

Component	Purpose
IMPORTRANGE	Fetches the data from the external spreadsheet.
TO_TEXT	Converts every piece of data into a string format.
ARRAYFORMULA	Ensures TO_TEXT applies to every cell in the range, not just the first one.
QUERY(..., "...", 0)	Filters the now-standardized data. The 0 indicates no headers in the range (since TO_TEXT often consumes them).

---

Comparison of Solutions

Depending on your specific needs, you might choose one of these three methods:

Method	Pros	Cons
TO_TEXT Wrapper	Easiest fix; preserves all data visibility.	Math functions like SUM or AVG won’t work inside the QUERY string.
Helper Column at Source	Most stable; allows for clean data types.	Requires editing the source document, which you may not own.
The &“” Trick	Shortest formula: IMPORTRANGE(...)&"".	Can occasionally cause performance lag on very large datasets.

---

Common Traps and Troubleshooting

What if I need to do math on the results?

If you use the TO_TEXT method, you cannot use select sum(Col2). To perform math, you have two options:

1. Format at the Source: Go to the source sheet and ensure the column is strictly formatted as Format > Number > Plain Text or Number.
2. Double Query: Use one QUERY with TO_TEXT to pull the data, and a second QUERY or formula outside of it to convert specific columns back to numbers using VALUE().

Why did my headers disappear?

The TO_TEXT function often confuses the QUERY header detection. If your headers are missing or being treated as data, explicitly set the last argument of your QUERY to 1 and ensure your range includes the header row.

Can I automate this for new columns?

Yes. If you add new columns to your source sheet, ensure your IMPORTRANGE uses an open-ended range like Sheet1!A:Z. The ARRAYFORMULA(TO_TEXT(...)) wrapper will automatically scale to accommodate any new data brought in.

Does this impact performance?

Using ARRAYFORMULA on a massive range (e.g., 50,000+ rows) can slow down your spreadsheet’s calculation time. If performance is an issue, it is always better to clean the data at the source by ensuring each column contains only one data type, rather than forcing a conversion during the import.