Google Charts Using SharePoint List Data
I'm stumped and need a little bit of help
What I want to do is use Google Chart API to create a simple bar graph(chart) that will pull data from a Share Point list.
Here is Google Bar Chart Code....
<html>
<head>
<body>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['Month', 'Year', 'Number Of Days'],
['Dec', 2013, 10],
['Jan', 2013, 6],
['Mar', 2012, 22],
['Jun', 2011, 44] ]);
var options = {
title: 'Company Performance',
vAxis: {title: 'Year', titleTextStyle: {color: 'red'}} };
var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
chart.draw(data, options); }
</script>
</head>
<body>
<div id="chart_div" style="width: 900px; height: 500px;"></div>
This works but it is static and I need dynamic data coming from a SharePoint list. Here's the method I used to pull the data from a SharePoint list but can't seem to figure out the best way to get the Google Chart to use my data. Thank in advance.
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready( function() {
var siteUrl = "//serverURL",
listId = "{b2b61446-4dfc-44f0-80af-8d18b2b17547}";
$.get( siteUrl + "/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=TRUE&List=" + listId,
function( xml ) {
var zrow = xml.getElementsByTagName("z:row");
for(var i=0; i<zrow.length; i++) {
$("#table1 tbody").append(
"<tr><td>"
+ zrow[i].getAttribute("ows_Month")
+ "</td><td>"
+ zrow[i].getAttribute("ows_Number_x0020_Of_x0020_Days")
+ "</td><td>"
+ zrow[i].getAttribute("ows_Year")
+ "</td></tr>"
);
}
});
});
</script>
Answer
I want to thank Asgallant for the response. It was his reposense that prompted me to explore other ways or options to get this to work. Using HighCharts I came up with the following resolution to pull data from a SharePoint list using XML and JQuery. Check it out. By the way this works for both the Bar Chart and Pie Chart for HighCharts. All you need to do to flip the charts from Bar chart to Pie chart is change the options in the "plotOptions" of the code, where it says [bar]...change to [pie].
By the way, the underscore.js file can be downloaded from GITHib.com or just do a google search on underscore.js. Another thing....I haven't tested this with over versions of JQuery or SPServices so it might be important to just used what's already been tested here in this example.
Cheers!
And now the code.....The example is based on a custom list of Time Off Request for employees.
<script type="text/javascript" src="../Style Library/High Charts/jquery-1.8.3.min.js"></script>
<script type="text/javascript" src="../Style Library/High Charts/highcharts.js"></script>
<script type="text/javascript" src="../Style Library/High Charts/jquery.SPServices-0.7.2.js"></script>
<script type="text/javascript" src="../Style Library/High Charts/underscore.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$().SPServices({
operation: "GetListItems",
CAMLQuery: "<Query><OrderBy><FieldRef Name='Employee'/></OrderBy></Query>",
CAMLViewFields: "<ViewFields><FieldRef Name='Title'/><FieldRef Name='Day_x0020_Off_x0020_Taken'/><FieldRef Name='Request_x0020_Type'/><FieldRef Name='Time_x0020_Off_x0020_Description'/><FieldRef Name='Employee'/></ViewFields>",
listName: "{2fdbe305-a489-4428-a319-b4167b8dbabf}",
completefunc: processData
});
});
function processData (xData, status) {
var torData = [];
$(xData.responseXML).SPFilterNode("z:row").each(function () {
torData.push({
timeOff: $(this).attr('ows_LinkTitle'),
dayOffTaken: $(this).attr('ows_Day_x0020_Off_x0020_Taken'),
requestType: $(this).attr('ows_Request_x0020_Type'),
timeOffDescription: $(this).attr('ows_Time_x0020_Off_x0020_Description'),
employee: $(this).attr('ows_Employee')
});
});
var chartData = [];
var employeeData = _.groupBy(torData, 'employee');
_.each(employeeData, function(row) {
var empCount = row.length;
chartData.push( {
name: row[0].employee,
y: empCount
});
});
renderChart (chartData);
}
function renderChart (data) {
var chart = new Highcharts.Chart({
chart: {
renderTo: 'torChart',
plotBackgroundColor: null,
plotBorderWidth: null,
plotShadow: false
},
credits: {
enabled: true
},
title: {
text: 'Employee Time Off'
},
plotOptions: {
bar: {
allowPointSelect: true,
cursor: 'pointer',
dataLabels: {
enabled: true,
color: '#000000',
connectorColor: '#000000',
formatter: function() {
return '<b>'+ this.point.name +'</b>: '+ this.y +' Times';
}
},
}
},
series: [{
type: 'bar',
name: 'Days Off Taken',
data: data
}]
});
}
</script>
<div id="torChart"></div>