Lately, I have been involved with a lot of Google Chart work.  For those of you that don’t know, Google Charts allows you to create different kinds of graphs to visualize your data.  These kinds of graphs include: Pie Charts, Bar Charts, Line Charts, Table Charts, etc.
You can check out the rest of the charts in the chart gallery found here.

If you are not familiar with what a SharePoint list is, here’s a good place to start to learn (SharePoint List).

In this post, my goal is to share how I’ve created Google charts using a SharePoint list as a data source.  The data I will be using has been exported into my SharePoint list and you can read where to get this data from in my last post.

I am going to show you how to create a pie chart using the data from my last post.  My pie chart will be based on the total percentage of  “Pens,Pencils,Desk,PenSet and Binder” in a company.

First, open up notepad, SharePoint Designer 2010 or whatever you use for coding.  Next, to begin to create your google chart, the following libraries are required and can be loaded using the Script links in your js file.  Also, don’t forget to load your jQuery and SPServices library.

• The Google JSAPI API (Loads the needed API’s)
• The Google Visualization library (“This library defines all the core utility classes and functions.”)

Once you have have your data and your libraries are loaded, you are ready to use SPServices and jQuery to create the code that will draw the chart based on your provided data in your list.  The draw() method requires two parameters to draw your chart and it is “data” and “options”.  As long as you provide those two based on the type of chart, you should be good to go.

Every chart stores their data in a table and each has a different format for their data and how it should be prepared.  Always look through the documentation of the chart you want to find out table formats.
Here’s what google says…“Different charts use tables in different formats: for example, a pie chart expects a two-column table with a string column and a number column, where each row describes a slice, and the first column is the size label and the second column is the slice value. A scatter chart, however, expects a table consisting of two numeric columns, where each row is a point, and the two columns are the X and Y values of the point. Read your chart’s documentation to learn what data format it requires.”   –Google Charts Site

So, here’s what my pie chart table will look like:

var data = google.visualization.arrayToDataTable([
   ['Region Sales', 'Items Available'],
['Binder', BinderItems],
['Pencils', PencilItems],
['Pen Set', PenSetItems],
['Pen', PenItems],
['Desk', DeskItems]
]);

Each of the rows starting with ‘Binder’ to ‘Desk’ is a slice in my pie chart.  I use SPservices and jQuery to loop through the “SampleData” list and provide the data for each item.

Once your data and your options are ready to go, you can now draw your chart by providing the following code:

var options = {
title: 'Available School Items',
is3D:true,
titleTextStyle: {color: 'black', fontName:'"Verdana"', fontSize: '12'},
legend: { textStyle: {color: 'black', fontName: '"Arial"', fontSize: '12'}}
};

var chart = new google.visualization.PieChart(document.getElementById('chart_div'));

chart.draw(data, options);

Once you are done with the .js file, create a web part page.  You can then add a content editor web part to the page and link it to your .js file.

Here’s a link to the pie chart in SharePoint – Pie Chart.  The code below shows how I put it all together.

</pre>
<table>
<tbody>
<tr>
<td> </td>
</tr>
</tbody>
</table>
<pre>
<!-- Begin Javascript and JQuery -->
<span style="color: #339966;">//load all your libraries</span>
<script type="text/javascript" src="http://issharepoint.com/isdemosite/Script/jquery-1.8.2.min.js" language="javascript"></script><script type="text/javascript" src="http://issharepoint.com/isdemosite/Script/jquery.SPServices-0.7.2.min.js" language="javascript"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script><script type="text/javascript">// <![CDATA[
google.load("visualization", "1", {packages:["corechart"]});

$(document).ready(function() {
<span style="color: #339966;">//Initialize your variables.  Will be used later to capture the value of each item as it's added up</span>
var Pencils=0;
var Binder=0;
var Desk=0;
var PenSet=0;
var Pen=0;

$().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "SampleData",  //Here's my list name
    CAMLViewFields: "<ViewFields></ViewFields>",
	CAMLRowLimit: 0,
	CAMLQuery:"<Query></Query>",
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {   //Loops through each list item
<span style="color: #339966;">//adds the values depending on the item
</span>
		if($(this).attr("ows_Item") == "Binder")
		{
			Binder += $(this).attr("Units");
		}
		else if( ($(this).attr("ows_Item") == "Pencil"))
		{
			Pencils += $(this).attr("Units");
		}
		else if( ($(this).attr("ows_Item") == "Pen"))
		{
			Pen += $(this).attr("Units");
		}
		else if( ($(this).attr("ows_Item") == "Pen Set"))
		{
			PenSet += $(this).attr("Units");
		}
		else if( ($(this).attr("ows_Item") == "Desk"))
		{
			Desk += $(this).attr("Units");
		}
      });

     }

  });
<span style="color: #339966;">//Passes the values into a function that will draw my chart
</span>
 drawChart(Binder,Pencils,Pen,PenSet,Desk);
});
 function drawChart(BinderItems,PencilItems,PenItems,PenSetItems,DeskItems) {
<span style="color: #339966;">//Puts the values in a data table that the pie chart needs.  Remember that every row is a slice
</span>
        var data = google.visualization.arrayToDataTable([
          ['Region Sales', 'Items Available'],
          ['Binder', BinderItems],
          ['Pencils', PencilItems],
          ['Pen Set', PenSetItems],
          ['Pen', PenItems],
          ['Desk', DeskItems]
        ]);

<span style="color: #339966;">//Cart Options shows title, font-color, legend and I want the chart to show in 3D
</span>
var options = {
          title: 'Available School Items',
          is3D:true,
          titleTextStyle: {color: 'black', fontName:'"Verdana"', fontSize: '12'},
          legend:  { textStyle: {color: 'black', fontName: '"Arial"', fontSize: '12'}}
        };
<span style="color: #339966;">//Finds my div and draws my chart there
</span>
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
}
<table>
	<tr><td><div id="chart_div" style="width: 900px; height: 500px;"></div></td></tr>
</table>

// ]]></script>

I hope that makes sense.
// ]]>

jQuery and SP Services SharePoint SharePoint 2010