Repeat group with Google sheets - how to do it?

google-sheets

(Philip Jones) #1

Hello Forum!

I am trying to experiment with Sheets as the server, all seems to be running smoothly except for the repeat group. I gather this functionality now exists but I am not sure what I need to do to actually make it work.

I am using ODK collect v1.16.4
I create the form in Excel and then convert to XML using http://opendatakit.org/xlsform/
I have set "submission_url" in settings sheet to the editable sharing link for the target sheet in Google drive

Do I need to create a separate sheet, or tab in the existing submission sheet, to receive the data from the repeat group? And do I need to put the separate "submission URL" for this sheet somewhere in my original XLS/XML? If so - where?

As it is, the cell for the repeat group in the parent sheet just says: =HYPERLINK("null", "rmf_monitoring_form_gdrive-visit_1-rcm_plan")

Thank you!


(Dinesh Dangol) #2

No, you do not have to create separate sheet. When you download the data in .CSV file or Excel file, data from repeat group will be in different sheet but in same CSV file or Excel file.


(Grzegorz Orczykowski) #3

Everything works automatically and new sheets for (repeat groups) are created automatically as well during uploading. Just create a test form and try it out if you need.


(Chris Berens) #4

Maybe it is not the editable sharing link for the target sheet. Rather copy the url of the target sheet from the address bar (current url) at the top of yr browser (when you are at that sheet).

Good luck! This functionality is awesome.

My next learn is to find out how to join the repeats back into their parent survey to make a long version of the survey. The reason is to relate common fields to each individual in the household. I think there must be an east Sheets formula.


(Philip Jones) #5

Thanks for your replies... but - still struggling! I have a form with a single repeat group. The main form data uploads to Google Sheets just fine, but the repeat group data is nowhere to be seen.

@Grzesiek2010 - it does't create anything automatically. I kind of hoped that it would. Could it be that ODK app doesn't have sufficient permissions to modify the Google drive?

@Chris_Berens1 - I changed the link as per your suggestion. Seems to make no difference, both options work as far as general submissions go. Is your re-linking data for analysis purposes, or to enable tailoring further questions based on already given answers? I will also be looking for the same for analysis purposes. Actually, one thing I am musing for all this GDPR regulations is to put all personal data in a repeat group so it is automatically removed from the "main" survey. I would plan to turn the parent key to a QR code such that it would be very easy to link subsequent surveys to the same set of personal data - even without the analysis team having access to that personal data...


(Philip Jones) #6

Help!

I'm still having absolutely no success with this - using Google Sheets for a form with repeat groups. regular data uploads fine into the "submissions" Google sheet, but any data inside a repeat group just disappears off the face of the earth.

I get "upload success" message from ODK collect - and no error messages from ODK app, nor any odd notification from Google.

I have set "sharing" preference for the folder in which the ODK forms and submission sheets such that anyone with link "can edit".

I have tried with a new form -
xml here: https://drive.google.com/open?id=1rMUcujewnd3gV4JjkxYZkbhh53_6vuXB
xls here: https://drive.google.com/open?id=1LwuFZ00nKoNF7SLozv_YC9alUmsltTeO

I still only get this in the cell that I would expect would link to the created sheet for repeat group data:
=HYPERLINK("null", "rmf_validation-ha_1-ha_1_plot")

I have uninstalled and re-installed ODK collect (v1.16.4), and given it every permission it asks for on my phone.

I have gone to "manage linked apps" in the Google drive and see that "all ODK Android Tools and Site" are connected to drive (though the drop down "options" is blank)

Does the submissions form have to be in Google drive root?

Could the issue be that my phone is connected to 2 different Google accounts? The settings in ODK, are for only one Google account, obviously.

Is there a way to create the sub-sheets myself, and direct repeat groups to have their own submission URL to these sheets??

Or is it that repeat groups and google only work in a very specific set of circumstances, for the chosen few?

Any ideas, anyone??

Thanks!


(Etienne Trimaille) #7

I have the same problem.
Only the first sheet in the calc document is created. In the first sheet, I can see a formula like yours showing "error". You can see only one sheet in my google document.

Note that I have another form, with a single "repeat 3 times", it works perfectly in the other one.