Google Sheet Query complaining of Column with date value having no date value - is this a bug?

0
votes

I have the following (simplified from original) formula which pulls in data from another sheet (multiple sheets in the original formula) and calculates the sum of a column based on the month() value of another column:

=QUERY({
IFERROR(IMPORTRANGE('C1'!$B$5, "PLACEMENTS!A4:L1000"), {"",TO_DATE(0),"","","","","","","","","",""})},
"SELECT SUM(Col8) WHERE Col2 IS NOT NULL AND month(Col2)+1="& MONTH($A3) &" AND Col1="""&B$2&""""& IF(NOT($C$1="ALL"), "AND Col6="""&$C$1&"""" ,)&  " label SUM(Col8) ''",0)

I keep getting the #VALUE! Error:

Unable to parse query string for Function QUERY parameter 2: Can't perform the function month on a column that is not a Date or a DateTime column

If I remove the IFERROR wrapper to the IMPORTRANGE statement then the error goes away and as you can see, in the case of an error on the IMPORTRANGE statement, an empty array with Col2 set as a date with TO_DATE(0) is inserted.

This formula without the IFERROR works fine, and pulls in rows and provides a SUM value:

=QUERY({IMPORTRANGE('C1'!$B$5, "PLACEMENTS!A4:L1000")},"SELECT SUM(Col8) WHERE Col2 IS NOT NULL AND month(Col2)+1="& MONTH($A3) &" AND Col1="""&B$2&""""& IF(NOT($C$1="ALL"), "AND Col6="""&$C$1&"""" ,)&  " label SUM(Col8) ''",0)

Here's a link to a sample sheet that the formula imports from with the same structure of data: Sample Sheet Data

If the Query works without the IFERROR wrapper, why does it produce the error about Col2 with the IFERROR wrapper in place which shouldn't be triggered in this case? This exact same formula works fine on another user's spreadsheet. Is this a Google Sheets/Query bug or is there something I'm doing wrong here? Thanks

2
mysqlsqlgoogle-sheets
Can you provide in your question the comple formula that works and the one that does not? Also providing a sample spreadsheet would help troubleshooting. – ziganotschka
@ziganotschka I've added the formula that works but unfortunately I'm not able to provide a sample spreadsheet that works as it would include financial data. Thanks – Diagonali
You can provide a dummy spreadsheet with fake values as long as it has the same formatting. – ziganotschka
@ziganotschka I've now updated the question with a link to sample data. – Diagonali

2 Answers

1
votes

IFERROR(exp1,exp2) is equivalent to IF(NOT(ISERROR(exp1)),exp1,exp2)

However, while the first option is not always compatible with queries, the second is more so.

In your case changing

IFERROR(IMPORTRANGE('C1'!$B$5, "PLACEMENTS!A4:L1000"), {"",TO_DATE(0),"","","","","","","","","",""}

to

IF(NOT(ISERROR(importrange('C1'!$B$5, "PLACEMENTS!A4:L1000"))),importrange('C1'!$B$5, "PLACEMENTS!A4:L1000"), {"",TO_DATE(0),"","","","","","","","","",""})

will solve the problem.

0
votes

You have not shared the Sheet Snapshot you're working on, and it is hard to know what is happening in your formula.

Answer Updated

IFERROR function is giving {"",TO_DATE(0),"","","","","","","","","",""} incase first parameter is false and that is the array which is generating the error.

In the Query function in Select clause, you're doing Sum(Col8) which need a number for summation, but in array, at location 8 you're giving an empty string. You just need to add zero at index 8 {"",TO_DATE(0),"","","","","",0,"","","",""}