Add an option to specify the sheet that should be used by ODK Collect while sending filled forms to Google Sheets

Ideas
,
1

Currently there is no option to specify which sheet in a spreadsheet should be used to receive filed forms, the first one on the list is always used.

Why this is a problem:

  • We can’t use one spreadsheet to receive data from different forms (maybe it’s not a good idea in case of complex forms with repeatable groups but simple forms, why not)
  • It’s confusing for our users that the target sheet must be the first on the list and I’ve solved many problems on the forum like
    Question: “I can’t send forms to GS”
    Answer: Please make sure your target sheet is the first on the list

Possible solution:
We could add two options:

  • a global option in General Settings -> Server under Fallback submission URL [sheet name]
  • a local option for each single form like we do specifying submission url:
    <submission action="https://example.com/submission" sheetName=”sheetName” method="post"/>

If not specified the forms should be sent to the first sheet like now.

I wanted to invite to the discussion people who use GS and might be interested in this feature:
@notaplatypus @seewhy

2

Hi @Grzesiek2010
Thanks for including me on this topic.

I would be wary about this option because of the repeat-groups issue. Having something that works in only one situation might lead to some kind of divergence for specific needs.

In my mind the Google Sheet is the repository and therefore is created specifically for the form, and not integrated with other elements of analysis (just my opinion). However, the data can then be linked to other sheets within Google Drive where you can add columns, calculate things etc...

Using this method you can also view any images directly by adding a column and using the formula:

=IMAGE(SUBSTITUTE('[sheetname]'![cell],"https://drive.google.com/open?id=","https://docs.google.com/uc?export=view&id="))

An alternative way of viewing data from different forms within a single spreadsheet is to use the 'importrange' function of Google Sheets

=IMPORTRANGE("[source spreadsheet]","[sheet]!A:I")

I have found this useful when sharing the data with others as it is possible to collate different forms into a single spreadsheet, and also look-up values via the PARENTKEY when using repeat-groups to make the data easier to identify. It also means that the data is 'safe'!

I think Keep It Simple (because I'm Stupid) is a fairly good motto, so being able to point to different sheets runs a risk, probably higher than the potential benefits...

Hope this helps.

3

Thanks I understand your concerns but by default it would work like now, if a user decides to specify the sheet name we could assume their understand what that means.

4

Currently if someone renamed the first sheet nothing gets broken. With an explicit sheet target failures can occur if name change aren't carefully coordinated. For example, consider an update to a form (and the Google Sheet) that changes the target sheet name, but not all enumerators are working from the latest form?

5

True because now the first sheet is used and its name doesn't matter.

That's a good point but you can do the same with submission_url defined on a form level so to me it's rather a pro. A user might want to send submissions of a newer form version to another spreadsheet using a different submission_url but also they might want to use the same spreadsheet but just different sheets.

I din't say it's a perfect feature for everyone, it's rather for small forms without repeats because sending multiple forms with repeats to the same spreadsheet would lead to a mess.
It would be just an additional option. By default it would work like now but it would allow users that are aware of the feature to do something more.