Google Sheets Select Query using Absolute reference involving month()

Question

I have a Google Sheet SELECT Query as below. I am expecting the query to retrieve a valid rows with the month part of the date matching the value in D8.

I am not getting any error but the query comes back with #N/A

=query(IMPORTRANGE("sheetID","Form responses 1!A2:H"),"select Col1,Col3,Col5,Col6,Col8 where Col3='"&$B8&"' and month(Col6)='"&D8&"'")

Can someone please help me to reconstruct the above query.

Answer 1

Unfortunatelly Google Sheets doesn't show the "correct" error when it occurs on certain functions like IMPORTRANGE when they are nested on functions like QUERY.

IMPORTRANGE require authorization to access an external spreadsheet but the authorization button isn't show when IMPORTRANGE is nested in another function.

If you didn't this yet, put your IMPORTRANGE formula on an empty cell in the following way;

=IMPORTRANGE("sheetID","Form responses 1!A2:H")

If you didn't authorized it yet, pass the mouse pointer over the cell to display the authorization button and clic on it.