Reading Google FusionTable or Spreadsheet data into List

Google provides FusionTables and Spreadsheets web applications for maintaining tabular data. These Google applications allow users to create, update and modify data and share them with other users. Google also offers API for accessing these data using a subset of SQL statements sent over HTTP requests.

In this blog, I’ll show you to use App Inventor to issue SQL statements for accessing this data and how to convert data so that it can properly be displayed into such components as ListPicker or ListView.

Below are examples of Spreadsheet and FusionTable data that will be used for this blog.

Creating, providing sharing and getting the tableid or the key is beyond the scope of this post. However, if there are enough interest/comments, I will provide another post detailing the process.

As shown in figure below, our design will include 2 buttons to query Google Spreadsheet and FustionTable. We’ll use a Web component to send HTTP requests and receive results. We’ll use a label to display raw results and a ListView to display formatted list result.

Figure below shows the logic that is used to send query to FusionTable or Spreadsheet. It also shows the implementation that is used to convert the response from CSV Table to List. As seen in figure below, we are using URL endpoints to invoke RESTful API. The URL takes parameters such as table name, output format, SQL statement. Once the HTTP request is sent, a response will be sent back which will trigger Web GotText event handler. Please note that blocks below do not include any type of validations and in production, validation is required to ensure that response code of 200 is received back.

The responseConent that is received back is in form of CSV table like below (R = row, C = column):

• “R1C1″,”R1C2″,”R1C3”
• “R2C1″,”R2C2″,”R2C3”
• “R3C1″,”R3C2″,”R3C3”

We use “list from csv table” to convert responseContent to a list of list as shown below; keep in mind that parenthesis represent list:

((R1C1 R1C2 R1C3) (R2C1 R2C2 R2C3) (R3C1 R3C2 R3C3))

Data is now converted to list of lists and we now can set elements of our ListView to this list using ListView.Elements. However, since we are dealing with lists of lists, the elements in the ListView will include the inner list parenthesis and will look like below:

• (R1C1 R1C2 R1C3)
• (R2C1 R2C2 R2C3)
• (R3C1 R3C2 R3C3)

In order to remove the start and end parenthesis, I now use a procedure called stripParens that will loop through each list, strip the parenthesis and return result as a list:

Below shows the result querying the Google Spreadsheet

Source:

Download source HERE

Interested in Ai2LiveComplete? Please check features and link to demo site  HERE.  Alternatively, if you have problem accessing the doc, you can check document, HERE