We want to get data from a google spreadsheet and display it as a chart using gRaphaël. We are going to transform the following spreadsheet into a bar graph.
gRaphaël’s goal is to help you create stunning charts on your website. gRaphaël is based on raphael.js. Raphael.js is a small JavaScript library that should simplify your work with vector graphics on the web. It currently supports Firefox 3.0+, Safari 3.0+, Opera 9.5+ and Internet Explorer 6.0+.
You can download the code of the sample at the end of this article.
1. Loading data from a Google Spreadsheet in javascript
Google spreadsheet offers to serve its data thanks to the Google Data Protocol. The Gdata format that suite to our needs is JSONP. They call it JSON in script. Basically, what we are going to do, is include a script tag in the page that point to the spreadsheet data and when the data is loaded, it will call a function with this data in parameter.
1.1 Preparing our spreadsheet
- Create a spreadsheet that look like our (2 columns: the first one contain the label, and the second one the data)
- Save and publish the spreadsheet (Warning! Don't publish private data!)
- Click on "More Publishing Options," change file format to ATOM & generate URL
- The URL should look something like 'http://spreadsheets.google.com/feeds/list/[spreadsheetid]/[worksheetid]/public/values.'
- Find the parts of the URL that match the example below and keep them for the second part.
1.2 Loading the spreadsheet data
/*
* Specify the information about our spreadsheet
*/
var spreadsheet_id = "tiRm6cBSE3AZJnyLC6Wlysw",
worksheet_id = "od6";
/*
* Creates a script tag in the page that loads in the
* JSON feed for the specified spreadsheet_id/worksheet_id.
* Once loaded, it calls loadGraphJSON.
*/
function getJSON() {
var script = document.createElement('script');
script.setAttribute('src', 'http://spreadsheets.google.com/feeds/list'
+ '/' + spreadsheet_id + '/' + worksheet_id + '/public/values' +
'?alt=json-in-script&callback=loadGraphJSON');
// insert the script tag into the header
document.documentElement.firstChild.appendChild(script);
}
/*
* Extract the information we need into 2 lists (data and labels)
* We iterate over the rows (json.feed.entry)
* and extract our day and distance columns int 2 lists.
* Once this list created, we call drawChart to draw the graph.
*/
function loadGraphJSON(json) {
var data = [];
var labels = [];
for (var i = 0; i < json.feed.entry.length; i++) {
var entry = json.feed.entry[i];
//this is our first column
labels.push(entry["gsx$day"].$t);
// this is our second column
// We need to convert it to float because we get the data as string
data.push(parseFloat(entry["gsx$distance"].$t));
}
drawChart(labels, data);
}
Now that we have loaded the data from the google spreadsheet, we can pass to the second part of this article: displaying the data with raphael.js.
2. Displaying the data with raphael.js
We are going to need some javascript library. insert them at the beginning of your page in the header tag. you can find them on the raphael.js website, or at the end of this article in the downloads.
<script src="raphael.js"></script>
<script src="g.raphael.js"></script>
<script src="g.bar.js"></script>
We can now pass at the interesting part: drawing the chart with raphael.js.
function drawChart(labels, data) {
/*
* Create an instance of raphael and specify:
* the ID of the div where to insert the graph
* the width
* the height
* Tip: Remember that the reference point (0, 0) is at the top left position.
*/
var r = Raphael("holder", 600, 300);
/*
* Create the chart at the position with the parameters:
* * pos on the x axis where the drawing start
* * pos on the y axis where is drawing start
* * width
* * height
* * the values: it needs to be a list of list since you can have multiple data
* * extra parameters:
* stacked: Putting stacked to false seems to create a problem with the labels
* type: the end of the bar, it can be: round sharp soft
*/
var chart = r.g.barchart(10, 10, 580, 280, [data], {stacked: true, type: "soft"});
/*
* Create an hover effect to display the value when the mouse is over the graph.
*/
chart.hover(function() {
// Create a popup element on top of the bar
this.flag = r.g.popup(this.bar.x, this.bar.y, (this.bar.value || "0") + " km").insertBefore(this);
}, function() {
// hide the popup element with an animation and remove the popup element at the end
this.flag.animate({opacity: 0}, 300, function () {this.remove();});
});
/*
* Define the default text attributes before writing the labels
* you can find pore information about the available attributes at:
* http://raphaeljs.com/reference.html#attr
* and in the SVG specification:
* http://www.w3.org/TR/SVG/
*/
r.g.txtattr = {font:"12px Fontin-Sans, Arial, sans-serif", fill:"#000", "font-weight": "bold"};
/*
* We write the labels.
* There is a bug not fixed at the time of writing this article. We added a patch to
* g.bar.js : http://github.com/DmitryBaranovskiy/g.raphael/issues#issue/11
*/
chart.label(labels);
/*
* Set all the bar greater or equals than 15 to red
*/
// iterate over all the bar
for (var i = 0; i < chart.bars[0].length; i++) {
var bar = chart.bars[0][i];
// if the value of the bar is greater or equals to 15 we change the color to red
if (bar.value >= 15) {
bar.attr("fill", "#bf2f2f");
bar.attr("stroke", "#bf2f2f");
}
}
}
You have now a beautiful Graph. see the result
Download
The code of this howto is available on github:
http://github.com/jeremi/raphael-graph-demo.
Download the source
Links
