Moving data from Central to Google Sheets

As Google Drive is no longer used as a server and currently only the central ODK is being used, developers or administrators can help users with information
Or guidance on how we can easily and quickly import the data into Google spreadsheets through the center's APIs, just like it used to be.
I mean is there a way in which we can use the odk center as a transistor and through a Google cheats pi import the data into the drive as a storage exactly as it was before.

1 Like

Why do you want the data in Google Sheets? Understanding that would help guide the suggestions

1 Like

I'm a beginner in programming, however I really need to handle everything that arrives from ODK Central in a dynamic way, and I need to display them in an interface (frontend).. and I need this data to be stored in a more affordable cloud because the cost of storing data on a VPS is too high for me to handle it from the server itself, in other words, the best way to handle data coming from ODK Central is to send it to Manu using cheap storage like Google Drive or azure, and build an interface that manages data directly from this cloud, showing dynamic graphics in carousel format among others from my form... I hope I was clear because I really need this information to start my project.. thank you very much!

It sounds like you like using ODK Collect and your primary needs beyond that are a very low cost solution and access to basic data analysis that produces real-time graphs and charts, is that right?

We stopped sending data directly from Collect to Google Sheets for many reasons, some of the most significant being that there are many cases in which we could not guarantee data integrity, because it does not work at scale, it doesn't allow us to add new functionality like Entities, and because Google often changes their interfaces and requirements, leading to significant work.

Using ODK Central will require hosting it and having a domain name which involves some cost. You'll also need to have enough technical expertise to keep the server up to date and healthy.

If those don't sounds realistic, you can consider platforms that use XLSForms/ODK Collect/Enketo and offer a free (but limited) service tier. Some popular examples are Ona and Kobo.

Thanks for responding, but I think you didn't quite get what I meant. I already have ODK Central hosted on a Digital Ocean server. However, maintaining data on a cloud service with a VPS is very expensive. The solution I need is to store media coming from Collect without taking up space on the cloud of my VPS where my ODK Central server is located. Additionally, I will build, using some low-code or no-code platform, an interface to dynamically display the data along with the images from each unique form (IT SEEMS THAT THERE'S ALREADY A COMMUNITY MEMBER THINKING ABOUT SOMETHING SIMILAR WITH APPSMITH). Assuming that I make 500 registrations of a specific item per day, it's a lot of data to store on a VPS server, and it's also very risky to store it on a personal physical server. So, ODK Central would only serve as a transition to another storage server, and this storage server would use the Central API to import the data. Do you understand?

I'm very sorry, I did misunderstand! So then do I understand correctly that your concern is primarily with storing and then sharing out pictures and/or videos that take up significant storage space? You want to download and remove them from Central and store them somewhere from where you can publish references to them? You mention 500 submissions a day -- roughly how big are those submissions?

There are two ongoing initiatives you should be aware of. First, it's not currently possible to delete and purge individual submissions, only entire forms. In the next Central release, it will be possible to delete and purge individual submissions. Second, in the next Central version it will also be possible to store files uploaded to Central in S3-compatible storage (External storage for blobs in ODK Central). This won't immediately address the need to make those media files publicly available but it may relieve some of the long-term storage concerns.

Do you already have visualizations in Google Drive that would show data and media like you want it if the data were in Google Drive? You mention @mathieubossaert's experimentation with AppSmith but that doesn't answer the question of where to store the data -- in his case he replicates it into a normalized postgres database. There are also similar things that people have done with CKAN (some description of how), Superset and Google Looker Studio.

1 Like

If you want to do it very cheap, try using Python in a Google Colab Notebook to fetch your submission data from ODK Central using OData. Mount GDrive in your script and export the fetched data to Google Drive in your desired format like CSV, Excel, JSON, Parquet etc.

2 Likes

Hi @BrunoAngelo!

I'm using pyODK to extract data and attachments from Central to both local file system and SharePoint folders. If you find useful I can share some code that I wrote in Jupyter notebooks. Are you familiar with Python?
The interesting coincidence is that I haven't share any of that yet because all coments and variable names are in Portuguese and it'll take some time to organize and document :))
Maybe we can do that together and get it back to everyone.

5 Likes

Hi, how are you? I'm sure this would be very useful.
I would be very happy if you could share it with me, or if we could exchange information about this project.
He is very interested in setting up a data management system from collect..
If you could help me with your knowledge I would be immensely grateful.