How to adjust Google Sheets query to handle mixed data types correctly?

Other Questions Google Sheets
1

I’m using a complex Google Sheets query to pull data from an ‘INPUT’ sheet into a ‘PILOT_FORAY’ sheet. The query works fine for most data types like strings, booleans, and dates. But it’s not handling numeric values correctly when they’re copied to new cells for calculations.

For example, numbers appear correctly in the results table, but when I try to sum them in another cell, I get 0. This happens even though other data types (strings, booleans, dates) can be copied and used without issues.

I think the problem is that the query is treating all values as the most common data type in the ‘INPUT’ sheet. Is there a way to modify the query so it preserves the original data types, especially for numbers? The solution needs to work regardless of which data type is most common in the input data.

Here’s a simplified version of my query:

=INDEX(LAMBDA(x, y, {y; IFERROR(HLOOKUP(y, x, SEQUENCE(COUNTA(QUERY(x, "offset 1", )), 1, 2), ))}) (QUERY(INPUT!A1:K, "select A, max(G), H where C = '7e053706-1f2b-4397-bf80-b345671d22c10' and I = 'Pilot' group by A, H pivot D", 1), {"Instance Id", IFERROR(VLOOKUP(SEQUENCE(1, 9, 7), {TABLE_CONFIG!C2:C, TABLE_CONFIG!F2:F }, 2, )), "File Id"}))

Any suggestions on how to modify this query to fix the number handling issue would be greatly appreciated.

2

I’ve encountered similar issues with Google Sheets queries and mixed data types. The problem often stems from how Sheets interprets the data when it’s pulled through complex queries. One workaround I’ve found effective is to use the TO_TEXT function within your query to force all values to be treated as text initially, then convert them back to their appropriate types in subsequent cells.

Try modifying your query like this:

=INDEX(LAMBDA(x, y, {y; IFERROR(HLOOKUP(y, x, SEQUENCE(COUNTA(QUERY(x, “offset 1”, )), 1, 2), ))}) (QUERY(INPUT!A1:K, “select A, TO_TEXT(max(G)), TO_TEXT(H) where C = ‘7e053706-1f2b-4397-bf80-b345671d22c10’ and I = ‘Pilot’ group by A, H pivot D”, 1), {“Instance Id”, IFERROR(VLOOKUP(SEQUENCE(1, 9, 7), {TABLE_CONFIG!C2:C, TABLE_CONFIG!F2:F }, 2, )), “File Id”}))

After this, you can use functions such as VALUE() or DATEVALUE() in your calculation cells to convert the text back to the appropriate data type. This method helped me maintain data integrity across various data types in my own projects.

3

hey, i’ve had this prob too. what worked for me was using the TEXT function to convert everything to text first, then use VALUE to change numbers back. like this:

=ARRAYFORMULA(IF(ISNUMBER(VALUE(TEXT(your_query_here,“@”))),VALUE(TEXT(your_query_here,“@”)),TEXT(your_query_here,“@”)))

it’s a bit messy but it keeps the data types right. hope this helps!

4

As someone who’s wrestled with Google Sheets queries extensively, I can relate to your frustration. One trick that’s worked wonders for me is using the ArrayFormula function in combination with the VALUE function. This approach preserves data types while allowing for flexible handling of mixed data.

Try wrapping your entire query in an ArrayFormula, then use VALUE to convert numeric strings back to numbers:

=ArrayFormula(VALUE(INDEX(LAMBDA(x, y, {y; IFERROR(HLOOKUP(y, x, SEQUENCE(COUNTA(QUERY(x, “offset 1”, )), 1, 2), ))}) (QUERY(INPUT!A1:K, “select A, max(G), H where C = ‘7e053706-1f2b-4397-bf80-b345671d22c10’ and I = ‘Pilot’ group by A, H pivot D”, 1), {“Instance Id”, IFERROR(VLOOKUP(SEQUENCE(1, 9, 7), {TABLE_CONFIG!C2:C, TABLE_CONFIG!F2:F }, 2, )), “File Id”})))

This method has consistently solved mixed data type issues for me, ensuring numbers remain numbers for calculations while leaving other data types intact. Give it a shot and see if it resolves your problem!