Skip to content

Instantly share code, notes, and snippets.

@bpwebs
Created May 20, 2023 19:46
Show Gist options
  • 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: bpwebs.com
* Post URL: https://www.bpwebs.com/visualize-google-sheets-data-in-html-charts
*/
function doGet() {
let template = HtmlService.createTemplateFromFile('Index');
let html = template.evaluate().setTitle('Data Visualization Web App');
html.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
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 = [];
dataRanges.forEach(function(rangeObj){
let rangeData = sheet.getRange(rangeObj.range).getValues();
data.push({key:rangeObj.key, values:rangeData});
});
return data;
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-KK94CHFLLe+nY2dmCWGMq91rCGa5gtU4mk92HdvYe+M/SXH301p5ILy+dN9+nJOZ" crossorigin="anonymous">
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/js/bootstrap.bundle.min.js"
integrity="sha384-ENjdO4Dr2bkBIFxQpeoTz1HIcje39Wm4jDKdf19U8gI4ddQ3GYNS7NTKfAdVQSZe" crossorigin="anonymous">
</script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<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.
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
google.script.run.withSuccessHandler(displayChart).getChartData();
}
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',
series:{
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 [];
}
</script>
</head>
<body>
<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>
</div>
<div class="row">
<div class="col-sm-6 mb-4">
<div id="chart1" style="width: 100%; height: 300px;"></div>
</div>
<div class="col-sm-6 mb-4">
<div id="chart2" style="width: 100%; height: 300px;"></div>
</div>
</div>
<div class="row">
<div class="col-sm-4 mb-4">
<div id="chart3" style="width: 100%; height: 250px;"></div>
</div>
<div class="col-sm-4 mb-4">
<div id="chart4" style="width: 100%; height: 250px;"></div>
</div>
<div class="col-sm-4 mb-4">
<div id="chart5" style="width: 100%; height: 250px;"></div>
</div>
</div>
</div>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment