In this post, I demonstrate how to create a nested view using Colorbox jQuery library on Google Web Application.
How to Video:
Video Notes:
- Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
- Colorbox Documentation
- CSS and jQuery Libraries: https://cdnjs.com/
- CSS and jQuery Libraries: https://www.cdnpkg.com/
Code in Video:
function doGet(e) {
return HtmlService.createTemplateFromFile('WebAppBoot').evaluate();
}
function getRegions()
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var regionSheet = ss.getSheetByName("REGION");
var regionRange = regionSheet.getDataRange();
var regionValues = regionRange.getValues();
return regionValues;
}
function getStates(region)
{
var returnData = [];
var ss= SpreadsheetApp.getActiveSpreadsheet();
var statesSheet = ss.getSheetByName("STATES");
var getLastRow = statesSheet.getLastRow();
for(i = 2; i <= getLastRow; i++)
{
if(region == statesSheet.getRange(i, 1).getValue())
{
returnData.push([statesSheet.getRange(i,2).getValue(),statesSheet.getRange(i,3).getValue()])
}
}
return returnData;
}<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js" ></script>"
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jquery.colorbox/1.4.33/example1/colorbox.css" >
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.colorbox/1.6.4/jquery.colorbox-min.js" ></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.3/css/bootstrap.min.css" >
<script>
$( document ).ready(function() {
getData();
});
function getData()
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
var displayTable = '';
displayTable += '<table class=\"table\" id=\"mainTable\" >';
displayTable += "<tr>";
displayTable += "<th>Region</th>";
displayTable += "<th>Area</th>";
displayTable += "<th>Name</th>";
displayTable += "<th></th>";
displayTable += "</tr>";
ar.forEach(function(item, index)
{
if(index > 0)
{
displayTable += "<tr>";
displayTable += "<td>"+item[0]+"</td>";
displayTable += "<td>"+item[1]+"</td>";
displayTable += "<td>"+item[2]+"</td>";
displayTable += "<td><input type=\"button\" value=\"States\" class=\"btn btn-primary\" onclick=\"showStates('"+item[0]+"')\" /></td>";
displayTable += "</tr>";
}
});
displayTable += '</table>';
document.getElementById("rowdata").innerHTML = displayTable;
}).getRegions();
}
function showStates(region)
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
var displayTable = "<div style=\"padding: 10px\" ><span style=\"font-weight: bold; font-size: 20px\" >Region: "+region+"</span>";
displayTable += '<table class=\"table\" id=\"mainTable\" >';
displayTable += "<tr>";
displayTable += "<th>States</th>";
displayTable += "<th>Tax Rate</th>";
displayTable += "</tr>";
ar.forEach(function(item, index)
{
displayTable += "<tr>";
displayTable += "<td>"+item[0]+"</td>";
displayTable += "<td>"+item[1]+"</td>";
displayTable += "</tr>";
});
displayTable += '</table></div>';
$.colorbox({html: displayTable});
}).getStates(region);
}
</script>
</head>
<body>
<div id="rowdata"></div>
</body>
</html>Related Posts
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 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 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.
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 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.
