Problem with google sheets query() - Date literals should be of form yyyy-MM-dd

How To
1

I am facing an issue with google Sheets (advanced search, search rows (advanced)) and query().

I have the following query. When testing it on google sheets, it works like a charm.
SELECT A WHERE A >= date '"&TEXT((TODAY()+7),"YYYY-MM-DD")&"' AND A < date '"&TEXT((TODAY()+14),"YYYY-MM-DD")&"' AND C = '' LIMIT 5

When executing on make, i get the following error:

RuntimeError

Invalid query: Invalid date literal [“&TEXT((TODAY()+7),“YYYY-MM-DD”)&”]. Date literals should be of form yyyy-MM-dd.

Of course I tried to just use yyyy-MM-dd , but same result.
I search for 30 minutes now but can’t find a working solution.

What am I doing wrong? Thanks

2

I’m definitely no expert, but could it be the use of ’ that causes the problem? I know ’ and " are often interpreted differently. I don’t know enough about Make text manipulation yet to know if this is the problem, though.

L

3

Could be, yes. But this is how it is explained everywhere - and if i use it in a google sheet directly, it works. And wrong defined ’ or " should throw another error.

4

no other idea / solutions? Would be amazing to find something. Thanks!

5

Couldn’t find a solution using googles TODAY() function.
Had to adapt the code and in the end i used the formateDate() and parseDate() function of make.com.
And voila, it works.

2 Likes