In this blog post I’ll show you how to use a public Google Spreadsheet as data source in your Android application.
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:
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:
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.