Posted on by

Create Charts in Web Apps using Google Sheets

In this post, I demonstrate how to use the Chart.js JavaScript library to create Charts on a Web App using data from Google Sheets.

How to Video:

Video Notes:

  • Legacy Apps Script Editor Used in Video
  • Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
  • Chart.js Library Click Here
  • Chart.js Documentation Click Here
  • JQuery Library Click Here
  • For Further Details on Deploying a Web App Click Here

Code in Video:

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('WebAppChart');
}

function getCases() { 
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var casesSheet = ss.getSheetByName("US COVID CASES"); 
  var getLastRow = casesSheet.getLastRow();  
  
  return casesSheet.getRange(2, 1, getLastRow - 1, 2).getValues();  

}

function getPopulation() { 
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var populationSheet = ss.getSheetByName("CITY POPULATION"); 
  var getLastRow = populationSheet.getLastRow();  
  
  return populationSheet.getRange(2, 1, getLastRow - 1, 4).getValues();  

}
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.3/Chart.js" 
    integrity="sha512-QEiC894KVkN9Tsoi6+mKf8HaCLJvyA6QIRzY5KrfINXYuP9NxdIkRQhGq3BZi0J4I7V5SidGM3XUQ5wFiMDuWg==" crossorigin="anonymous"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.3/Chart.css" 
    integrity="sha512-SUJFImtiT87gVCOXl3aGC00zfDl6ggYAw5+oheJvRJ8KBXZrr/TMISSdVJ5bBarbQDRC2pR5Kto3xTR0kpZInA==" crossorigin="anonymous" />
    <script>
    $( document ).ready(function() {
      getCases();
      getCityPopulation();
    });
    
    function getCases() 
    {
      google.script.run.withSuccessHandler(function(ar) 
      {
    
        console.log(ar);
    
        var data = [];
        var label = [];
        ar.forEach(function(item, index) 
        {    
          data.push(item[1]);
          label.push(item[0]);   
        });
    
        var ctx = document.getElementById("lineChart").getContext("2d");

	    var lineChart = new Chart(ctx, {
		type: 'line',
		data: {
			labels: label,
			datasets: [{
				label: 'Cases',
				fill: true,
				data: data,
			}]
		},
		options: {
			maintainAspectRatio: false,
            elements: {
				line: {
					tension: 0.000001
				}
			},
			title: {
				display: true,
				text: 'COVID Cases US'
			},
			scales: {
				xAxes: [{
					display: true,
				}],
				yAxes: [{
					display: true,
				}]
			}
		}
	    });

      }).getCases();
    
    };
    
    
    function getCityPopulation() 
    {
      google.script.run.withSuccessHandler(function(ar) 
      {
    
        console.log(ar);
    
        var data = [];
        var label = [];
        var color = [];
        ar.forEach(function(item, index) 
        {    
          data.push(item[2]);
          label.push(item[0]);
          color.push(item[3]);
        });
    
        var ctx = document.getElementById("pieChart").getContext("2d");

	    var pieChart = new Chart(ctx, {
		type: 'pie',
		data: {
			labels: label,
			datasets: [{
				//label: 'Cases',
				//fill: false,
				data: data,
                backgroundColor: color,
			}]
		},
		options: {
            maintainAspectRatio: false,
            title: {
				display: true,
				text: 'CITY POPULATION'
			},
		}
	    });

      }).getPopulation();
    
    };
    </script>
  </head>
  <body>
  <div style="width: 1000px; height: 400px;" >
  <canvas id="lineChart" class="chartjs-render-monitor" ></canvas>
  </div><br>
  <div style="width: 400px; height: 400px;" >
  <canvas id="pieChart" class="chartjs-render-monitor" ></canvas>
  </div>
  </body>
</html>

Related Posts

Embed Map with Marker Google Web App Embed Map with Marker on Google Web App - In this post, I demonstrate how to embed a map with a marker on a Web App using locations from Google Sheets. I am using the Leaflet Javascript Library.
Create Date Picker on Web App How to Create Date Picker on Google Web App - In this post, I demonstrate how to create a Date Picker on HTML form in a Google Web App. This helps the user to easily select the date as well as keep it in a uniform format.
Autocomplete Field Google Web App How to use Autocomplete Field on Google Web App - In this post, I demonstrate how to use the JQuery Autocomplete Field within a Bootstrap Form on Google Web App. This helps with narrowing down a list of values for easier selection on a dropdown
JQuery Tablesorter Web App JQuery Tablesorter on Google Web App - In this post, I demonstrate how to use the JQuery Tablesorter on a Google Web App. This is a good way to sort, filter and scroll through a table data.
Colorbox Google Web App Create Nested View on Google Web App using Colorbox - In this post, I demonstrate how to create a nested view using Colorbox jQuery library on Google Web Application.

Published by codewithcurt