Google Sheets query doesn't display all values
google-sheets, google-sheets-query
Solution
Google sheets `query` accepts only one data type per column, it detects automatically:
- if Col contains more taxt values: returns text
- if Col contains more numbers: returns numbers.
I solved the same problem with two workarounds:
- use `filter` or combine `filter` with `query`
- make extra column and convert all values into text by adiing any char, and then get rid of it.
https://support.google.com/docs/answer/3093343?hl=en
In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.
Problem
I have a `Google Sheet` with the following query formula: ``` =QUERY('Contact Changes'!B1:T,"SELECT C,D,E,F,G,H,I,J,K,L,M,N,O,R,S WHERE T='RMT'") ``` The source sheet displays the following results when filtering column `T` by `RMT`. So, the `Query` formula should produce the same results. However, column `L` does not display the field values when they are text values: If you look in `J126` (city column), you can see the value of `asdf` which is displayed correctly in the results `H2`. But, the same value in `L126` is not displayed in the results `L2`. The only way I can get a value to display in this column is by entering numbers. I have: - checked the column data format - tried removing and re-adding the column in the `Query` - changing the column order - entering the same values in a different column (they display in the results) - manually re-entering the values in the same column (they don't display in the results) Am I missing something obvious? What else can I try?