Tuesday, May 22, 2012

Showing dynamic graphs in a blog

Lets face it, those static graphs in blogs are sometimes completely useless, sometimes one bar is overpowering the rest, you can't really edit the data easily, and don't even mention changing the graph type on the fly, including a few graph types in the same page? huh! you must be joking, why would I overcrowd the page just for ease of use?


Here's what I came up with, its doesn't have that flashy select box with multiple graph types, but it should be pretty easy to add.


Update: The demo page is not working in IE, the host I'm using is serving javascript files as text/plain instead of text/javascript.


First, create graph data in google spreadsheet, make sure you publish the spreadsheet, otherwise you won't have the url needed (File -> publish to the web -> Start Publishing)


Then create a page in the blog with this code (or similar), make sure you have the correct urls, for the scripts and your spreadsheet.



 <script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
 <script src="Scripts/google-spreadsheet.js" type="text/javascript"></script>
 <script src="Scripts/google-spreadsheet-parser.js" type="text/javascript"></script>
 <script src="http://code.highcharts.com/highcharts.js"></script>

     <div id="readContainer"></div>
     <div id="writeContainer"></div>

    <script type="text/javascript">


        //container: div container
        //type: 'line' for example
        //title: 'Dictionary Benchmarks'
        //categories: array of x-graph
        //ytitle: y graph title
        function DrawChart(container, type, title, categories, ytitle, series)
        {
            var chart = new Highcharts.Chart({
                chart: {
                    renderTo: container,
                    type: type,
                    marginRight: 130,
                    marginBottom: 25
                },
                title: {
                    text: title,
                    x: -20 //center
                },
                //                subtitle: {
                //                    text: 'Source: WorldClimate.com',
                //                    x: -20
                //                },
                xAxis: {
                    categories: categories
                },
                yAxis: {
                    title: {
                        text: ytitle
                    },
                    plotLines: [{
                        value: 0,
                        width: 1,
                        color: '#808080'
                    }]
                },
                tooltip: {
                    formatter: function ()
                    {
                        return '<b>' + this.series.name + '</b><br/>' +
     this.x + ': ' + this.y + '';
                    }
                },
                legend: {
                    layout: 'vertical',
                    align: 'right',
                    verticalAlign: 'top',
                    x: -10,
                    y: 100,
                    borderWidth: 0
                },
                series: series
            });
            return chart;
        }


        function ChartFromDictionaryData(container,sparser, rows, title, ytitle)
        {

            //need to do read / write / only

            //get all dictionary types
            var serieslist = sparser.DistinctByColumn(rows, "Dictionary");
            delete serieslist["Dictionary"];

            //get all threads
            var threads = sparser.DistinctByColumn(rows, "Threads");
            delete threads["Threads"];

            //highcharts categories 
            var categories = [];
            for (var thread in threads)
            {
                categories.push(parseInt(thread));
            }
            categories.sort(function (a, b) { return a - b; });


            //foreach dictionary, get all rows, order by threads, get timing

            var highchartseries = [];
            for (var series in serieslist)
            {
                var newseries = {};
                newseries.name = series;
                newseries.data = [];
                var rowdata = sparser.FilterByColumn(rows, "Dictionary", series);
                for (var thread in threads)
                {
                    var rowvalue = parseInt(sparser.GetValue(sparser.FilterByColumn(rowdata, "Threads", thread), "Time"));
                    newseries.data.push(rowvalue);
                }
                highchartseries.push(newseries);
            }
            DrawChart(container, "line", title, categories, ytitle, highchartseries);

        }



        localStorage.clear();
//replace with your published spreadsheet url
        var url = "https://docs.google.com/spreadsheet/pub?key=0Akqqf0SS9IbJdFVuZ0hzQnNKcFVFeGJkTzRHdWhYRVE&output=html";
        var googleSpreadsheet = new GoogleSpreadsheet();
        googleSpreadsheet.url(url);
        googleSpreadsheet.load(function (result)
        {
            try
            {
                var sparser = new GoogleSpreadsheetParser(result);

                var readrows = sparser.FilterByColumn(sparser.FilterByColumn(sparser.GetAllRows(), "Read", "TRUE"), "Write", "FALSE");
                var writerows = sparser.FilterByColumn(sparser.FilterByColumn(sparser.GetAllRows(), "Read", "FALSE"), "Write", "TRUE");

                ChartFromDictionaryData('readContainer',sparser, readrows, "Dictionary Benchmarks - Read", "Time (ms) per 1 million operations");
                ChartFromDictionaryData('writeContainer',sparser, writerows, "Dictionary Benchmarks - Write", "Time (ms) per 1 million operations");

                //$('#results').html(JSON.stringify(result).replace(/,/g, ",\n"));
            } catch (e)
            {
                alert(e);
            }
        });
    </script>


and voila, you have a dynamic chart.


Take a loot at highcharts, I have yet to see a better charting library, the graph selection is great:


http://www.highcharts.com/


I took the idea from Mike McKay, here, but I've modified his code and added some of mine, there's a test project at   https://github.com/drorgl/ForBlog/tree/master/GoogleSpreadsheetReader and the files are in https://github.com/drorgl/ForBlog/tree/master/GoogleSpreadsheetReader/Scripts feel free to use it.


Here's an example I did today:
http://uhurumkate.blogspot.com/p/graph-test.html


And one last bit of information, this link might help you to understand what google spreadsheets can do for you - as a datasource.
http://www.goopal.org/google-sites-business/google-spreadsheets/spreadsheet-output/publish-spreadsheet



No comments:

Post a Comment