ODK Collect to specific sheet in Google Sheet

1. What is the problem? Be very detailed.
I have a Google Sheets file with 5 sheets. I want to be able to submit data to a specific sheet, and I am willing to use one response sheet per ODK form (i.e. Form 1 submits to Sheet 1, Form 2 submits to Sheet 2, etc. But Sheets 1-5 exist within the same Google Sheet). However, the shared link does not respect the gid number that Google uses to identify the sheets. Furthermore, the ODK form only uploads data to the left-most sheet in my workbook. So if I rearrange Sheet 3 to be on the left of Sheet 1, the next submission will appear on Sheet 3.

I have tried these URL syntax:
https://docs.google.com/spreadsheets/d/[sheetKey]/edit?#gid=1845755943
https://docs.google.com/spreadsheets/d/[sheetKey]/edit?usp=sharing#gid=1845755943
https://docs.google.com/spreadsheets/d/[sheetKey]/#gid=1845755943

Am I missing something? Or is it not possible to specify the sheet to which I want my data uploaded?

2. What app or server are you using and on what device and operating system? Include version numbers.
XLSForm on ODK Collect, on Google Pixel 2. Sent forms go to Google Drive.

3. What you have you tried to fix the problem?
Experimented with different sheet gids, Google Sheets URL syntax, rearranging sheet order.

4. What steps can we take to reproduce the problem?
Create an XLSForm and a Google Sheet with multiple sheets. Use #gid=[01234567yoursheetIDhere] in the submission URL that points to a specific sheet within your Google Sheet. Send the form and observe the results.

5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.
It's important to my project that the sheets all "live" in the same file, as other non-techy people will want to access the raw data and possibly make corrections or input directly into the sheet. The next best option would be to create a separate Google Sheet document per ODK form, but that is less ideal as it is not very streamlined. I figure since Google Sheet already generates a unique gid for each individual sheet, that this should not be too difficult to do with ODK Collect submissions?

Thanks!

I put together a similar setup for myself and ran into the same problem. Just to verify when you said "Use x in the submission URL" I'm guessing you mean in the submission URL of the forms themselves (rather than using the fallback URL option in Collect's setting)?

I'm wondering if what you've referred to as the "sheetKey" is what's being used for submissions and the gid being ignored. I'm going to have a look through the code and see if that's the case.

P.S. awesome username!

Forms are always sent to the first sheet in the given spreadsheet. Adding an option to specify sheet name you want to use would be a good option I think but we need to discuss it.
@LN what do you think?

1 Like

TLDR: It doesn't look to me like that there is a fix better than the work around you suggested without changes to the Collect app.

Looking at the code (mainly in InstanceGoogleSheetsUploader it looks like we grab what Google refers to as the "Spreadsheet ID" (these docs were quite helpful in understanding this) and then use that to "append" to the Spreadsheet. I'm guessing the way Google handles this (as @Grzesiek2010 is saying) is to just append to the first "Sheet" inside the "Spreadsheet".

Interestingly from having a quick look around the API/SDK docs for Google Sheets (as I was there anyway) I couldn't find a way to append to a Sheet within a Spreadsheet as the "append" action (documented here) seem to deal in Spreadsheet IDs. I've not really played with this API before so could be totally wrong but I've gone ahead and asked a question on Stack Overflow anyway.

It's possible. We already use many sheets in repeat groups which have their own sheets and we insert rows there by there names as i remember.

1 Like

Thanks for all the responses. By [sheetKey] I mean the long alphanumeric string which @seadowg says Google calls the Spreadsheet ID. But there is a unique Sheet ID that is generated for each sheet within a spreadsheet, noted in the URL as a "gid". And yup, I'm entering the Google Sheet URL into the Submission URL field of the Settings page in the XLSForm. I don't have anything entered in the fallback URL.

For now, my own use case has changed as we've created a workaround by creating just one survey with a question early on that asks something like "what data do you want to enter?", and then using form logic to branch out to different sets of survey questions based on the selection. As such, all data are sent together to one sheet and we will enforce minimal manual editing directly in Google Sheets (so the interface/cleanliness of the data sheet(s) is now less important). Each survey instance will also contain many blank cells because of this skipping, but it will be fine for our purposes. The data sheet will be connected to a dashboard which will be the main interface of the user with the data.

But nonetheless, if the gid could be used in the future in the submission URL to target a specific sheet within a Google Sheet, it would be a great feature to have! And perhaps make the data sheet a bit more accessible to "medium-techy" people who want to look at the raw data.

3 Likes

Ah that sounds like a great solve @notaplatypus. I'd vote we close this out if that's ok with you?