Data Visualization with Google sheets AngularJs and SVG

It is actually quite easy to implement (almost) live data visualizations without a huge amount of coding. In the past you would need to create admin screens to input and edit data, code to retrieve and transform that data, then code to generate an image of the data to serve up to the user.

All of this was quite a lot of work and the results would be static images that did not scale. This article details a quick and not necessarily production ready way to achieve the same result with a lot less effort.

Leverage the power of Google sheets

First of all you need a data source, Google sheets have a lot of benefits; they are web based, have plenty of import and export options, built in functions and data retrieval. One other useful feature of Google sheets is that you can retrieve your data as a XML or JSON feed.

As a proof of concept we are going to use Google sheets to retrieve delayed stock quotes.

The function to get the days price change is as follows, simply put this into a new Google sheet to see the result:


This will retrieve the percentage change in current price since the previous closing price for a stock (delayed by 20 minutes). We use the percentage change since it can be compared equally between stocks.

There are other values you can retrieve apart from the price change. A full list can be found on the Google Finance docs

So in our spreadsheet we might simply want the ticker and price change as a percentage, you can see how adding multiple quotes to a single sheet would be easy. Add some sensible column names and you are done.

Next we are going to get a JSON feed of the data. The spreadsheet must be published to enable this feature, this method is therefore not suitable if you have any sensitive data on the spreadsheet. Since we are just retrieving data that is already publicly available this is not an issue. Go to file then Publish to the Web.

You need to infer the endpoint for the JSON feed since this is not given by Google, this will be in the following format. The spreadsheet ID can be taken from the URL in the browser when viewing the spreadsheet in Google docs. SPREADSHEETID /od6/public/values?alt=json

If you prefer you can also get the feed as XML by changing the alt parameter to XML.

Scalable Vector Graphics

SVG or Scalable Vector Graphics as a standard have been around for a while, as the name suggests these are scalable and without loss of resolution. Another benefit of SVG is that since they are just tags in html then you can quite easily generate and manipulate them with code.  

Here is a very simple example of the mark-up to create a coloured box. This should work in most modern browsers. You can find lots more simple SVG examples on the w3schools website but it is possible to create much more complex images using SVG.

      <rect height="60"width="60"style="fill:#ff0000;"/>

Create the Angular application

This is the only real coding required in this example. You don't need to use angular for this but with the template system it works quite nicely with SVG.

It is fairly simple angular module that fetches the JSON feed then converts it into an array of more usable objects.

First create the angular module and controller, the controller injects the FeedData dependency which is going to be our service:

var myapp = angular.module("myapp", []);
myapp.controller('mycontroller', ['$scope', 'FeedData', function ($scope, FeedData) {
       $scope.FeedData = new FeedData();

Before creating the service we want to have a simple data transfer object, this will be what our service returns an array of.

myapp.factory("Stock", [function () {
    function Stock(ticker, price, pricechange) {
              this.Ticker = ticker;
              this.PriceChange = pricechange;
              this.PriceChangeAbs = Math.abs(this.PriceChange);
              this.Color = this.PriceChange > 0 ? '#298C6F' : '#DE5D3D';

    Stock.prototype.IsValid = function () {
              var invalidText = '#N/A';
              return this.PriceChange != invalidText;

    return Stock;

You could just return the JSON straight to the template but there is some validation required before the template can use it. We also want to avoid lots of inline code in our template so we add in some public variables to help us out one to get a colour to indicate increase or decrease in price and another to ensure we always have a positive value to apply to a dimension on our vector graphic.

Now create the service we inject the dependencies for our DTO and the http module.

myapp.factory('FeedData', ['$http', 'Stock', function ($http, Stock) {
    var FeedData = function () {
              this.items = [];

              var url = 'YOUR_SPREADSHEET_FEED_URL';
              $http.get(url).success(function (result) {
                     angular.forEach(result.feed.entry, function (item, index) {
                           var stock = new Stock(
item.gsx$change.$t);                            if (stock.IsValid()) {                                   this.push(stock);                            }                      }, this.items);               }.bind(this));        };     return FeedData; }]);

Finally you can create a template to use the data with SVG, at first I though some custom directives would be required to achieve this but angular handles everything quite nicely.

<div id="ng-app" data-ng-app="myapp" data-ng-controller="mycontroller">
    <div data-ng-repeat="item in FeedData.items">
        <h4 data-ng-bind="item.Ticker"></h4>
        <h5 data-ng-bind="item.Price"></h5>
            <circle cx="30" cy="40" r="20" data-ng-style="{ 'stroke': item.Color, 'stroke-width': ((item.PriceChangeAbs) + 2), 'fill':'none' }" />

You can see a demo of this technique being used here.