I have a <code>Google Sheet</code> with the following query formula: <pre class="prettyprint"><code>=QUERY('Contact Changes'!B1:T,"SELECT C,D,E,F,G,H,I,J,K,L,M,N,O,R,S WHERE T='RMT'") </code></pre> The source sheet displays the following results when filtering column <code>T</code> by <code>RMT</code>. So, the <code>Query</code> formula should produce the same results. <img src="https://i.stack.imgur.com/UPi5X.png" alt="enter image description here"> However, column <code>L</code> does not display the field values when they are text values: <img src="https://i.stack.imgur.com/FmqBg.png" alt="enter image description here"> If you look in <code>J126</code> (city column), you can see the value of <code>asdf</code> which is displayed correctly in the results <code>H2</code>. But, the same value in <code>L126</code> is not displayed in the results <code>L2</code>. The only way I can get a value to display in this column is by entering numbers. I have: <ul> <li>checked the column data format</li> <li>tried removing and re-adding the column in the <code>Query</code> </li> <li>changing the column order</li> <li>entering the same values in a different column (they display in the results)</li> <li>manually re-entering the values in the same column (they don't display in the results)</li> </ul> Am I missing something obvious? What else can I try?

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 <pre class="prettyprint"><code>=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'") </code></pre>

Google sheets <code>query</code> accepts only one data type per column, it detects automatically: <ul> <li>if Col contains more taxt values: returns text</li> <li>if Col contains more numbers: returns numbers.</li> </ul> I solved the same problem with two workarounds: <ul> <li>use <code>filter</code> or combine <code>filter</code> with <code>query</code> </li> <li>make extra column and convert all values into text by adiing any char, and then get rid of it.</li> </ul> <hr> https://support.google.com/docs/answer/3093343?hl=en <blockquote> 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. </blockquote>

Google Sheets query doesn't display all values

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?

Why are my columns limited in Google Sheets?

Maximum of 5 Million Cells These can be in a single worksheet or in multiple sheets. In case you already have the 5 million limit reached in fewer columns, it will not allow you to add more columns (and vice versa, i.e., if 5 million cells limit is reached with a certain number of rows, it will not allow more rows).

How do I limit query results in Google Sheets?

The LIMIT clause reduces the quantity of rows that is pulled from another sheet. “ select * Limit 5" – the string pulls all data and limits the returned result to the first 5 rows + the header.

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'")

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.

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.

Google Sheets query doesn't display all values

Tags:

google-sheets

google-sheets-query

davids

People also ask

3 Answers

JPV

Max Makhrov

Nicholas Delkeskamp

Recent Activity

Donate For Us

Google Sheets query doesn't display all values

Tags:

google-sheets

google-sheets-query

davids

People also ask

3 Answers

JPV

Max Makhrov

Nicholas Delkeskamp

Related questions

Recent Activity

Donate For Us