1

I'm not sure what is happening in my Google Sheet query and could really use some help. I have a quick screencast that demonstrates and explains the issue.

Basically, I'm using a query to pull data from a Google Spreadsheet that is populated by a Google Form. If the form submits only numbers (100), there is no problem. However, if someone submits a string ($100 or 100 dollars), the query will not display the string. It will only display form submissions that are 100% numbers.

Also, if the 1st submission is a string (not 100% numbers) the query will work.

Here is my query

=query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, CI, BZ, CA, BA  where J = 'Industry Certification'",false)

Any ideas on why this is happening?

Also, I'm aware that I can limit the form to numbers only. I'm trying to avoid that and learn 'why' this is happening.

3

A good place to start is documentation:

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.

In other words, if you have a bunch of numbers and some text in the same column, the text will be ignored.

If you allow mixed data types within a column, query is not for you. You may be able to achieve the desired result with filter. The following example with filter is equivalent to the query in the question:

=filter({data!B2:J, data!CI2:CI, data!BZ2:BZ, data!CA2:CA, data!BA2:BA},  data!J2:J = "Industry Certification") 

The first argument is an array of data to be filtered (with { } used to combine non-adjacent columns). The second is an array of True/False values to be used as a filter. Multiple conditions can be used.

1
  • combo of TO_TEXT, ARRAYFORMULA and Col references should solve it:

=ARRAYFORMULA(QUERY(TO_TEXT(data!B2:CL),
 "select Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col87, Col78, Col79, Col53 
  where Col10 = 'Industry Certification'", 0))

  • @Jarle this is how to do it – user0 Jun 7 at 10:24
0

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. (this is true) but... I solved this problem like this. If my data is in the range "A1:E100" my query is going to be ArrayFormula(TRIM(MAYUSC(QUERY(A1:E100;"select *")))) ---> whit this formula, if in any column we have mix data the problem will continue so we have to try something like this:

=QUERY(ArrayFormula(TRIM(MAYUSC({A1:E100})));"SELECT *").

protected by Community Jun 7 at 19:00

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.