Skip to content

Instantly share code, notes, and snippets.

Created May 20, 2023 19:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save bpwebs/f2bae8dbd5a2df06269d4e15eddac31f to your computer and use it in GitHub Desktop.
Save bpwebs/f2bae8dbd5a2df06269d4e15eddac31f to your computer and use it in GitHub Desktop.
Visualize Google Sheets Data in HTML Charts - Create a Dashboard
* Visualize Google Sheets Data in HTML Charts
* By:
* Post URL:
function doGet() {
let template = HtmlService.createTemplateFromFile('Index');
let html = template.evaluate().setTitle('Data Visualization Web App');
html.addMetaTag('viewport', 'width=device-width, initial-scale=1');
return html;
function getChartData() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
const dataRanges = [
{range: 'Data!A2:B12', key:'Range1'},
{range: 'Data!D2:G12', key:'Range2'},
{range: 'Data!I2:J12', key:'Range3'},
{range: 'Data!A16:B26', key:'Range4'},
{range: 'Data!D16:E26', key:'Range5'}
let data = [];
let rangeData = sheet.getRange(rangeObj.range).getValues();
data.push({key:rangeObj.key, values:rangeData});
return data;
<!DOCTYPE html>
<base target="_top">
<link href="" rel="stylesheet"
integrity="sha384-KK94CHFLLe+nY2dmCWGMq91rCGa5gtU4mk92HdvYe+M/SXH301p5ILy+dN9+nJOZ" crossorigin="anonymous">
<script src=""></script>
<script src=""
integrity="sha384-ENjdO4Dr2bkBIFxQpeoTz1HIcje39Wm4jDKdf19U8gI4ddQ3GYNS7NTKfAdVQSZe" crossorigin="anonymous">
<script src=""></script>
// Load the Visualization API and the corechart package.
google.charts.load('current', {'packages':['corechart']});
google.charts.load('current', {'packages':['geochart']});
// Set a callback to run when the Google Visualization API is loaded.
function drawChart() {;
function displayChart(data) {
var chart1Data = google.visualization.arrayToDataTable(getDataByKey(data,'Range1'));
var chart2Data = google.visualization.arrayToDataTable(getDataByKey(data,'Range2'));
var chart3Data = google.visualization.arrayToDataTable(getDataByKey(data,'Range3'));
var chart4Data = google.visualization.arrayToDataTable(getDataByKey(data,'Range4'));
var chart5Data = google.visualization.arrayToDataTable(getDataByKey(data,'Range5'));
// Set chart options
var chart1Options = {
title: 'Sales by Region',
var chart2Options = {
title: 'Marketing Campaign Performance',
0: {targetAxisIndex: 0}, // Impression on the left axis
1: {targetAxisIndex: 1},
2: {targetAxisIndex: 1}, // Clicks and Conversions on the right axis
vAxes: {
0: {title: 'Impressions'},
1: {title: 'Clicks/Conversions'}
legend: {position: 'bottom'}
var chart3Options = {
title: 'Sales by Product Category',
is3D: true,
legend: {position: 'bottom'}
var chart4Options = {
title: 'Lead Generation Channels',
legend: {position: 'bottom'}
var chart5Options = {
title: 'Sales by Month',
legend: {position: 'bottom'}
// Instantiate and draw our charts, passing in some options.
var chart1 = new google.visualization.GeoChart(document.getElementById('chart1'));
var chart2 = new google.visualization.AreaChart(document.getElementById('chart2'));
var chart3 = new google.visualization.PieChart(document.getElementById('chart3'));
var chart4 = new google.visualization.ColumnChart(document.getElementById('chart4'));
var chart5 = new google.visualization.LineChart(document.getElementById('chart5'));
chart1.draw(chart1Data, chart1Options);
chart2.draw(chart2Data, chart2Options);
chart3.draw(chart3Data, chart3Options);
chart4.draw(chart4Data, chart4Options);
chart5.draw(chart5Data, chart5Options);
function getDataByKey(data, key) {
for (var i = 0; i < data.length; i++) {
if (data[i].key===key) {
return data[i].values;
return [];
<div class="container bg-body-secondary">
<div class="row">
<div class="mt-4">
<h1 class="text-center" style="font-family: 'Blockletter', sans-serif;">MY DASHBOARD</h1>
<div class="row">
<div class="col-sm-6 mb-4">
<div id="chart1" style="width: 100%; height: 300px;"></div>
<div class="col-sm-6 mb-4">
<div id="chart2" style="width: 100%; height: 300px;"></div>
<div class="row">
<div class="col-sm-4 mb-4">
<div id="chart3" style="width: 100%; height: 250px;"></div>
<div class="col-sm-4 mb-4">
<div id="chart4" style="width: 100%; height: 250px;"></div>
<div class="col-sm-4 mb-4">
<div id="chart5" style="width: 100%; height: 250px;"></div>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment