Form+Media updates from central even when submission account is Google: Separate Server and Account Settings in Collect

Features
#1

In reference to an existing feature in recent past: This change may have affected the feature/flexibility. https://github.com/getodk/collect/commit/640ca84dc88cb0cb005267784912eda658b44b0c

1. What is the general goal of the feature?
Re-Allow form and/or media updates if the form was sourced from Central/Aggregate even if current submission account is a Google Account/Drive in Collect.
One way is to re-allow the updates in collect as they used to be prior to Aug 2020.
An alternate could be to update forms+media based on source server of each form and not the currently selected Server/Account in collect. Furthermore, let the user decide which update mode to use: Either based on each form or Server in general settings.
Another could be to separate FormServer from Submission Account allowing Collect User flexibility to point to separate server for forms (e.g. Central or Aggregate) while submitting data via Google Account to Google Drive or other Server. This may decouple forms server from submission account and therefore Google Account may not intersect with Form/Media update settings. This ensures the forms/media continue to receive updates from Central or Aggregate without the User having to switch between servers for the sole purpose of getting form updates. This is a major new hassle and point of confusion and makes the automatic form updates not very flexible.

2. What are some example use cases for this feature?

  1. CRUD operations on submitted data until this feature (partially) becomes available in Central
    User may host forms in Central or Aggregate while submitting data to Google Drive for ease of viewing, editing, updating received data. This will be essential until Central offers facility to Accept/Reject/Update/editing/sharing incoming data via web forms.

  2. Use google app script or other 3rd party analysis tools in the cloud
    Allows admins to use a vast array of Google App Scripts for minor workflows thus complementing the existing feature set offered by ODK Products. This also allows for diverse use cases be implemented with very little effort on the User side e.g. mail, notifications, workflows etc. While ODK may not support this feature explicitly - this flexibility may not be very complex from an adoption perspective. This used to be an unsaid/implicit feature in Collect/Aggregate and was expected to be carried forward. I would really appreciate if this can be re-instated.

Inline with current Collect feature where different forms are allowed to contain submission URLs pointing to multiple targets - the form update should be tied to the source server of the form and not just the currently selected server. Form Updates should still be allowed for forms that were sourced from Central even if the Submission Server account is Google based. Please note Form updates in collect that were sourced from Aggregate used to update just fine and were an expected feature in central from a user's perspective. The current workaround is to switch back and forth between Servers and that is a hassle.

3. What can you contribute to making this feature a reality?
I can enthusiastically advise and test.

Reference to an existing issue

Form media updates in Collect with submissions in Central and Google Drive Sheets
Form media updates in Collect with submissions in Central and Google Drive Sheets
Form media updates in Collect with submissions in Central and Google Drive Sheets
#2

It seems like the key ask here is being able to have forms submit to a different place than where they were retrieved from. Personally that's a new idea to me so just to clarify your needs here:

  1. Why do you need to submit filled forms to Google Drive rather than Central?
  2. Why do you need to store blank forms in Central rather than Google Drive?

I'm sure your answers will repeat some of the things you mention in your post but interested to drill down to exactly what your motivations are for this setup.

#3

Fair ask.

Why do you need to submit filled forms to Google Drive rather than Central?
A: Central is very helpful with user/project and form update features. Moreso from a end-users' perspective the automatic updates feature makes getting media updates very easy for end user. I've a 3rd party server updating media via REST calls to Central.

Google drive allows for data to be edited/monitored and whole rows to be deleted in case of errors in records. It also allows me to run queries and notification(mail,Calendar etc) operations with ease. Also - most users have Google accounts and it's easy for field workers to get access to Google Forms. It's much too easy to extract meaningful data from google sheets (CSVs) to update media in Central via APIs.

Why do you need to store blank forms in Central rather than Google Drive?
Blank Forms keeps my Central instance light and snappy and I manage data via Google Sheets. Media and other uploads are done to Central that get downloaded to field data collectors' devices automatically without their conscious involvement.

I'm sure i'm missing few more advantages. It's a small but very useful integrated workflow pipeline that updates every 15 minutes. Very very helpful. I'm willing to put all my code and stuff in "showcase".

#4

Central already allows hosting forms that submit to Google Sheets. Collect supports the same.

It's just the form update that ceased in collect few releases ago and as I upgraded to central recently - this became evident right away.

#5

It sounds like you're using a lot of the features of both Central and Google Drive!

Right now Collect can only really speak to one location to fetch and submit forms - you're right about being able to use auto update using the last configured Open Rosa server (Central, Aggregate, Kobo etc) and submit to Google Drive before, but I don't think this was an intentional feature of Collect and could potentially cause a lot of confusing situations. Personally I don't think it makes sense to reintroduce that behaviour (but others might disagree).

One thing that might make sense in the future would be to add a way to configure separate fetch and submit locations, so you could have Central set up as where to get forms and Google set up as the place to submit them. This would be a pretty big change however.

In the meantime, as a workaround, is there anyway you could use the Central OData and Google APIs to integrate with the Google services (you mentioned mail and calendar)?

#6

