Google Drive/Sheets as server

google-drive
odk-aggregate

(Richard Fletcher) #1

Hi all,

I have only recently investigated this feature of ODK - using Google Sheets as a server in place of ODK Aggregate. Not sure how I overlooked this when first learning about ODK, it seems really helpful for people to try things out without having to install a server, and as a feature it seems to have been around for quite a while! Another pleasant surprise of ODK...!

I enjoyed making this video about ODK Aggregate and was planning to do another about using Google Sheets instead - even faster for new users to get started.

I am aware of some of the differences between using ODK Aggregate and Google sheets (such as here) and here

In terms of features it seems the main drawbacks are: "Ensure no repeats, audio, or video prompts are in the form. Groups, pictures, GPS prompts are perfectly fine." - you can take pictures and upload these, but you can't use images in the form (as there is no media upload step like there would be with aggregate). You can't record audio or video.

A different type of consideration for me would be that being based in the UK it is useful for me to be able to use App engine to determine where my project server is based (in the EU for data protection reasons) - by using Sheets instead, I wouldn't know where the data is physically stored. Although that wouldn't be a concern if you weren't collecting personal data, depending on your deployment.

I would be very interested and appreciative to hear of any further practical insights, technical concerns or perhaps even legal and ethical reasons you would consider when looking at deployments based on Google Sheets or ODK Aggregate. Thanks !


(Abdul-Majeed) #2

it will be great to have a video on this too, since there are alternative ways of doing things and as far as learning and practice is concern it will be a good idea for the community.

thanks
fabla2020@gmail.com


(Abdul-Majeed) #3

deploying based on google sheets will require an internet connection before one can complete and submit the form but with ODK aggregate you can do so by collecting the data offline and later submit it when you have internet connection, so it will depend on the type of data you are taking and the availability of internet connection.

thanks
fabla2020@gmail.com


(Hélène Martin) #4

That's fantastic, @rfletch0! It is definitely a very useful feature. It has also seen some enhancements recently so some of the limitations are no longer applicable!

This is no longer the case. You can include a -media folder in gdocs that will be downloaded along with the form.

Any media is now supported thanks to @Shobhit_Agarwal.

@Grzesiek2010 is working on repeat support at https://github.com/opendatakit/collect/pull/1851 and it should be available in the next release!

Absolutely, this is an important one.

This actually works the same way between sheets and Aggregate -- you need a connection to download the form (though you can always transfer it to the phone manually) but then you can be offline as long as you want until you need to submit.


(Abdul-Majeed) #5

hi @LN, I have just tested the google sheet and released that some of this features should be look at,

  1. the names of the media files should used the instance name or something that can be easily identify.
    like the name of the image files, audio files and video files. they are in numeric which does not give a clear picture as to which entry it belongs to when you download it.

@Grzesiek2010 it will be good if the repeat group issues are fixed.

good work to everyone

thanks
fabla2020@gmail.com


(Richard Fletcher) #6

Thanks @LN and @Fabla

I have just tried out the media options. Not thoroughly tested but my guess is that uploading/downloading media takes a bit longer via Drive/Sheets than it does via Aggregate?

I think using Drive/Sheets is still probably okay under GDPR. Google's advice is here also involving Privacy Shield which I was previously unaware of. Of course there are still other reasons people may want their own server.


(Yaw Anokwa) #7

Agreed that this could be better. I've filed an issue at https://github.com/opendatakit/collect/issues/2005 so we can understand what our options are.


(Abdul-Majeed) #8

Hi @LN and @yanokwa, i am testing the google/sheets as a server and i would like to know if it is possible to use external file (csv file) to pull data from the external file to the form, if Yes can you brief me on how it will be done?

i am thinking about pulling a census data into the baseline form using the external file of the census data is that possible. like information of household when you entered the household id into the baseline form it should pull information of that household to confirm that you are in the right household. thank you.

thanks


(Hélène Martin) #9

Yes, absolutely. It works the same way as with other media -- you put your csv in the -media folder. Try it out and please let me know if you have additional questions!


(Abdul-Majeed) #10

Thanks @LN, it works when i copy the csv file to the -media folder. now my question is, if i want to share the xform and the results/response file to enumerators to be complete the questions and submit the form by giving them permission to view and edit right respectively, how do i get the csv file into their -media folder if they are not close to me and have to download the forms for the survey. like how it is done in aggregate server. you upload the xform together with the csv file.

thanks


(Hélène Martin) #11

Great! Now you can share both the xml and the -media folder with enumerators. From Collect, they will get the form xml from “Shared With Me” in “Get Blank Form”. Collect will automatically pull the corresponding -media folder.


(Abdul-Majeed) #12

@LN, this is great, i have tried it and it works perfectly. this is great.
thanks


(Richard Fletcher) #13

Hi everyone, I got around to making a video about this feature and here it is:


Hope someone finds it useful
Any feedback don't be shy!
Thanks

(Azam Iqbal) #14

Nice work
I also appreciate your work


(Hélène Martin) #15

A post was split to a new topic: Invalid sheet ID when submitting to Google Sheets


(Yaw Anokwa) #16

2 posts were split to a new topic: Unable to make API calls when running Collect


(Hélène Martin) #17

@Johnnycardel I saw in your introduction that you were having trouble with Gsheets integration. Have you checked out this very useful video? If you have any specific questions it doesn't answer, please post in the support category!