Sharepoint Solutions

Visualizing Data Using SharePoint and Google Charts

Globally, business executives rely extensively on data and analytics for decision making. With the tremendous amount of data that is being churned out by individuals, businesses, systems etc., it is becoming increasingly complex for executives to make business sense from the enormous data that is at their disposal. Further, data alone is normally not useful to the decision makers unless it is analyzed, interpreted, organized and presented in a visually appealing manner. A lot of individuals and businesses make use of diverse visualization tools to create impressive charts. While there are a number of tools available in the market for data visualization, Google Charts stands out in many ways. 

Google Charts - Creating stunning graphs

Google Charts, when integrated with SharePoint, can be immensely useful to business executives and other knowledge workers to dramatically improve their ability to comprehend and present information utilizing interesting visualizations. Data can be visualized in SharePoint in different ways. Below are some of the options:

  • SharePoint Services along with any JavaScript API (Chart.js, Google Charts, Highcharts, D3.js etc.)
  • Chart Web Part (Depreciated from SharePoint 2013 onwards)

It is important to note that if you are using SharePoint version available in Office 2013, the Chart Web Part feature is no longer available. The alternate way is to make use of any JavaScript Chart API to visualize the data in SharePoint. In this post, we explain how Google Charts API can be used to visualize the data.

Here are a few compelling reasons to choose Google Charts over other JavaScript Charts:

  • Free to Use – With no cost for using and excellent compatibility with SharePoint, there’s no reason not to try it.
  • Rich Gallery – Choose from a variety of charts, from simple scatter plots to hierarchical tree maps.
  • Highly Customizable – Configure the charts to perfectly match the look and feel of your project/website/intranet.
  • HTML5-SVG Compatible – Google Charts is cross-browser compatible, cross-platform portable and mobile friendly. Furthermore, no additional plugins are required.
  • Dynamic Data – Connect your data in real time using multiple data connection tools.

Integrating Google Charts in SharePoint 2013

Here’s a step-by-step process to visualize data using Google Charts API in SharePoint 2013.

Step 1
  • Log in to Office 365 and navigate to SharePoint Online.

SharePoint Main Menu

  • The first step is to create a web page in SharePoint where you intend to showcase Google web charts. Below is the process to create a web page in SharePoint.
  • Click on the ‘Settings’ icon and then select ‘Add a page’. Give a preferred name to the web page and save the page. In this illustration, we have named the web page as ‘Charts Demo’.

Office 365 settings

  • The next step is to create a new ‘Custom List’. Here’s the process to create a ‘Custom List’: Click on the ‘Settings’ icon available on the top right corner of your browser and then select the option ‘Add an app’.

Office 365 settings

  • Then click on the ‘Custom List’ component displayed on the Site Contents screen.

Custom List

  • Provide a unique name to the custom list. Here, we have named the custom list as ‘smartphone_sales’.

Custom List Name

Step 2

Visit your ‘Site Contents’ page by clicking on the Settings > Site Contents and ‘smartphone_sales’ tab and then edit the ‘custom list’ as shown below.

Custom List Settings Screen

  • Rename the title column depending on your preference. In this case, we have renamed it as ‘Year’.

Title Column

  • Click on the ‘+’ button to add new columns (data type). Here, we have added three new columns titled ‘Red Mi3’, ‘Honor 5s’ & ‘Le 1s’.
  • Subsequently, you can add data to the list by clicking on ‘new item’ or ‘edit’ options.

New Item

  • Add ‘smartphone_sales’ custom list to the ‘Charts Demo’ page.
  • Go the ‘Charts Demo’ page and click on the ‘Edit’ option available on the right side of the page.

Charts Demo Edit

  • Click on the ‘Insert’ tab and then select ‘Web Part’ and select the list, click on the ‘Add’ button and save the web page.

Web Part

Step 3

There are two methods to include Google Charts API in SharePoint

  • Method 1 – Download the dependencies and save these in ‘Site Content’ > ‘Site Assets’
  • Method 2 – Include dependencies directly using the below URLs. In our example, we have followed this method.
Dependent Libraries

https://www.gstatic.com/charts/loader.js
https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices.min.js

