Google sheet query renders numbers as text-CodePudding

I am trying to query multiple sheet importrange

=query({IMPORTRANGE("sheet url", "Student List!E4:T"); IMPORTRANGE("sheet url", "Student List!E4:T")},"select * where Col7 is not null",0)

Issue is query rendering a column 10(say column T) which consist of text and numbers as text only.

i am using below formula to further filter based on numeric value but doest work because of previous query importrange issue

=FILTER('Sheet'!C3:Q,'Sheet'!L3:L <=0)

CodePudding user response：

You can do it with the help of FILTER and INDEX, "getting rid" of QUERY:

=FILTER({IMPORTRANGE("sheet url", "Student List!E4:T"); IMPORTRANGE("sheet url", "Student List!E4:T")},INDEX({IMPORTRANGE("sheet url", "Student List!E4:T"); IMPORTRANGE("sheet url", "Student List!E4:T")},,7)<>"")

Or, the same written with LAMBDA:

=LAMBDA(range,FILTER(range,INDEX(range,,7)<>""))
({IMPORTRANGE("sheet url", "Student List!E4:T"); IMPORTRANGE("sheet url", "Student List!E4:T")})

CodePudding user response：

you can run lambda on it:

=ARRAYFORMULA(LAMBDA(x, IFERROR(x*1, x))(
 QUERY({IMPORTRANGE("sheet url", "Student List!E4:T"); 
        IMPORTRANGE("sheet url", "Student List!E4:T")},
 "where Col7 is not null", 0)))