Using Google Spreadsheets as Data Source in Your Android App

In this blog post I’ll show you how to use a public Google Spreadsheet as data source in your Android application. 

Background

Google allows you to treat any public spreadsheet as a table in a database by using the Google Query Language. This language provides an easy way of interrogating a data source and pulling back the results of the query as JSON response or an HTML table.

Normally, you'd access a Google Spreadsheet through a url similar to the one below:
 
https://docs.google.com/spreadsheets/d/1tJ64Y8hje0ui4ap9U33h3KWwpxT_-JuVMSZzxD2Er8k (English Premier League Final Table 1999)

If you want to get the same spreadsheet as a JSON response, you'll have to use its key to construct a URL as shown:

https://spreadsheets.google.com/tq?key=1tJ64Y8hje0ui4ap9U33h3KWwpxT_-JuVMSZzxD2Er8k



As you can see, it is not a ready-to-use JSON and a little bit of work is necessary to make it compatible with org.json.JSONObject. Nevertheless, once the unnecessary parts are trimmed, you’re left with a JSON object that contains all the rows and columns of the spreadsheet.

In addition, the Google Query Language offers server side filtering through SQL-like statements but that would be covered in another post.

Downloading a Google Spreadsheet

Network operations are unpredictable and can cause poor user experience. Therefore, it’s a good development practice to perform network operations on a separate thread from the UI. 

The AsyncTask class provides a way to do that by exposing two important methods:

• doInBackground() does all the work around downloading a web page content as a string. When it’s done, it passes the result to onPostExecute.
• onPostExecute() takes the returned string and displays it in the UI.

Following is the class I use to download the contents of a Google Spreadsheet.

Constructing a JSON object

Once the JSON string has been downloaded and is in compatible format, it can be used to create a JSONObject.

JSONObject jObject = new JSONObject(stringResponse);

The JSONObject then can be traversed in order to create the application's ViewModel. For this particular example, I have a class called Team with the following fields: 

• position
• name
• wins
• draws 
• losses 
• points

The final result can be seen below.




Grab the full source code from GitHub:

https://github.com/telerik/Android-samples/tree/master/Blogs/Json-Reader