Adding the code snippet to the web page in SharePoint
  • Go to the ‘Charts Demo’ page and click on the ‘Edit’ option (top right side).
  • Add the ‘Script Editor’ to the page by following the below instructions (refer to the below screenshot).

Adding Code Snippet

  • Click on the ‘Edit Web Part’ option as shown below.

Edit Web Part
Edit Snippet

  • Copy and paste the code snippet in the ‘Web Part’ and click on the ‘Insert’ button and save the page.
Code Snippet ScreenGoogle Charts API Code Snippet

[html]
<html>
<head>
<script src="https://www.gstatic.com/charts/loader.js" type="text/javascript"></script>
<script src="https://code.jquery.com/jquery-3.0.0.min.js" type="text/javascript"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices.min.js" type="text/javascript"></script>
<script language="javascript">
var returnedItems = null;
function loadGoogleLibAndDraw(){
google.charts.load(‘current’, {‘packages’:[‘bar’,’line’]});
google.charts.setOnLoadCallback(visualizeData);
}
function visualizeData() {
var context = new SP.ClientContext();
var list = context.get_web().get_lists().getByTitle(document.getElementById(‘customListName’).value);
var caml = new SP.CamlQuery();
caml.set_viewXml("<View></View>");
returnedItems = list.getItems(caml);
context.load(returnedItems);
context.executeQueryAsync(onSucceededCallback, onFailedCallback);
}
function onSucceededCallback(sender, args) {
var data = new google.visualization.DataTable();
data.addColumn(‘string’, ‘Year’);
data.addColumn(‘number’, ‘Red Mi3’);
data.addColumn(‘number’, ‘Honor 5s’);
data.addColumn(‘number’, ‘Le 1s’);
var enumerator = returnedItems.getEnumerator();
var markup = ”;
while (enumerator.moveNext()) {
var row = [];
var listItem = enumerator.get_current();
row.push(listItem.get_item(‘Title’));
row.push(listItem.get_item(‘sezq’));
row.push(listItem.get_item(‘_x0063_977’));
row.push(listItem.get_item(‘_x0077_q11’));
data.addRow(row);
}
var options = {
chart: {
title: ‘Sales Trend’,
},
bars: ‘vertical’
};
var barChart = new google.charts.Bar(document.getElementById(‘BarChart’));
barChart.draw(data, options);
var lineChart = new google.charts.Line(document.getElementById(‘LineChart’));
lineChart.draw(data, options);
}
function onFailedCallback(sender, args) {
var markup = ‘<p>The request failed: <br>’;
markup += ‘Message: ‘ + args.get_message() + ‘<br>’;
displayDiv.innerHTML = markup;
}
</script>
</head>
<body onload="loadGoogleLibAndDraw()">
<form name="metricsform" id="metricsform">
<input id="customListName" name="customListName" value="smartphone_sales" type="hidden"/>
</form>
<div>
<div id="displayDiv"></div>
<div id="BarChart" style="width: 900px; height: 500px;"></div>
<div id="LineChart" style="width: 900px; height: 500px;"></div>
</div>
</body>
</html>
[/html]

  • After the page is saved, the screen will automatically reload and display the chart based on the data. Below are a few sample graphs generated using Google Charts.

Google Charts Graph
Google Charts Graph

Troubleshooting

All things considered, if the above code does not work, then you may have to check the column names.

  • Go to the Site Contents > ‘smartphone_sales’ > Settings

Trouble Shooting Settings Screen

  • Each column is identified by a unique ID, which is auto generated by SharePoint. You can obtain this information from the URL by clicking on the column hyperlink.

Unique ID

  • Change the column IDs in the following JavaScript function. This would fix the issues (if any).

Javascript

Custom Ribbon Action Feature

Additionally, by making use of Custom Ribbon Action, a pop up of SharePoint Modal Dialog of the associated charts can be displayed. This enables users to make changes to the custom list using the quick edit option; the changes are instantly reflected in the charts, making the visualization more interesting.

SharePoint Development and Microsoft SharePoint Services

Being an innovative software development firm specializing in Microsoft technologies, Evoke can help you with Microsoft SharePoint services; we offer end-to-end Microsoft SharePoint solutions for global enterprises. Their comprehensive set of SharePoint services enables enterprises to meet their productivity goals and objectives.

