How to sort Google Sheets query result by column values (month names), numerically and within the query statement itself?

Question

I have a formula which aggregates IMPORTRANGE data from external sheets and then combines the output of multiple queries, which works fairly well:

={IFERROR(QUERY({
IFERROR(IMPORTRANGE('C1'!C3, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C4, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C5, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C6, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C7, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C8, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C9, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C10, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C11, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C12, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C13, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C14, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C15, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C16, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C17, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C18, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C19, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C20, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C21, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C22, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C23, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C24, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C25, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C26, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C27, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C28, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C29, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C30, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""});
IFERROR(IMPORTRANGE('C1'!C31, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""})},
"SELECT * WHERE Col8 IS NOT NULL AND Col1='PERSONAL' OR Col1='RESOURCER' OR Col1='NEW' ORDER BY Col12 desc", 0),{"","","","","","","","","","","",""});
IFERROR(QUERY({IFERROR(IMPORTRANGE(MANAGERSHEET, "PLACEMENTS!A4:L200"), {"","","","","","","","","","","",""})},
"SELECT * WHERE Col8 IS NOT NULL AND Col1='PERSONAL' OR Col1='RESOURCER' ORDER BY Col12 desc", 0),{"","","","","","","","","","","",""})}

Col3 in this query imports as a string value representing the month ("January", "February", "March", etc). I'm thinking also of explicitly selecting the Columns one by one (SELECT Col1, Col2, Col3, etc) for more flexibility.

I'm trying to figure out if it's possible within the query statement to convert (or interpret) this month value string so that I can sort by month numerically. i.e. January = 1, February = 2, etc.

I've tried looking through the official Google Query Language Reference (Version 0.7) and none of the scalar functions seem to be able to achieve what I'm trying to do or at least not in any of the ways I've tried. Googling the question hasn't found any pointers either, with most results relating to number to month string conversions or else suggesting some Google Sheets native functions that can't be used within the Query itself.

Is this possible within the Google Query Language in Google Sheets without having to resort to adding columns separately?

Can the month name be converted to a numerical value during the SELECT procedure or can it be interpreted as a number with the ORDER clause?

player0 player0 · Accepted Answer · 2019-11-28T10:51:35

if your months would be valid dates then you can do:

=QUERY(A1:A5, "order by month(A)", 0)

otherwise, you will need to convert it:

=ARRAYFORMULA(QUERY({A1:A5, MONTH(A1:A5&1)}, "select Col1 order by Col2"))

to convert month name to a number:

=MONTH("August"&1)

How to sort Google Sheets query result by column values (month names), numerically and within the query statement itself?

1 Answers