I've been using these features for couple of years now. I agree that in long term separating the form and submission settings will make solve this. I am a strong advocate for keeping with the current arrangement in Central.
I considered/ tried ODATA but was unsuccessful as its very expensive on data and media front and requires much longer processing times that exhaust quotas.
My request is that we find a way to re-allow form updates based on source of form until we wait for central data approval and hopefully separate form server and submission account for previously downloaded forms.
I suggest to use this as a quick win & stop-gap measure until CRUD features become available in central.

#7

I really appreciate all the great work ODK team has been putting in over the years offering flexibility and open source solutions over long years. I think this flexibility is secret gem that can be retained to offer flexibility that does not stray far from currently capability of Collect/Central. This offers a chance for mature users to grow in the community exploring some interesting frontiers.

#8

I suggest to use this as a quick win & stop-gap measure until CRUD features become available in central.

Central might actually beat us to it here. The next release looks like it will include accept/reject and edit.

I think for the moment the best option is to use an older version of Collect. These can be downloaded here: https://github.com/getodk/collect/releases

#9

out of curiosity - which was the latest Collect version that still supported updates for each form from its source?

#10

out of curiosity - which was the latest Collect version that still supported updates for each form from its source?

I think it would most likely be v1.27.4. As that behaviour isn't tested it's hard to verify, but I think the changes were made as part of introducing the Exactly Match Server features in v1.28.0.

1 Like
#11

The central feature has been on horizon for a very long time and still I'm not sure how much functionality it will offer. Would love to vote up the current ask on the collect front.
Another issue(s) will be future proofing all the forms as there've been several changes as of late.

#12

@seadowg You're correct however submission approval is one sub-section of only one of the use cases in this request. I can demo/showcase the same as well.
Taking a broader approach of the potential of re-allowing updates using either Server and Submission account separation or re-enabling updates for "previously downloaded forms" menu-selection will result in allowing(similar to how it used to be) the broad range of flexibility (which is very inviting feature in terms of mature user adoption) to enable other use cases I've mentioned above (mail/notifications/workflows/queries and anything else google offers).
This unsaid/implicit flexibility only adds to the Collect's versatility and offers a range of capabilities for the open source minded community with very little effort on ODK consumer side. Who can help promote some input feedback on this? Is there a way to request feedback on this from forums/advisory members proactively. I can help to put some time into this. Also can demo my use in a meeting and also share my scripts for use by ODK user community freely.

#13

ODK has a governance system documented here. New features to Collect or other tools are usually discussed by the TAB and you can ask them to include topics (that's mentioned here).

Personally, I think it would be a good idea to try to work around this at the moment (by building some connector between Central and GSheets) so you're not stuck on an old version of Collect. If you're having problems with that I'm sure people in the community would be able to help as there has been a lot of playing around with the Central API on here!

1 Like
#14

Cool - thanks so much @seadowg .
I will try to request TAB to add this to the agenda for next meeting (tentatively). @TAB
In addition the ask above, your idea of building a connector is also cohesive because it will not only help import/export (with limited users/calls especially if the connector can support incremental/delta data updates to help with quotas). Additionally it will also enable Users that are stuck not knowing how to *use ODATA for Export/import data. At the same time I think it's quite ambitious goal and may take long time to release. (Also That may require a separate feature request)
I agree strongly with not getting stuck on the old version of collect. Thank you for such positive feedback.
Your earlier idea of separating Server and submission account is also very good as it does solve the problem and brings back the integration flexibility without hampering form updates.

Another way/paradigm to solve this may be to allow Google credential based submission if specified in XLSForm even if current Server is Central. Collect already attempts to do that but fails (errors out) perhaps due to credentials or endpoint or protocol in use. This will also resolve the problem.

Scenario: User can manually (one time) Set/configure Google Account in Collect and switch over to (Central) as the server in Collect Settings (enabling regular updates). User can point to submission credential preference or collect can automatically use Google credential if submission URL is GoogleSheet in the Xlsform -> Settings to use Google Account already configured in Collect (this is already done at runtime by collect upon submission based on xlsform>settings) and submit using Google User configured in Collect even if the current selection is Central server.

I do not know if it's technical feasible/roadmap worthy but a tangent I wanted to share for your review. It will also result in enabling updates while submitting with respective credential. I will

#15

@alios82 Thank you for raising this issue with the @TAB. We discussed this in detail today and here's what we've decided.

We are not going to undo this change. It's not widely used and it's going to be a maintenance burden once we ship multiple projects support in Collect.

If the pain point is accept/reject/update, support for this workflow is shipping in Central in April/May, so until then, you can stay on the old version of Collect. Here's the release criteria if you'd like a sneak peek.

If the pain point is getting data into Google Sheets, then the best way forward is using the REST API to send data from Central to wherever. You can do this with:

4 Likes
#16

@alios82 here are a few pointers to use the R route:

  • Use ruODK to download the data from ODK Central:
    • Setup ruODK: follow vignette Setup.
    • Download all data: follow vignette odata-api.
  • (Optional: transform data locally as needed)
  • Use googlesheets4 to upload data:
    • Setup a Google service account token as per this vignette so you can run the script non-interactively. I couldn't get the non-interactive auth to workn in any other way.
    • Example code to update a Google sheet is here.

Mind that Google Sheets has a hard limit of 5 million cells.
Ping me here if you have further questions!

1 Like