2

I have a query in my Google Sheet to pull student testing data from a master list based on drop-down menu options. My goal is to sort the filtered data by Column L (Completion Date) so that I can graph student (or grade, or group) progress over time. The query works perfectly to filter data based on the drop-down selections, but it does not work if I add "order by" to the query. If I remove the "IF" statements after the query that filter the data based on drop downs, the sort works.

Here is the query as currently written:

=Query('ELA Data'!A1:AY,"Select A,B,L,E,K,J,O,Q,R,AX,T,X,Y,AA,AB,AD,AE,AG,AH,AJ,AK,AM,AN,AP,AQ WHERE A is not null"&IF(A2="All Students",""," AND A = '"&A2&"' ")&IF(B2="All Grades",""," AND E = '"&B2&"' ")&IF(C2="All SPED",""," AND K = '"&C2&"' ")&IF(D2="All ELL",""," AND J = '"&D2&"' ")&IF(E2="All Academic Years",""," AND F = '"&E2&"' ")&IF(F2="All Overall Relative Placement",""," AND S = '"&F2&"' "),1)

With the "order by":

=Query('ELA Data'!A1:AY,"Select A,B,L,E,K,J,O,Q,R,AX,T,X,Y,AA,AB,AD,AE,AG,AH,AJ,AK,AM,AN,AP,AQ WHERE A is not null order by L"&IF(A2="All Students",""," AND A = '"&A2&"' ")&IF(B2="All Grades",""," AND E = '"&B2&"' ")&IF(C2="All SPED",""," AND K = '"&C2&"' ")&IF(D2="All ELL",""," AND J = '"&D2&"' ")&IF(E2="All Academic Years",""," AND F = '"&E2&"' ")&IF(F2="All Overall Relative Placement",""," AND S = '"&F2&"' "),1)

If I don't change the drop-down menus from "All xyz," the sort works. If I select a different option, I get a #VALUE! error: "Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "and" "AND "" at line 1, column 102." Here is a copy of the deidentified data so you can get an idea of what I'm working with.

Any assistance would be greatly appreciated!!!

1

1 Answer 1

1

The order by clause needs to come after all the parts of the where clause. To make your formula easier to debug, put this formula in cell A5:

="select A, B, L, E, K, J, O, Q, R, AX, T, X, Y, AA, AB, AD, AE, AG, AH, AJ, AK, AM, AN, AP, AQ where A is not null " & if(A2 = "All Students", "", " and A = '" & A2 & "' ") & if(B2 = "All Grades", "", " and E = '" & B2 & "' ") & if(C2 = "All SPED", "", " and K = '" & C2 & "' ") & if(D2 = "All ELL", "", " and J = '" & D2 & "' ") & if(E2 = "All Academic Years", "", " and F = '" & E2 & "' ") & if(F2 = "All Overall Relative Placement", "", " and S = '" & F2 & "' ") & " order by L"

Then modify the formula in cell A9 like this:

=query('ELA Data'!A1:AY, A5, 1)

See the query language help page.

1
  • Brilliant, thank you!!! I knew it would probably be something simple, but I am definitely not a programmer.
    – Rachael
    Commented Nov 28, 2022 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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