Using Google Spreadsheets as a Data Source in Your Windows Store App
In this blog, post I’ll show you how to use a public Google Spreadsheet as data source in your Windows Store application.
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 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.
Grab the full source code from GitHub:
https://github.com/telerik/win8-xaml-sdk/tree/master/Blogs/Json-Reader-Universal/
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 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.
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:
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:
- position,
- name,
- wins,
- draws,
- losses and points
Grab the full source code from GitHub:
https://github.com/telerik/win8-xaml-sdk/tree/master/Blogs/Json-Reader-Universal/
Data, JSON, Spreadsheet, UI, UWP
About the Author
Kiril Stanoev
Hi, I'm Kiril and I'm the Product Manager of Telerik UI for Android, Windows Universal and Windows Phone. Feel free to ping me on +KirilStanoev or @KirilStanoev
Related Posts
Comments
Comments are disabled in preview mode.
All articles
Topics
Latest Stories
in Your Inbox
Subscribe to be the first to get our expert-written articles and tutorials for developers!
All fields are required