In this blog, post I’ll show you how to use a public Google Spreadsheet as data source in your Windows Store 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 Newtonsoft.Json.Linq.JObject. 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 System.Threading.Tasks.Task class provides a way to do that. Below 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 JObject.
var jsonObject = JObject.Parse(jsonResponse);
The JObject instance then can be traversed in order to create the application ViewModel. For this particular example I have a class called Team with the following properties:
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.