Repeat group with Google sheets - how to do it?

google-sheets
#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!

0 Likes

#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.

0 Likes

#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.

0 Likes

#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.

0 Likes

#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...

0 Likes

#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!

0 Likes

#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.

0 Likes

#8

Hello, etrimaille.

I have been facing this exact same problem. Would you mind explaining how you solved it?
Have you created another sheet named "form_prelevements_peche..." in the same place of this sheet?

0 Likes

#9

I'm going to investigate the issue. It would be good if you can attach your forms, it's always helpful.

1 Like

#10

Thank you.

Here is the forms I am using:
mr.xlsx (134.1 KB)

Edit: I have mistakenly uploaded the xlm file.

0 Likes

#11

Thanks for your forms, it was very helpful. I was able to track down the root problem and prepared a solution. You can expect the bug to be fixed in the upcoming v1.20.

0 Likes

#12

We've fixed this bug in ODK Collect v1.20 Beta, @prvsct. Can you give it a try?

0 Likes

#13

Thank you Grzegorz and Yaw. I couldn't imagine the fix would come so quickly.

I will gladly test the v1.20 Beta and report any trouble.

Thank you again.

0 Likes

#14

Hi Chris
An old thread so you have probably found a solution, if not ...
What I tend to do is to add calculated fields into the repeat that simply add the common data I to the repeat (therefore I don't need to link back).

So if I collect "AuditorName" and "Timestamp" on the main form and "Issues found" in the repeat group, I add calculated fields called Auditor = ${AuditorName} and FormDate =${Timestamp}

I hope this helps.

0 Likes