ODK Central OData to Google Sheets

Support
1

Hello universe :slight_smile: !

Trust everyone is doing well here.

Quick question - just migrating to ODK Central and this comes with its list of question. First one for the day, can the Odata feed be connected to Google Sheets? The links I am reading from, I find them quite unreliable of sorts. Any ideas on this please?

1 Like
2

I'm curious, @Prasanna_Sundaram. Why do you need the data in Google Sheets? Is connecting directly to the OData feed using Excel or Tableau not sufficient?

3

Hello @yanokwa. Writing on behalf of @Prasanna_Sundaram. We need it in Google sheet because we have created ready to use individual forms for end users so that live data could be uploaded. in case the data could be linked to google sheets, the results would be live.

Have to explore yet on other platforms. We have done that in the past on google sheets and clients are comfortable with that.

2 Likes
4

Thank you @ashutosh

@yanokwa We primarily work with schools of lower socio economic status in India, and for most of them a higher end technology like PowerBI is still a few miles away. They prefer and are comfortable with more static options like Excel and Google Sheets.

Between Excel and Google Sheets again, Google sheets is live, real-time, the integration of form creations to certain administrative files they need are much more real time. With Excel its an extra step for them to work with Excel online.

Finally, we have set up and trained folks to be comfortable on Google Sheets, drive access, sharing etc. so for all purposes, the demography we work with is finally comfortable with google sheets now and if it can be avoided to re-invent the wheel, then we would like to do that.

2 Likes
5

Has anyone done this so far? Any clues on how google sheets can be integrated to OData?

6

I found this thread, but pardon me, I still dont understand what I have to do here.

7

@Prasanna_Sundaram Have you tried @odil's suggestion at ODK Central v0.3 Beta - #5 by Odil to use https://github.com/saiteja09/GoogleSheetsODataReader? Curious what you find.

1 Like
8

Thank you @yanokwa, we are currently exploring the same. When we get to it a little more hands on, I will keep us posted on how and what we find. :slight_smile:

Thank you!

9

Welcome to the community Ashutosh. You will love this space here.

Ashutosh is my working partner, we are exploring the "data in education" space together.

1 Like
10

hi all: your explanation makes sense for why you are trying to use google sheets.

but if excel is available, you might be interested to know that they've done a lot of work under the covers on live and sync'd data cases, and in this case because it natively understands odata, it may be the more robust choice. google sheets seems more live/realtime because it is more responsive to direct human input, but because 1) it is used to being the sole "truth" and in this case it is not, and 2) it does not natively understand odata, i worry that it may actually be the more difficult choice.

in either case, two observations might be helpful for you:

  1. whether you use excel or google sheets, i would suggest an architecture where there is one imported source of truth of ODK data, a separate source of truth on data collect via the google form, and to merge those two into a third final product which is read-only. this will minimize issues where if the synchronization fails you no longer know which rows are truth and which are broken.
  2. odata sounds fancy but really it is just JSON. the odata part of it additionally specifies a standard schema definition format (which we provide) and a write-back query for modifying data (which we do not support). so if you can't find a good odata-related tool, but you already understand what your own data schema is, then any tool that understands JSON will do just fine.
2 Likes
11

Thank You Issa, I guess I am going to need a little help to understand everything there though. Let me try to process as much as I can and get back to you soon.

12

Just curious if anyone else is still in this situation or have used: https://github.com/saiteja09/GoogleSheetsODataReader

I'm in a similar situation as @Prasanna_Sundaram in a rural area working mostly with non literate people and would like to stay with google sheets.

I just found an add-on for Google Sheets that is free that works for smaller amounts of data. I was able to connect several test datasets, however in my dataset that has lots of nested groups, none of the groups are showing up. I'm quite new to Odata and ODK Central and not sure why the groups aren't showing up. I'm not even sure what question to ask other than why is all the data from groups not showing up when being queried?

https://docs.skyvia.com/skyvia-query-google-sheets-add-on/

When I connected to the same dataset via desktop version of Excel, I had to manually expand each group in order to import data from that group, then data was successfully queried.

1 Like