Use Case - Device Agnostic Offline Data Collection to Google Spreadsheets

A reader reached out to me last month with an interesting use case requesting the best system(s) to use. I thought I would share it here to show the capability of these systems.

##The Use Case
A team of remote surveyors each take 20 donated devices to a field site to collect information after they deliver a training. These devices include old iPads, windows phones and android tablets. None of them have a mobile data connection. The implementer asked if it’s possible to load the surveys onto each of the 20 donated devices from the surveyors office, take them to the field and upload the survey results once they return to the office. Most importantly, the implementer would like to have each surveyor access only their data on a Google Spreadsheet without having to login while maintaining a central repository of all surveys for admins to manage.

Key Challenges:

  • Must be device agnostic (run through a modern web browser)
  • Must be able to preload the surveys, collect data without a data connection and upload the results once connected again
  • A surveyor’s data must only be accessible to them and their manager
  • Surveyor’s access to their data must be private to them through their Google Apps account

Ideas

I immediately rattled off a few suggestions including the new Ona Beta, which allows for information sharing across projects (Check Out Their Features) I figured that Enketo was a must because it was the only system that could collect data offline through a browser. Additionally, I needed to make sure that a JSON push was available so we could get the data out to the surveyors Google Spreadsheets.

##The Solution
It’s not up, but here’s the outline:

Device (Enketo) -> Ona.io -> Google Apps Scripts -> Google Spreadsheets

Setup

  • The implementer creates the survey as an XLSform, making sure to add a surveyor field that the surveyor will give the participants as the first question. It’s a text field. This is key because you can’t change the survey once you upload it to Ona.io. However, you can easily read a text field and parse it on the back end.
  • The implementer uploads the XLSform to ona.io and sets the project settings
  • The implementer sets up a Google Apps Script to receive the form submissions and parse them to a Google Spreadsheets, one for each implementer based on the value of the question in the first field. (Unfortunately, this isn’t yet written and publically available, but a similar push to Google Fusion Tables, with instructions, is available here. Note, that this is where the parsing is done and some redundancy checks are appropriate. For example, a misspelled value should send an email to the implementer or manager. Additionally, new surveyor ids should create new spreadsheets and email the implementer so they can get the spreadsheet out to the appropriate surveyor.
  • The implementer puts the URL of the Google Apps Script into the REST SERVICES - JSON POST form properties in ona.io. Note that this is why we have to use ona.io instead of kc.kobotoolbox.org. KoboCat (kc.kobotoolbox.org) doesn’t expose the REST SERVICES at the form level in their user interface.
  • Once uploaded, the implementer sends the link of the Enketo form to all of the surveyors with their individual surveyor ID that is entered as the first question.

Field Use

  • The surveyor goes to the URL on each of the 20 devices while connected to the internet and bookmarks the page. This allows the surveyor to access the URL without remembering it.
  • The surveyor goes to the field with the 20 devices and provides a device to each person in the training. In the best case, the surveyor already has the survey open on the bookmarked page with the first value completed (Surveyor ID)
  • The participant completes the survey and clicks submit. It is then placed in the local cache and is queued for submission once the device is connected to the internet.
  • The surveyor collects the devices and takes them back to her office.
  • The surveyor connects each device to the internet and navigates to the bookmarked page. Each survey in the queue is submitted to the server automatically.
  • The ona.io server collects each survey and sends it to the Google Apps Script, one at a time.
  • The Google Apps Script receives each survey, parses it from JSON to the appropriate fields, performs logic and pushes the data to the appropriate Google Spreadsheet. (Note that changes to these spreadsheets may mess up the GAS script.)
  • The surveyor logs in to their Google Apps account on their computer and sees all of their survey data in a spreadsheet. Meanwhile, the manager is able to login to ona.io, use their rich interface or export all the data to their computer.

##Summary
I hope you’re still with me. I know this was a tough use case, but Enketo + Ona.io + Google Apps was able to make it happen. I see an interesting flow available using these systems because they run through the browser instead of a native application. I imagine that this workflow could be used to simulate an offline surveymonkey. Please let me know what you think, especially if you have a better solution in mind. I’ll make sure to update this post with scripts if they become available for the public.


Contact me if you'd like to talk about this post.

 