Why is this Google Sheets Query getting a parse error

0
votes

I am working on a query formula in google sheets. I am trying to get the WHERE part of the query to end at the end of the last month. I am getting a parse error at 'TEXT(EOMONTH(now(), -1),"yyyy-mm-dd")'. I know that is the portion that is failing because if I replace it with a date (2021-07-31) the query works.

Thanks in advance.

=QUERY(Transactions!A:Z, " SELECT K, -1 * SUM(E) WHERE F LIKE 'Investing' AND B > DATE '2020-05-31' AND B < DATE 'TEXT(EOMONTH(now(), -1),"yyyy-mm-dd")' GROUP BY K LABEL -1 * SUM(E) 'Investments' FORMAT K 'yyyy-MMM', -1 * SUM(E) '$#,##0.00' ")"))"

1
google-sheets
Might be easier if you posted sample sheet with only a couple rows of data – pgSystemTester
You've got several problems going on throughout that formula. As has been suggested above, it would be easier to eliminate those problems if we had access to a copy of your spreadsheet with some realistic data. – Erik Tyler

1 Answers

2
votes

There are more issues with your current formula than one. See my comment below your post. However, you can try this:

=QUERY(Transactions!A:Z, "SELECT K, -1 * SUM(E) WHERE F LIKE 'Investing' AND B > DATE '2020-05-31' AND B < DATE '"&TEXT(EOMONTH(now(), -1),"yyyy-mm-dd")&"' GROUP BY K LABEL -1 * SUM(E) 'Investments' FORMAT K 'yyyy-MMM', -1 * SUM(E) '$#,##0.00' ")

If that does not work, share a copy of your sheet.

Additionally, it's not clear to me why you're referencing A:Z when your QUERY only requires B:K. This won't affect the return; but in larger sheets, requesting only what you need can speed up processing.