14

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?

davids
  • 5,397
  • 12
  • 57
  • 94

4 Answers4

13

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.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • It is helpful to know that Query auto-sets the data type and accepts only one. However, it seems that the filter, or adding and removing a column is lots of extra work. Why not just set the source column values to `plain text`? – davids May 01 '17 at 20:52
  • You may try asking this Q on Google forum: https://productforums.google.com/forum/#!topic/docs/JoNRvkhVomI;context-place=forum/docs – Max Makhrov May 02 '17 at 05:41
  • I'm trying to combine `filter` with `query`... Can you please show an example? Should I use ArrayFormula? P.S. I am doing that in other GSheet through `importRange`. Looks like there is no a workaround :( – Andrew Anderson Jun 22 '17 at 13:53
  • I have a similar problem but it doesn't seem to be solved in the same way. When the result of the query is less than three lines, only columns with numbers are shown, not those with text. Anyone know why this may be due? – Mario Andrés Nov 11 '19 at 15:04
12

In addition to Max' contribution, you can also preprocess the data so that it's all converted to text and THEN wrap the query around this output. Something like

=QUERY(ArrayFormula(TO_TEXT('Contact Changes'!B1:T)),"SELECT Col3, Col4,    Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col17, Col18 WHERE Col19='RMT'")
JPV
  • 26,499
  • 4
  • 33
  • 48
5

I had the same problem in the past and today I tried to just format the entire column to "plain text" and it worked perfectly.

No problems anymore and every value is displayed.

Nicholas Delkeskamp
  • 101
  • 2
  • 3
  • Just be advised that one has to go update that formatting regularly, as it usually is lost for new entries into the database. – Canned Man Jun 10 '21 at 16:50
1

An example of combining with Filter with Query for those who may find it helpful:

=QUERY(FILTER('Original Statement'!A:Z, 'Original Statement'!A:A = "Trades"), "select *")
Peter Berg
  • 6,006
  • 8
  • 37
  • 51