A spreadsheet to save data in AppInventor (or other languages)

caCatalà (Catalan) esEspañol (Spanish)

Today’s article is very technical and not very pedagogical. As always, the origin is educational, since the problem arose in the classroom, with 16 years old students. But it’s a question of information technology.

This students follow the program Mobilitzem la informàtica with some adaptations (only in Catalan). It is a program where students learn design thinking developing an application for mobile phones with appiventor.

All in all, in many applications it is very useful to have a database in the cloud. For the students it is very easy to use Google services, as my school works with G Suite for Education. The problem is that Google does not offer databases for free. It has the Cloud SQL service, but it’s paid. Fusion Table was an option, but Google has already warned that in December it will disappear.

Fortunately Ferran Mas guided me to use a Google spreadsheet as a database, creating a script as webapp and connecting it to appinventor.

In fact, the operation is very simple. The script allows anyone who writes a specific URL, get a text as a response, specifically in JSON format (which is not more than a specific way to sort the data). For example, if you open following URL

https://script.google.com/macros/s/AKfycbyjJO31K-iRBf_J5ffv6tX6flAnWP-0PDmv-i9gtzS1x0J5NeA4/exec?action=query&num=1

script queries the spreadsheet and get the following result (in JSON format)

[{"action":"query","num":1,"question":"Capital of France?","response1":"Paris","response2":"Rome","response3":"London","right_response":1,"photo":"https://media-cdn.tripadvisor.com/media/photo-s/12/f5/f1/8d/eiffel-tower-summit-priority.jpg"}]

Therefore, any system that can make a call to the URL and collect the results, will be able to query the database. It can be App Inventor, another google app script, a javascript or any other.

The spreadsheet is not public, only certain users can consult it. But it must be clear that when giving access through the script, it can not be used to store confidential data. Anyone who knows the URL could make queries and end up knowing the content. It is true that the application does not make the address of the script public, but analyzing the traffic could be easily achieved, as there is no encryption.

Total, in case someone might be interested, I attach the link to an example tutorial with all the steps to create the spreadsheet, create the script, publish it, check it and use it from appiventor.

TUTORIAL CONNECTION SPREADSHEET GOOGLE AS DATABASE IN APPINVENTOR

caCatalà (Catalan) esEspañol (Spanish)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.