Fusiontables are the mechanism provided by App Inventor to operate on spreadsheet-like data. The component works out-of-the-box and is extremely simple to use, so if you’re new to App Inventor it’s highly advisable that you get familiarized with it following for example the pizza party tutorial from the AI website. However, once overcome this initial level, you’ll discover some annoying inconveniences in the AI component:
- Security: You need to grant public access to fusiontables in order for App Inventor to be able to access them
- Performance: The response takes a while to arrive and the message “Fusiontables: processing query…” is not the kind of experience you want to give to the user while he’s waiting
- Functionality: There are some syntax limitations that make it quite burdensome to perform some typical access operations (such as a performing a massive UPDATE or estalishing an ORDERBY using more than one field)
This tutorial explains how to set up a service to access fusiontables improving the first two issues (security and performance). The functionality enhancements will be explained in the second part. It’s also important to note that the service deployed in this first part is compatible with the AI fusiontables component whereas the service from the second part has some slight differences in the format of both input parameters and return value.
The core of the solution we’re going to explain is based on this ShivalWolf’s project, but I decided it could be helpful to write this post in order to complement Shiva’s excellent job with some points:
- Explain how to migrate existing applications to the new method
- Compare the service with the standard fusiontables from AI
- Add some functionalities
- Create a multipurpose service providing 3 important services in a single application: the enhanced Fusiontables from this tutorial, a mail sending functionality and the typical custom TinywebDB.
This first part covers the first two points.
What are we going to build
In this first part we’ll develop and deploy into App Engine a service that allows accessing fusiontables and then we’ll create a simple screen to access a fusiontable using both our new service and standard component from App Inventor:
Both access methods return the same contents because our web component works in a very similar way as the standard component. Very similar means that it allows accessing to data by means of the same SQL-like syntax. Therefore, if you have a working application and wish to adapt it to the new service, you just need to add a web component to the screen (informing its URL as https://yourservice.appspot.com/fusiontable) and make the following adaptations in the code blocks:
|App Inventor standard component||New method|
|How to send the SQL Sentence||Fusiontable.Query||Web.PostTextWithEncoding|
|How to parse the return||List from CSV table||Web.JsonTextDecode|
See the code below for further details about these adaptations.
Fusiontables web service (App Engine)
- Download App Engine for Python at http://code.google.com/appengine/. After installing it, run the GoogleAppEngineLauncher by clicking its icon
- Download these sources . It is a zip file containg the source code for the Fusiontables web service
- Unzip the downloaded zip file. It will create a folder named EnhancedFusionAE_light . You can rename it if you want but then you’ll have to change it also in the app.yaml file
- Edit the main.py and replace the contents of the variables google_user, google_password, api_key and AuthKey as explained below
- In the GoogleAppEngineLauncher, choose File | Add Existing Application. Browse to set the Path to the folder you just unzipped.
- [Optional] Click the Run button to launch a test web service on your local machine.
- In the GoogleAppEngineLauncher, choose Deploy
Client side (App Inventor)
- Download these sources and upload them into App Inventor
- Open the project with App Inventor. Inform the Text attribute in the TextBoxQuery component with a sentence in a SQL format, including the Id of your fusiontable. You can obtain it opening the table from Google drive and choosing the option About this table. The “Id” field shows the table key
- In the blocks code inform modify the initialization of the following variables (marked with a comment)
USR_KEY: indicate the same value you’ve informed in the main.py file (step 4 above)
SERVICE_URL: indicate the URL where you’ve deployed the service (typically something like https://yourapp.appspot.com)
How it works
The server side is a service that receives a SQL string using the syntax allowed by fusiontables and returns the data as a JSON string in the format:
[[row1_field1 row1_field2 …][row2_field1 row2_field2…]…]
I’m not going to explain the python code (which is not mine), but just remark the key aspects from the main.py file.
- We query if an authorization token exists and obtain it otherwise:
- We access fusion table data as if we were calling google’s URL with the SQL sentence as a post data, and adding the authorization token to the request header:
- Finally we return the string obtained from the response:
Regarding security, we just need to grant access to the fusiontable to the user account we’ve informed in the properties (since this is the account sent to fusiontable request). Typically this account will be the administrator of your AppEngine application so you can restrict access only to administrator informing the value ‘login: admin‘ in the handlers section of app.yaml file:
handlers: - url: /fusiontable script: main.app login: admin
That’s all regarding the service. The client side is simple to implement in App Inventor since the web component allows calling a web service and obtaining its return value as the parameter of the gotValue event. The easiest way to work with this data is converting it into a list using the json.decode function as shown below.
We’ll create an app that allows accessing a table using both the standard Fusiontable component and our service. It will look like this:
The graphical components are self-explanatory. The remaining components are:
- FusiontablesControl1: The standard AI component we’ll use for comparison
- Web1: The Web component we’ll use to call our service
- Clock1: A timer to measure the response time of both accessing methods
The first thing to do is indicate the value for the two configurations variables:
- SERVICE_URL: You must replace this variable by the URL where you’ve set up the service
- USR_KEY: Replace it by the authentication key you’ve informed in the main.py file
We have also 4 additional variables:
- lstResult: Used to store the list obtained from the response (in both access methods)
- iRows: Contains the number of rows read, in order to show it in the output message
- iBench1, iBench2: Store the time before and after obtaining data in order to show the delay time in the output message
And here are the actual functions:
The right hand functions are those that call the standard component and therefore are not object of this tutorial. However they can be useful to see the differences among both access methods.
Our web component requires just two functions (one for the request and the other one to capture the response):
- Button1,click: When we press the button, we access the fusiontable simply posting to our web service a string with two pieces separated by ‘|||‘:
- Authentication key
- SQL sentence
- Web1.GotText: The response is obtained in JSON format, which can be easily converted into a list thanks to the useful JsonTextDecode function provided by the web component
Finally, though it’s not relevant for this tutorial, here’s the code used to show the results in the output message:
That’s it. If you’ve arrived up to here you should have available a service compatible with the AI fusiontables component, and a sample AppInventor application that can access to it.
So what have we gained? Well, first off security. It’s not necesssary anymore to grant public access to the fusiontable for AI to be able to access to it. There’s also a performance issue. Actually it’s difficult to demonstrate that the current version of our service is faster than the standard component since it will give different results at each attempt, but at least our component does not show the message “processing query” which is not obviously the best way to entertain user’s waiting time. You’ll probably prefer a nicer progress bar or even show no message since the user can continue working without need to wait for the answer. For example, in our sample app you can press the “Web component” button and start editing the textbox before the results arrive. This is the magic of event-driven programming (see this related nice article from Jos Flores).
However, as demonstrated in this snippet from Puravida, App Inventor’s web component would also allow us to overcome both performance and security issues. So why should we bother to deploy a service? Well, the reason is that such a service opens the door for some nice features that can only be achieved in the server side. Indeed in part two we’re going to modify this service in order to add support for some candy functionalities leading to significant gains in performance and code reduction. That’s when the job we’ve done up to now will get more sense!