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.
Unfortunately currently it's not possible to send surveys to Google Sheets without the apostrophe. We use it to prevent users from data loose.
Your topic is not the first that raises the problem.
I think we could add an option in ODK Collect that would allow sending data without the apostrophe, it should contain a description and be disabled by default to make sure only aware users use it. Would that solve your problem?
I would say that the underlying issue here is that calculates are untyped. If you could specify that your calculation should be of time dateTime, you wouldn't have this problem. We have approved a specification for typed calculates in XLSForm and I hope that we can get that support out to users soon.
In the mean time, here are a couple of ideas to get the behavior you need:
modify the XML after converting your XLSForm so that the bind with the calculate has type dateTime instead of string. In the file you shared, that would mean changing <bind calculate="format-date( /data/dateofbirth ,'%Y-%m-%d')" nodeset="/data/repeat1/dateofbirthrepeat" type="string"/> to <bind calculate="format-date( /data/dateofbirth ,'%Y-%m-%d')" nodeset="/data/repeat1/dateofbirthrepeat" type="dateTime"/>
I'd really like to avoid adding yet another setting to Collect unless we absolutely have to because it's more functionality to maintain and is one more thing for folks doing projects to think about.
