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.
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
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
single repeat group
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
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?
I think I know what @Fabla means. Your problem is that having nested repeatable groups the main one has instanceId column which is ok but the child one also has only that column so you can't be sure which main repeat it refers to. Am I right?
I think the instanceID column on the repeat tab refers to the PARENT Instance ID. The repeat rows do not have a unique ID of their own.
An easy work around way to do this (assuming this is to give a unique ID per repeat instance that is still tied to the parent?) would be to:
-Add a couple of columns at the end of your repeat tab.
-In the first column use the ROW formula to get the spreadsheet row number,
-In the second column use CONCATENATE to add the instance ID, a "-" and the previous column value (the row number).
This will give you unique references for all repeat instances; if this is what you are trying to achieve? You could either do this manually from time to time or script it within your spreadsheet to put the formulas in where parent IDs are populated (so as not to break the sheet for future submissions).
In some cases, it's possible to fix the problem by building an appropriate form let's assume we have a form for collecting countries and their states, so we have two nested groups. One group is for countries and one for states and you can inject selected county to the second group treating that column as an ID. But generally, I agree it would be good to another column for each repeat group which contains another repeatable group inside.
thanks everyone for your response, you may released that each repeated question will have a responses, all that i was thinking was that, if for each response a unique id is created for it which can be identify. giving the same instanceid to the same repeated group responses will not be clear if you want to deal with one of the repeated response. that is why i suggested this. i know it can be done by creating addition column in the xlsform or xform with concatenate, but how many people can do that to create a unique id for the repeated groups.
Generally, I agree but I think it's not as important to work on this now. We are going to release v1.14.0 this week so adding that in the next release sounds good to me.
Thanks for the good discussion about unique repeat identifiers, everyone. I think it would be ideal to match the Briefcase behavior. Briefcase specifies KEY and PARENT_KEY columns for each repeat. the KEY is the uuid of the instance followed by the XPath path of the node. The PARENT_KEY is the parent's KEY.
I think this is important enough that it should go with the initial release of the feature. Otherwise users who start using it will have partially-filled key columns added later which will be confusing.
Ok, my one additional idea here is that since we are going to create keys for that groups (adding columns KEY and PARENT_KEY), we can do the same for the main level itself - I mean using that instead of instanceID because now if the instance doesn't have instanceID it can't be uploaded what is a limitation. Do you agree?
Great idea, @Grzesiek2010, it will be good to remove another limitation. To summarize: for the top level record, KEY will be a duplicate of the instanceID column if it exists or a UUID that Collect generates at upload time if instanceID doesn't exist.
Perhaps you just save the UUID used as the instanceID? Otherwise when people use other things like Aggregate or briefcase you can end up with duplicates?
Generating a uuid would only happen in the case where the form definition doesn't include instanceID. Then the KEY of the row in the Google Sheets top-level sheet would correspond to that uuid.
@W_Brunette are you suggesting that Collect should alter the form definition to add instanceID? I think that would be required to also add it to the records.
How about we make the documentation state that forms without an instanceID are supported with Google Sheets upload but that if no instanceID is specified it may not be possible to detect duplicates there or submitted to other servers? I think it's going to be very rare anyway because a user would have to manually generate or alter XML to end up without an instanceID.
@LN, documentation is a fine solution. This was a problem we had years ago it was kind of solved by most form generators just adding instanceID automatically for most users. We took extra precautions in briefcase and aggregate. I am just trying to be helpful and bring up problems of history past.
