I am trying to write a Google Sheets Query where i have added condition that is IF Importrange Col"G" has Date then replace date to "Yes" other wise "No".

But it gives an error, if i removed the condition then query works very well.

looking forward to your help.

from Query *Selection Col7 is the Column which dates i want to replace.

=SORT(QUERY(IMPORTRANGE(Links!A4,"DATA!A3:AW1000",IF(Links!G4:G1000=DATE(), "Yes", "NO"), "Select Col9, Col7, Col10, Col11, Col12, Col13, Col47, Col48, Col49 Where Col47 is not null", 0),1,TRUE))

Sheet Link: I have created an example what i have been looking for:

https://docs.google.com/spreadsheets/d/1umBqoiUljnCWCRnCzAwHZ9NnD_wmgkZZcwwb83Jtsvk/edit?usp=sharing

CodePudding user response：

You can use array pooling in the query source to accomplish this task. The first array is formed with IMPORTRANGE(Links!A4, "DATA!A3:AW1000") and the second array with the following formula IF(ISDATE_STRICT(IMPORTRANGE(Links!C4, "DATA!G3:G1000")), "Yes", "No"). The two arrays are then combined into one using ARRAYFORMULA({...array1..., ...array2...}).

The final formula will look like this: =ARRAYFORMULA(SORT(QUERY({IMPORTRANGE(Links!C4,"DATA!A3:AW1000"),IF(ISDATE_STRICT(IMPORTRANGE(Links!C4,"DATA!G3:G1000")),"Yes","No")}, "Select Col9, Col50, Col10, Col11, Col12, Col13, Col47, Col48, Col49 Where Col47 is not null", 0),1,TRUE))

But there is a disadvantage with this variant is that IMPORTRANGE is used twice in the formula. This slows down the speed of the table