Near-realtime data exchange with remote computers

Alper · 06-23-2019 02:01 AM

Hi,
My data is recorded in a single google sheet tab and numeric data of all users are in same tab, which is security filtered, per user.

When a user set a criteria to filter his/her own data, I want this filtered data be available to a local computer, probably as csv, and after running some custom analyses on numerical data, return the results to appsheet for presentation of the results in a view. So basically, I want to make some analyses thats not available in appsheet or Google sheet, and provide results. What might be the logical route for such a process? Creating an individual GS tab for each of users? (which I dont prefer). Or any other possibilities?

The local computer that carry out analyses will use python and/or R scripts to do the work. Both are able to read google sheets directly, as far as I know.

Any guidance is appreciated.

Jonathon

Would you be able to translate your python / R script into a google apps script, to perform the analysis directly in the google sheet? Google scripting is similar to javascript.

Alper

I wish I could. But particularly the analyses in R depends on some packages, and they are not simple to replicate.

Jonathon

If you are already capable of reading the google sheet / database into your python / R script, then its just a matter of updating the sheet with the results of the analysis?

Depending on the complexity of the data you are trying to POST back to the google sheet / database, it may be sufficient to use AppSheets API to update the records as needed:

Alper

Yes i was always thinking on need for a different table, but you are right, results can be written into a different column in the same data table, will be much easier.

Bellave_Jayaram

I suspect you want to display charts from the analysis. Can you build a web app using Flask or Django?

Alper

Hi, no I cannot build such web app, but I can find a programmer to do this for me. Another future need will be Kriging or similar interpolation graphs. Would you expand the details a bit more on web apps? Wondering the possibilities.

Bellave_Jayaram

There are different approaches for implementation but basically you need a way 1. to pass data from either AppSheet or your Gsheet into an endpoint by doing a POST (or use URL Parameters with a GET) and 2. process that data using Python in the web app and 3. render an HTML page showing the charts using a suitable charting library.