1. What is the general goal of the feature?
To make it possible to integrate data pulled through Briefcase and downloaded from Google Sheets by having the same columns on a Google Sheet as in an exported csv file. This would allow simple integration of data stored in different places from the same form definition. It also makes it easier to import into GIS (e.g. QGIS) which can use an X field and a X field to define a coordinate (delimited text file).
Currently data sent from Collect to Google Sheets is stored in the following three-column format:
Location | Location-altitude | Location-accuracy
[lat],[long] | [altitude] | [accuracy]
Briefcase exports data in the following four-column format:
Location-Latitude | Location-Longitude | Location-Altitude | Location-Accuracy
[lat] | [long] | [altitude] | [accuracy]
So currently, in order to create a csv file that I can import to my GIS, I need to download the sheet from Google drive, add two columns, and put a formula in each:
=LEFT(A2,FIND(",",A2)-1) [to get the latitude, assuming column A has the Location field]
=MID(A2,FIND(",",A2)+1,20) [to get the longitude]
Then copy and overwrite the cells (paste values only), then delete the Location column. Then save as csv format.
If the data were to be stored in Google Sheets in the four-column format, it would be consistent with the rest of the ODK suite, making it easier to use different tools to manage the same form. With ongoing data collection it is time-consuming to update the GIS compared with if all data is pulled through Briefcase.
This might only need to be an option - some folk will want to use the [lat],[long] format to create Google Maps based on the spreadsheet, so it would be unhelpful to lose that simplicity for those cases.
2. What are some example use cases for this feature?
Anyone who wants to use Google Sheets as a simple way of collating data from more than one enumerator, but then needs to analyse the data using a GIS would probably find it easier to have an X field and a Y field rather than a single [X,Y] field?
Also, as suggested above, if you have times where data is stored / pulled through different tools, it is hard to merge the data. As I've said on other posts to illustrate the issue:
Often I am doing fieldwork in areas where I have no internet connection on my field device and I need to download that data daily to a laptop, so I use Briefcase for that (I might be 3 or 4 days on site sometimes with hundreds of data points, so it's risky to leave the data on a single device). Or a colleague does the same. And so I often end up with two or more incompatible datasets from a single form... Not very efficient.
3. What can you contribute to making this feature a reality?
Happy to help in a non-coding capacity to find a workable solution.
Issues that have already arisen dealing with other potential Google Sheets datasets need to be resolved:
Changing the way the data is sent to Google Sheets potentially breaks existing datasets, so it needs to be an option that defaults to the current format. This then means that people changing the option need to adapt any existing data (possibly by using my formulae) before uploading the new form definition / changing server settings. Or just add a new 'first sheet' for the newly collected data.
