Repeat groups and google sheets upload

Hi
Is there a way, when using Google Docs as the destination for completed forms, to upload forms with repeats upon submission? Are there any plans to introduce this feture?
Thanks in advance,
David

Hi David,

Repeats are not currently supported for Google Drive, Google Sheets upload. You can see that confirmed in the Google.org documentation.

This could likely be done in the same way that Briefcase exports form results with repeats. You would end up with the first sheet looking just like it does right now but with an extra column for a generated unique value. Then each repeat group would get its own sheet with results connected to the main sheet through the unique ID (and same idea for repeats inside repeats). It would be useful if you could try out the Briefcase functionality (more info here) and let us know whether that would work for you. If you have other ideas on what the results could look like, do share!

Another thing to keep in mind is that there is a 400,000 cell limit in Google Sheets. It would be more likely to be reached with complex forms including repeats.

All this to say, it's not easy but it is possible! Let's move this thread to Features so it can be voted on and further discussed.

Thanks for the quick reply Helene
Yes I have used both Aggrigate and Briefcase with repeats. Briefcase is
perhaps a little cumbersome, but does do the job.
I was aware that it hadn't been supported previously ... but the pace of
development currently within ODK is so impressive - it is hard to keep up!
Great to see such a vibrant community.

Given that the parent and repeat are related, it would be nice to keep them
together, perhaps on different tabs of the same spreadsheet rather than
different spreadsheets?

I am currently using Google Drive as a backend for my form submissions
together with MS Flow to move data into production databases because it is
simple to set up and maintain without any infrastructure maintence. The 2
limitations for me are repeats and large forms ( a google column limit).

Thanks again for the response.

Regards, David

1 Like

Thanks so much for your nice comments about ongoing development! Made my day. :blush:

I agree that it would be convenient to have repeats in the same document.

I've added the pr-welcome tag which means that the feature is well-defined and that code contributions (pull requests) are welcome from anyone in the community.

Please make sure you click the 'vote' button next to the topic title to express your interest. @DavidM I believe there have been other such requests sent to the list before and it would be useful to have a list of them with the original authors tagged so their ideas are also included.

1 Like

Glad to know you found a workaround.

I would be very grateful if you could share steps to achieve repeat data entry with google sheets. I am a noob in xml, so please explain in simple terms. I do not know what briefcase means in this reference.

Waiting for your reply.

Thanks in advance!

Hi Veethahavya
Briefcase is a Windows desktop tool supplied by ODK to allow you to process
forms taken from a phone or tablet and either produce csv files or process
the forms onwards to ODK aggrigate. The briefcase tool produces a csv file
for responses to a given survey, and another csv file for repeats with a
linking field to the survey entry number of a response.

Ideally a new development could produce the same output produced by
briefcase but put onto 2 tabs of a Google spreadsheet (one tab for survey
response, another tab for repeats) when submitting forms to Google sheets
rather than to Briefcase.

I hope this makes sense?

Regards,
David

2 Likes

Thanks for the explanation Mr. David!

The part about Briefcase was very easy for me to understand. However, I do not wish to use ODK aggregate and stick to google sheets integration. So, please correct me if I am wrong. To take in repeat entries, all I have to do in google sheets is create a new tab called "repeats"?

Grateful for your patient explanation.

Waiting for the clarification.

Best,
Veethahavya.

If your surveys are going to Google, then you cannot use repeats (repeats
only work with Briefcase or Aggregate).
This was a request from me to the development team to think about adding
the ability in a future release for submissions to google to allow repeats.

To be clear - it doesn't work today. Hopefully it will work in a future
release.

Regards,
David

4 Likes

Thank you Mr. David.
I really do hope they take it up as a forthcoming feature.
Have a good day.

Best,
Veethahavya.

Is it possible to work around the problem, to create a calculated field in the main form in which the repeat data are serialized then processed by a macro-like a Google sheet?

That's an interesting idea. Unfortunately, it's not currently possible because Collect does not allow forms that contain a repeat to be sent to Sheets!

1 Like

At the same time i would like to suggest the below.

As the repeat is part of the succeeding questions, there can be a logic in repeating the master data for the repeat questions if the user wishes for
the same.

i.e Lets say in my case the parent data is
Local Body Name, House No, Ward No and No. of Members and then comes the repeat data about the members i.e name, age and sex.

In this context i dont mind the parent data being replicated for each repeat row, if a provision for the same in provided while creating the form.

The scenario is explained as below

Current Scenario

