This week I was asked by my friend Chris Griffith about using a Google spread sheet as a data source for a prototype he was building. Both Chris and I build prototypes as part of our daily jobs, so the question makes sense; what happens when you need to use archived data -- data that you need will need to update from time to time -- but you don't have the time to build a server application or use something like Firebase to deliver the content?

Certainly you may use a JSON file, but managing and maintaining this file can be a pain. It also doesn't give a real online experience. So, off I went to The Googles to see what was out there on this topic.

Chris threw a JQuery-specific on this subject link at me. Thanks to Amit Agarwal for blogging. If you'd like to give him some support there are some Google scripts for purchase on his site.

Using this technique we create a publicly viewable spread sheet and access it using Ionic 2 without using an API key, just the id of the spread sheet.

Caveat: If you use this technique for creating a data source using a Google spread sheet please be advised that this spread sheet will be public. Please consider the data you are using with this technique and do not put anyone's private data at risk. While the spread sheet will only be available via a URL, that doesn't mean that someone won't be able to find it. You can use Google to find mock data generators online.


Caveat: This code sample is built using Ionic 2 Beta 8 which was released earlier this week. I'll do my best to remember update this code sample when and if there are significant changes to the code in the future (or remember to remove this notice). This code sample uses TypeScript. I wholeheartedly endorse using TypeScript with Ionic 2 and Angular 2.

Setting Up a Spread Sheet

Here is my Google spread sheet:


Yeah, fairly simple, but this is an example post not a full application. Also notice that this is a live document, not an image. We've published this spread sheet on the Web via Google Drive.

We have a typical first row that holds the headers for the columns. Notice how I have named these headers as lowercase and without spaces. I've purposefully named these this way because they will end up as keys for our data source.

Now here comes the tricky part: setting it up to be available online. In Google Drive select the File > Publish to the Web... option which will end up giving you a URL which has an id inside of it which is important. In our case the url looks like this:

https://docs.google.com/spreadsheets/d/15Kndr-OcyCUAkBUcq6X3BMqKa_y2fMAXfPFLiSACiys/pubhtml

The id that is in this url is:

15Kndr-OcyCUAkBUcq6X3BMqKa_y2fMAXfPFLiSACiys

Important: Do NOT use Google Drive's blue Share button on the right-hand side of the tool bar for this technique. My results with using that sharing feature with this technique led to receiving JSON errors.

Where Are We Going.

Let's look at the results that are logged with our completed code so you can understand where we are going with this code sample:

Figure 1: Console output for the Final Code Sample.


At the top you see under the "Raw Data" text the Object that you receive from Google using this technique. And, as JSON goes, it is verbose. But the opened nodes are the ones we are focusing on here. Take a look at the Object > feed > entry node. This is where your spread sheet data resides.

The entry node is an Array of Objects representing the row of data in the spread sheet. Notice the keys that are prefixed with gsx$: these are the nodes that contain the row content. For example, the gsx$firstname key represents the firstname column of that row. Now you can see why I mentioned the naming of the headers in the first row.

Now notice that the gsx$firstname key points to an object contain an key of $t. That is the property we are wanting to retrieve.

I could have focused on the Object > feed > entry > content node, but I decided this was the way to go.

At the bottom of Figure 1 you also see the Array that we use in the view component.

Creating the Provider

I used the Ionic CLI's generate command to create my provider and added some code to process the incoming data. Lets take a look at it:

Most of this code is boilerplate. The interesting part of this code resides inside the subscribe() where the data is processed.

Confession: I am still working on gaining the skills necessary for modern Javascript Array processing. This code example is how I solved this problem, but if there are better ways of processing this data please let me know.

In that code we loop through the data.feed.entry node, find the items that are prefixed with gsx$ (and error checks that the subitem contains a $t key) and processes the key stripping away the prefix and creates a typical key/value object which is pushed into our returnArray.

It may be a little much for beginners to take in, but I think careful review of the code should reveal the code's simplicity.

The Component Code

Now lets look at the code in Component that calls this provider:

Since this data is static we are only retrieving this data once so we are making the call inside out construtor method. If we wanted to retrieve this data upon every time the view is loaded, we would leverage the new Ionic lifecycle event ionViewLoaded. Take a look at the lifecycle events in the NavController documentation.

Also note that we have placed the spread sheet id inside this component. There are many ways to provide a separation of concerns in providers. We are placing the id of the spread sheet outside of the provider so that there is no need for any decision-making about which spread sheet to load. Another option would use constants inside our provider to hold the ids and expose those constants to our view.

Summary

In this article we created and published a spread sheet on Google Drive and leveraged it as a data source in an Ionic 2 / Angular 2. Feel free to comment below or contact me on Twitter if you have questions or comments.