1. What is the problem? Be very detailed.
Google sheets query function does not pull dates that has a leading apostrophe. The standard date type question does not have a leading apostrophe but the calculate type question has the leading apostrophe. I am using the calculate type question to store the date previously entered by the user but every new submission through odk collect to google sheets causes the apostrophe to still appear even though the column is formatted as ,"yyyy-mm-dd" through google sheets before submission. Therefore the query function works fine with the standard date type questions but not with a calculate type question. It works in the scenario, I format the date in the column after the submission or simply just delete the apostrophe after submission but I do not want to have to do this. Is there a way to prevent the calculate question from adding the leading apostrophe to the formatted date when submitted through odk collect? I used the calculate type question since it is a hidden question to link the submission sheet to the repeat sheet.
Query function: =query(importrange("XXXXX"),"select * where Col1 matches date '"&TEXT(B1,"yyyy-mm-dd")&"' ",1)
2. What app or server are you using and on what device and operating system? Include version numbers.
ODK Collect v1.25.2
3. What you have you tried to fix the problem?
- Formatted the google sheets column that has the calculated formatted date before submission.
- Tried double quotes instead of single quotes in the query function.
4. What steps can we take to reproduce the problem?
-Output of a calculate type question.
5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.
DateTest.xlsx (21.7 KB)
DateTest.xml (1.1 KB)