Integers and dates sent by ODK Collect to Google Sheets as text

Support
,
1

1. What is the problem? Be very detailed.
When sending data to Google Sheets each column arrives as text - so dates and numbers have the format '09/10/2019 and '100 respectively. I know that this technique is used to fool Excel into treating numbers as text, but I wonder why it should be the case here when my ODK Collect question-types are specifically dates and integers.

Here are the relevant lines from the xml made generic with [placeholders] and without the <> :

bind jr:preload="date" jr:preloadParams="today" nodeset="/[form]/today" type="date"
bind nodeset="/[form]/[group]/[question]" required="true()" type="int"

This means it breaks any calculations that are linked to those columns, unless I write a formula to strip the apostrophe before evaluating...

2. What app or server are you using and on what device and operating system? Include version numbers.
ODK Collect 1.23.3, Google Drive

3. What you have you tried to fix the problem?
Checked that I set the question types correctly

4. What steps can we take to reproduce the problem?

You could try using the above xml in a form and upload to Google sheets. I have found this to be the case on different forms so I don't think it is just me being stupid (for a change).

5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.

2

As far as I know, there is no way to tell Google Sheets the types of individual values. That is, you can either tell Google Sheets to infer type based on what the data looks like or send it raw. Could you please confirm, @Grzesiek2010?

Initially the type was inferred by Google Sheets but this caused data loss in certain cases, especially with select multiples (see Strange output from "select_multiple" questions).

The apostrophe is a special character recognized by most spreadsheet programs including Google Sheets as indicating that the value is raw. It should still be possible to do calculations with those values.

I tried putting '09/10/2019 in cell A1 and was able to confirm that =weekday(A1) in another cell works as intended.

What calculation is failing?

3

Thanks @LN for the explanation - I have come across problems with spreadsheets misinterpreting data types (I think Excel bases its 'choice' on the first 10 records?). Sounds like avoiding the risk of data loss is far more important than my inconvenience - especially now I know it is a deliberate 'feature'.

I am using a Lookup and it gives #N/A if the value in the lookup table value is an integer and the ODK imported data is raw (i.e. with the apostrophe).

The reason for this is that the data is being collected at predefined pegs that are numbered (I have a GPS location from the survey set up) so to save the enumerators having to enable GPS, I want to link the peg number to the initial survey, to be able to produce a google map (coordinates are the result of the lookup). There are too many pegs to easily use a select_one to link through to UUID from the original survey. I know the risks of them potentially not being in the right place etc, but I am trusting them to be able to read a peg and type a number. Anyway that's not an ODK issue!

So my work around would be:

=lookup ( int ( A2 ), [search range], [result range] )

(assuming that the ODK raw value is in column A, and the [search range] values are integers)

which I've tested and works.

Thanks for your help.

2 Likes
4

@seewhy I don't always get a chance to respond to your posts but I wanted to take a second to say that I really appreciate the usage context that you provide to explain what you're trying to do and how you're approaching it. Even if it's not explicitly "an ODK issue," it's really helpful for trying to design documentation and features that make all users' lives easier. I refer to your posts often (even if I haven't explicitly acknowledged them!). So please keep it up when you can. :blush:

I'm glad that in this case there is a relatively easy fix. :dancing_women:

1 Like
5

Hi @LN
Thanks for your post (sorry it's taken me a while to respond) - I'm glad that my 'wittering' is sometimes useful. I work on the basis that if someone can understand why I want to do something, I might have a better chance of success in finding a solution, rather than just 'can this be done' or 'this doesn't work'. It's also good to know that context can be helpful, and if it helps identify some scenarios where particular features might have a use, that's great.

Sometimes I find my own answers just by writing questions on this forum - there have been a few occasions where I've drafted a question and then re-read it, searched my brain and the forum a bit harder once I've framed the problem more clearly, and worked out how to do it - so it does help me too. I'm often too embarrassed to actually post them!

Thanks for all your help and support, its greatly appreciated - that's to the whole 'team' out there!
CY.

1 Like