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

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

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.

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.