Google Sheets query doesn't display all values

google-sheets google-sheets-query
davids picture davids · May 1, 2017 · Viewed 15.4k times · Source

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.

enter image description here

However, column L does not display the field values when they are text values: enter image description here

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?

Answer

Max Makhrov picture Max Makhrov · May 1, 2017

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.

Related questions

QUERY syntax using cell reference

I'm having trouble figuring out a fairly simple QUERY statement in Google Spreadsheets. I'm trying to use a cell reference instead of static values and I'm running into trouble. Below it the code I'm using, but I keep getting a "…

Read More
Google SpreadSheet Query: Can I remove column header?

I'm doing this query at my google spreadsheet: =QUERY(H4:L35;"select sum(L) where H='First Week'"; -1) But it returns a little table with "sum" as header and result below it. What I want is just the result! …

Read More
Google Sheets: How to replace text in column header?

I have a query like this =QUERY(B2:C9; "select (C * 100 / B) - 100") in my Google Sheets. What is displayed as a column header is: difference(quotient(product(100.0()))100.0()). I want to put a human readable description there instead. How can …

Read More