ARRAY_LITERAL Error using 3 QUERY with month and year

Question

I'm trying to use a google sheet formula to import data from 3 differente sheets using query with month and year. I used to use the query only with month and it works fine...I add "...and year(Col2)=2022" and now doesn't work.

The old code working was:

=SORT({QUERY(IMPORTRANGE("LINK";"Sheet1!A:P");"SELECT Col2,Col5,Col6,Col16,Col12 WHERE month(Col2)+1=12";0);SI.ERROR(QUERY(IMPORTRANGE("LINK";"Sheet2!A:P");"SELECT Col1,Col4,Col5,Col14,Col13 WHERE month(Col1)+1=12";0);SPLIT(REPETIR(" |";5);"|"));SI.ERROR(QUERY(IMPORTRANGE("LINK";"Sheet3!A:I");"SELECT Col1,Col4,Col5,Col9,Col8 WHERE month(Col1)+1=12";0);SPLIT(REPETIR(" |";5);"|"))};1;VERDADERO)

The new one is this (I just added: and year(Col2)=2022 in every query and I have a ARRAY_LITERAL error:

=SORT({QUERY(IMPORTRANGE("LINK";"Sheet1!A:P");"SELECT Col2,Col5,Col6,Col16,Col12 WHERE month(Col2)+1=12 and year(Col2)=2022";0);SI.ERROR(QUERY(IMPORTRANGE("LINK";"Sheet2!A:P");"SELECT Col1,Col4,Col5,Col14,Col13 WHERE month(Col1)+1=12 and year(Col1)=2022";0);SPLIT(REPETIR(" |";5);"|"));SI.ERROR(QUERY(IMPORTRANGE("LINK";"Sheet3!A:I");"SELECT Col1,Col4,Col5,Col9,Col8 WHERE month(Col1)+1=12 and year(Col1)=2022";0);SPLIT(REPETIR(" |";5);"|"))};1;VERDADERO)

Any solution?

Regards!!!

score 2 · Accepted Answer · answered Oct 26 '22 at 22:31

try:

=SORT({IFERROR(QUERY(IMPORTRANGE("LINK", "Sheet1!A:P"),
 "select Col2,Col5,Col6,Col16,Col12 
  where month(Col2)+1=12 
    and year(Col2)=2022", 0), SPLIT(REPT(" |", 5), "|"));
 IFERROR(QUERY(IMPORTRANGE("LINK", "Sheet2!A:P"),
 "select Col1,Col4,Col5,Col14,Col13 
  where month(Col1)+1=12 
    and year(Col1)=2022", 0), SPLIT(REPT(" |", 5), "|"));
 IFERROR(QUERY(IMPORTRANGE("LINK", "Sheet3!A:P"),
 "select Col1,Col4,Col5,Col9,Col8   
  where month(Col1)+1=12 
    and year(Col1)=2022", 0), SPLIT(REPT(" |", 5), "|"))}, 1, 1)

ARRAY_LITERAL Error using 3 QUERY with month and year

1 Answers1