Evoke not only helps global enterprises develop outstanding products, but it also understands their business lifecycle to build highly scalable products. Evoke provides a clear vision and a comprehensive plan to accomplish SharePoint goals. By embracing Evoke’s Microsoft SharePoint Services, an enterprise can improve the overall profit and stay ahead of its competitors.

Call Evoke Technologies at +1 (937) 660-4925 or find out more about how our SharePoint development services can resolve your business problems.

Please follow and like us:

46 Comments

  1. Siva Sareddu

    July 27, 2016

    Good Job Zama!

  2. Jaya

    October 5, 2016

    Very nice and interesting post Zama.

  3. Bhaskar

    January 19, 2017

    Good Job Zama!

  4. Al

    February 7, 2017

    I followed the example on SP 2013 but no graph appeared 🙁

    • Could you please elaborate a bit on the issue? An error message or other details would help me look into the issue.

  5. Urmila

    March 8, 2017

    How can we create effective visualization charts with data stored in excel sheets on SharePoint? Does Google Charts have a free version or any other highly effective options without a high cost? Please suggest

    • We have to consume REST Excel services to obtain data from Excel stored in SharePoint. Power BI is a highly effective option for dealing with Excel data stored in SharePoint.

  6. Greg

    March 14, 2017

    Could you please explain if this solution is compatible as well with SharePoint 2010?
    How to use this with JSOM? How to adapt the code?
    Thanks!

    • Yes, the solution is compatible with SharePoint 2010. And the answer to your second question is by referring to SP.js and SP.Runtime.js and no change in code.

  7. Gunasekhar

    April 3, 2017

    Hi,
    Will you help me how to display data in chart format form SharePoint libraries.
    Thanks

    • Library is also a list and by just replacing the list name with your library name and referring the right column names should help you to generate the charts shown in the post.

  8. Darin

    April 7, 2017

    Great little tutorial. I got it to work! How can I use a pie chart instead of the bar or line charts? I made some modifications to the code but I cannot get it to work. Please advise. Thank you!

    • Thanks Darin, new google.charts.Pie should be able to generate a pie chart and also remove bars in the options.

  9. Darin

    April 7, 2017

    How can I modify the code to use a pie chart instead of a bar or line chart?

  10. Jorge Fernández

    April 18, 2017

    Hi, i get this working in an on premise environment, but on sp online it is not working, any advice?

    • It should be working for both, make sure you are referring to the correct list name and internal column names.

      • Imran

        June 13, 2019

        Hello Badiyuzama, I have tried many different ways and many different times but some reason it is now working. I am confused that I have created this chart about few months ago and it was working fine, but suddenly it stop working, please let me know if anything change in the js files? I am not a coder.
        best

  11. Zubair Ashfaq

    April 26, 2017

    Dear,
    I want to display 6 to 8 different types of graph on a single page in SharePoint Online like bar chart, line chart, pie etc but with different lists and columns of lists. Is it possible with your code?
    Secondly i am trying to draw a pie chart but unable to draw can you send me the code to draw a pie chart?
    Waiting for your response. Your quick response will be highly appreciated.
    Regards,
    Zubair

  12. Maciek

    April 27, 2017

    Hello,
    I followed your instructions and when I saved the page first time, the graph appeared, although empty because there was no data entered in the list at the time and when I did enter data in my list, I reloaded the page and no graph was shown at all.
    I tried downloading the scripts and saving them in SiteAssets and changing the script accordingly, but it didn’t help. I got no error messages.
    I tried updating the column IDs, even list ID and still nothing.
    What else can I try?

  13. sanjay

    May 9, 2017

    neatly done Zama

  14. Pawan

    May 9, 2017

    I have added the code and also made changes as per the column names that I have, also did troubleshoot for the list item id but still the chart is not loading even if i add the webpart.

  15. Niraj Mohan

    May 22, 2017

    Hi Badiyuzama,
    I was trying to run your code with very slight modifications in sharepoint 2013 script editor.
    It was running yesterday but it returns a blank page now – I am not sure if I have goofed up somewhere because the script seemed to work pretty well for me.
    Pl help. I have tried to ask the same qn on stackoverflow as well.
    I run alerts an it gives me alerts after enumerator, after barChart.draw(data, options), lineChart.draw(data, options) and even after google.charts.setOnLoadCallback(visualizeData);
    Any help is very appreciated.
    Thank you!
    https://www.gstatic.com/charts/loader.js
    https://code.jquery.com/jquery-3.0.0.min.js
    https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices.min.js
    var returnedItems = null;
    function loadGoogleLibAndDraw(){
    google.charts.load(‘current’, {‘packages’:[‘bar’,’line’]});
    google.charts.setOnLoadCallback(visualizeData);
    }
    function visualizeData() {
    var context = new SP.ClientContext();
    var list = context.get_web().get_lists().getByTitle(document.getElementById(‘customListName’).value);
    var caml = new SP.CamlQuery();
    caml.set_viewXml(“”);
    returnedItems = list.getItems(caml);
    context.load(returnedItems);
    context.executeQueryAsync(onSucceededCallback, onFailedCallback);
    }
    function onSucceededCallback(sender, args) {
    var data = new google.visualization.DataTable();
    data.addColumn(‘string’, ‘Part No’);
    data.addColumn(‘number’, ‘Volume’);
    var enumerator = returnedItems.getEnumerator();
    var markup = ”;
    while (enumerator.moveNext()) {
    var row = [];
    var listItem = enumerator.get_current();
    row.push(listItem.get_item(‘Part_x0020_No’));
    row.push(listItem.get_item(‘Volume’));
    data.addRow(row);
    }
    var options = {
    chart: {
    title: ‘KPIs’,
    },
    bars: ‘vertical’
    };
    var barChart = new google.charts.Bar(document.getElementById(‘BarChart’));
    barChart.draw(data, options);
    var lineChart = new google.charts.Line(document.getElementById(‘LineChart’));
    lineChart.draw(data, options);
    }
    function onFailedCallback(sender, args) {
    var markup = ‘The request failed: ‘;
    markup += ‘Message: ‘ + args.get_message() + ”;
    displayDiv.innerHTML = markup;
    }

  16. Viet K,

    May 24, 2017

    Hi Badiyuzama,
    I gotta say thanks for this nice post. It works like a charm. Thanks for sharing again.
    Regards,
    Viet K.

  17. Andrew

    June 27, 2017

    This is great.
    Got it working with your sample data.
    Can it sum non table data ? i.e. Count ID by date or week ?

  18. suhasini

    July 26, 2017

    Badiyuzama Mohammad,
    I have tried this charts it is worked perfectly, I want two charts in same page, I have tried but not getting charts. Only one chart displayed at a time please suggest me in attached the code also:
    /*code for two charts in same page*/
    https://www.gstatic.com/charts/loader.js
    https://code.jquery.com/jquery-3.0.0.min.js
    https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices.min.js
    var returnedItems = null;
    var returnedItems1 = null;
    function loadGoogleLibAndDraw(){
    google.charts.load(‘visualization’, ‘1’, {packages: [‘corechart’]});
    google.charts.load(‘current’, {‘packages’:[‘corechart’]});
    google.charts.setOnLoadCallback(visualizeData1);
    google.charts.setOnLoadCallback(visualizeData2);
    }
    function visualizeData1() {
    var context = new SP.ClientContext();
    var list = context.get_web().get_lists().getByTitle(document.getElementById(‘customListName’).value);
    var caml = new SP.CamlQuery();
    caml.set_viewXml(“”);
    returnedItems = list.getItems(caml);
    context.load(returnedItems);
    context.executeQueryAsync(onSucceededCallback, onFailedCallback);
    }
    function onSucceededCallback(sender, args) {
    var data = new google.visualization.DataTable();
    data.addColumn(‘string’, ‘DM’);
    data.addColumn(‘number’, ‘Offshore’);
    data.addColumn(‘number’, ‘Onsite’);
    var enumerator = returnedItems.getEnumerator();
    var markup = ”;
    while (enumerator.moveNext()) {
    var row = [];
    var listItem = enumerator.get_current();
    row.push(listItem.get_item(‘Title’));
    row.push(listItem.get_item(‘Offshore’));
    row.push(listItem.get_item(‘Onsite’));
    data.addRow(row);
    }
    var options = {
    title: ‘Head count’,
    isStacked:true,
    };
    var chart = new google.visualization.ColumnChart(document.getElementById(‘BarChart’));
    chart.draw(data, options);
    }
    function onFailedCallback(sender, args) {
    var markup = ‘The request failed: ‘;
    markup += ‘Message: ‘ + args.get_message() + ”;
    displayDiv.innerHTML = markup;
    }
    function visualizeData2() {
    var context1 = new SP.ClientContext();
    var list1= context1.get_web().get_lists().getByTitle(document.getElementById(‘customListName1’).value);
    var caml1 = new SP.CamlQuery();
    caml1.set_viewXml(“”);
    returnedItems1= list1.getItems(caml1);
    context1.load(returnedItems1);
    context1.executeQueryAsync(onSucceededCallback1, onFailedCallback1);
    }
    function onSucceededCallback1(sender, args) {
    var data1 = new google.visualization.DataTable();
    data1.addColumn(‘string’, ‘DM’);
    data1.addColumn(‘number’, ‘April’);
    data1.addColumn(‘number’, ‘May’);
    data1.addColumn(‘number’, ‘June’);
    var enumerator1 = returnedItems1.getEnumerator();
    var markup1 = ”;
    while (enumerator1.moveNext()) {
    var row1= [];
    var listItem1 = enumerator.get_current();
    row1.push(listItem1.get_item(‘Title’));
    row1.push(listItem1.get_item(‘April’));
    row1.push(listItem1.get_item(‘May’));
    row1.push(listItem1.get_item(‘June’));
    data1.addRow(row1);
    }
    var options1 = {
    title: ‘Revenue’,
    };
    var chart1= new google.visualization.PieChart(document.getElementById(‘piechart’));
    chart1.draw(data1, options1);
    }
    function onFailedCallback1(sender, args) {
    var markup1= ‘The request failed: ‘;
    markup1 += ‘Message: ‘ + args.get_message() + ”;
    displayDiv.innerHTML = markup1;
    }

  19. suhasini

    July 26, 2017

    Badiyuzama Mohammad,
    Hi,
    I have built this charts based on your link it has worked perfectly, I passed data through Excel and SharePoint list, I want to click this chart so that excel file will open, I have tried this but not getting the expected result. Please suggest me in below attached that code also.
    /*code for charts*/
    https://www.gstatic.com/charts/loader.js
    https://code.jquery.com/jquery-3.0.0.min.js
    https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices.min.js
    var returnedItems = null;
    function loadGoogleLibAndDraw(){
    google.charts.load(‘visualization’, ‘1’, {packages: [‘corechart’]});
    google.charts.setOnLoadCallback(visualizeData);
    }
    function visualizeData() {
    var context = new SP.ClientContext();
    var list = context.get_web().get_lists().getByTitle(document.getElementById(‘customListName’).value);
    var caml = new SP.CamlQuery();
    caml.set_viewXml(“”);
    returnedItems = list.getItems(caml);
    context.load(returnedItems);
    context.executeQueryAsync(onSucceededCallback, onFailedCallback);
    }
    function onSucceededCallback(sender, args) {
    var data = new google.visualization.DataTable();
    data.addColumn(‘string’, ‘DM’);
    data.addColumn(‘number’, ‘Offshore’);
    data.addColumn(‘number’, ‘Onsite’);
    var enumerator = returnedItems.getEnumerator();
    var markup = ”;
    while (enumerator.moveNext()) {
    var row = [];
    var listItem = enumerator.get_current();
    row.push(listItem.get_item(‘Title’));
    row.push(listItem.get_item(‘Offshore’));
    row.push(listItem.get_item(‘Onsite’));
    data.addRow(row);
    }
    var options = {
    title: “Head count”,
    isStacked: true,
    legend:’none’,
    colors: [
    ‘#FF8080’,
    ‘#8080FF’,
    ‘#FF0000’,
    ‘#0000FF’]
    };
    var barChart = new google.visualization.ColumnChart(document.getElementById(‘BarChart’));
    barChart.draw(data, options);
    }
    function onFailedCallback(sender, args) {
    var markup = ‘The request failed: ‘;
    markup += ‘Message: ‘ + args.get_message() + ”;
    displayDiv.innerHTML = markup;
    }

  20. Ramshankar

    October 4, 2017

    Hi,
    I have a lookup column which holds my count value. i have to bind the count in one of my columns in datatable as a number value how do i convert the column into number type please help

  21. Md Maidul Islam

    October 25, 2017

    Hi,
    I follow your every steps.
    I download these files.
    https://www.gstatic.com/charts/loader.js
    https://code.jquery.com/jquery-3.0.0.min.js
    https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices.min.js
    Save it on document library . Name are loader.js, jquery.js and SPServices.js.
    I link these on html like these.
    http://server1:2366/dashboard/Test1/loader.js
    http://server1:2366/dashboard/Test1/jquery_min.js
    http://server1:2366/dashboard/Test1/SPServices.js
    ……………………………..
    I check my column name and change on html like below.
    row.push(listItem.get_item(‘Title’));
    row.push(listItem.get_item(‘Red%5Fx0020%5FMi3%5Fx2019%5F’));
    row.push(listItem.get_item(‘Honor%5Fx0020%5F5s’));
    row.push(listItem.get_item(‘Le%5Fx0020%5F1s’));
    ….
    I add list and add WebParts as per your Blogs .
    But When I save it it does not show the graph .
    I am uses SP server 2016.
    I check on inspect there I saw that loader,js create twice . one is blank .
    Is show the error like that :
    GET https://www.gstatic.com/charts/45.2/loader.js net::ERR_NAME_RESOLUTION_FAILED
    Please let me know.
    Md Maidul Islam
    I am new on SP , html , JS, and Jquery.
    Please Explain what did I have done mistake.

    • Petrov

      August 23, 2019

      You need to exchange the ascii underscore character %5F with _
      So should be:
      row.push(listItem.get_item(‘Red_x0020_Mi3_x2019_’));
      row.push(listItem.get_item(‘Honor_x0020_5s’));
      row.push(listItem.get_item(‘Le_x0020_1s’));

  22. Rudy

    November 24, 2017

    Hello,
    Do any one have any idea, why its not working in SharePoint 2016. I am doing step by step as suggested. But getting just a blank page.
    Its Annoying !

  23. Ysar

    September 6, 2018

    Is there a way when we can click on the graphs it links back to the SharePoint list it was used to generate the graph.

  24. Mr. Coffee

    April 10, 2019

    That was awesome. Worked perfectly.

    • Imran

      April 17, 2019

      I have tried every possible way and it is not work, anyone can help please?

  25. Nico

    June 6, 2019

    Is this function compatible with SP 16? I continue to get a blank page. Thank you

  26. Ryan S

    June 18, 2019

    Hello, does anyone know how to make this code work? It was working few months back but now, it is not working, I was wondering what happened?

    • Ryan S

      July 12, 2019

      Hi, did chart work for you?

  27. Cameron Schultz

    June 27, 2019

    Is anyone able to change the chart format into something else? For me anytime of change it, nothing will load. However, I can add “scatter” and it works but every other type causes the graph to disappear. (Likely because of an error trying to load said line.)
    google.charts.load(‘current’, {‘packages’:[‘bar’,’line’]}); — This works fine
    google.charts.load(‘current’, {‘packages’:[‘bar’,’line’,’pie’]}); — This does not
    google.charts.load(‘current’, {‘packages’:[‘bar’,’line’,’scatter’]}); — This works fine
    I thought at first it was just an error with the naming of other kinds of charts but I have tried everything! Only scatter will advance without error on load. Someone please assist!
    *Using sharepoint 2013 on prem

  28. Bhavneet

    December 6, 2019

    Do we need to mention path of List any where ?

  29. Geeta Tharwani

    January 8, 2020

    i need help that i need to count that data of particular list. As in if i have to represent that count of all 3 years for RedmI, honor and Le and then have to represent it in graph. Also, need help with not able to connect the link as well.

  30. erick bohon

    February 5, 2020

    hello. i was able to get it to work in sharepoint 2016. the issue is it only shows up when i put the page in edit but when i take it out of edit, it disappears.

Leave a comment