Datasets in ODK Collect
LB Name Ward No House No No. of Members
Adat 13 1345 2

Repeat Members Data
Name Age Sex
A 18 Female
B 21 Male
In Google sheets it shown as

LB Name Ward House No Members Name Age Sex
Adat 13. 1345. 2 B 21 Male
I.e the last record of repeat is stored

Proposed Scenario in Google Sheets
Scenario-1
LB Name Ward House Members Name Age Sex
Adat. 13. 1345 2 A 18 Female
Adat 13 1345. 2 B. 21 Male

Scenario-2
LB Name Ward House Members Name Age Sex
Adat. 13. 1345 2 A 18 Female
B. 21 Male
Scenario-3
LB Name Ward House Members meta-id
Adat. 13. 1345 2. Uid-233444
separate sheet repeats with repeat values
Name Age Sex. meta-id
A 18 Female Uid-233444
B. 21 Male. Uid-233444

Is any of the above option be possible in ODK collect with Google drive, Google sheets

Aggregate "used' to be able to do repeats into google fusion tables. I would suggest trying that out to see if it still works. (Changes to APIs could have caused it to break, I haven't tested it recently)

Dear Yaw

In continuation of the earlier communication and going through the Features
that Google Drive and Google Form Offers, would like to recommend an
enhancement in the current facility that ODK Offers in integrating with
Google Drive.

Lets say a scenario where i have 100 entities to be mapped by 5 persons and
the data includes images to be stored in Google Drive. Currently the
provision available in ODK is to share the XML form to the 5 users and give
write privilege to the result sheet to all the five users and then once the
data is capture and submitted by the 5 users then the images are uploaded
to the Open Data Kit Folder created in individual users Google drive and
tagged using Google Photos.

Now here the end user gets control over my project data. Time being what i
have done is i shared my Open Data Kit folder to all the 5 other users and
hence all the data submitted by them is being store in my submissions
folder. But still they have access control over my folders.

Suggested Solutions
While submitting Images from Google Forms , A submission directory is
automatically created with the field name of the data in the same directory
location of the results sheets and all the collected data is stored in the
same folder. This seems a better strategy to collect data using ODK Collect
and Google Drive.

Also i suggest that Google can change their access permission levels on
Drive Contents from
Can View and Can Edit, to Can View, Can Edit, Can Write/Can Submit, which
will help safe and consistent data capture using ODK Collect and others
incorporating tools.

Thanks & Regards,Anup Krishna P,Specialist MIS,State Program Management
Unit(SPMU),Rasthriya Gram Swaraj Abhiyan (RGSA),Local Self Government
Department,Govt. of KeralaPh: 94960 46910

Hi Anup

I also use Google Drive. The obvious answer to get the permissions Granularity is to use Aggregate. I understand this is not always possible or practical.

As a work around to the same problem you could use Zapier (or MS Flow or ITTT) to simply copy each new row added to a different sheet (where only you have access). Same applies to copying new Photos.

This isn't a perfect work around, but may allow you to ensure you have moved any submitted data to a file only you have access to.

I hope this helps.

We've started work on this feature (you can track it at https://github.com/opendatakit/collect/issues/1385) and hoping to have it out in a release or two!

2 Likes

Oh man I'm so excited to see this happening. It will really improve the user experience making the data gathering flow much easier in the field, for us Google Sheets users :wink:

3 Likes

Repeats in Google Sheets is now available in beta. You can try it at ODK Collect v1.14 Beta!

4 Likes

Hi all,

i have tested the ODK Collect v1.14 Beta with google sheet on two instances they they all work perfectly for me. below are the instances i used it on

  1. single repeat group
  2. repeat group inside repeat group.

but i have a suggestion with regards to the instanceID, the instanceID for the main data is the same to that of the repeat group instanceID which is perfect. but can there be another ID which could be uniquely identifying the entries in the repeat group sheets since they are all using the main data instanceID. like if we could have something like, key and parent kay where the parent kay will serve as main instanceID and the key will be for the repeat group entries id.

example:
current situation
main instanceID
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e

repeat groupID
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e

suggestion
main instanceID
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e

repeat groupID
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e-1
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e-2
uuid:0818e2b8-427c-4693-b8b4-0b2a18d0dd6e-3

The above is an example of a 1 main data with 3 repeated entry.
thank you
fabla2020@gmail.com

Interesting suggestion, @Fabla! Can you say more about what you want to do with that? Is it because order is important? Is there a particular kind of analysis it would enable?