In this post, I demonstrate how to Sort a Google Sheet on a Google Web Application. It is set up for each Column to be Sorted in Ascending Order.
How to Video:
Video Notes:
- Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
- Bootstrap Library Documentation
- JavaScript Sort Function Documentation
- For Further Details on Deploying a Web App Click Here
- Here is another way to Sort a Table in Web App. Click Here
Code in Video:
function doGet(e) {
var htmlOutput = HtmlService.createTemplateFromFile('DisplaySheet');
return htmlOutput.evaluate();
}
function getSheetData(column)
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName('Data');
var dataRange = dataSheet.getDataRange();
var dataValues = dataRange.getValues();
if(column !== '')
{
var headerArray = dataValues[0];
dataValues.shift();
var dataType = dataValues[0][column];
if(isNaN(dataType))
{
dataValues.sort(function (x, y) {
let a = x[column].toUpperCase(),
b = y[column].toUpperCase();
return a == b ? 0 : a > b ? 1 : -1;
});
}
else
{
dataValues.sort(function (x, y) {
return x[column] - y[column];
});
}
dataValues.unshift(headerArray);
}
return dataValues;
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.0/css/bootstrap.min.css" >
<style>
.buttonToLink
{
background: none;
border: none;
color: blue
}
.buttonToLink:hover
{
background: none;
text-decoration: underline;
}
</style>
<script>
function GetRecords(column)
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
var displayTable = '<table class=\"table\" id=\"mainTable\" >';
ar.forEach(function(item, index)
{
if(index == 0)
{
displayTable += "<tr>";
for(var i = 0; i < item.length; i++)
{
displayTable += "<th><input class=\"buttonToLink\" type=\"button\" value=\""+item[i]+"\" onclick=\"GetRecords("+i+")\" /></th>";
}
displayTable += "</tr>";
}
else
{
displayTable += "<tr>";
for(var i = 0; i < item.length; i++)
{
displayTable += "<td>"+item[i]+"</td>";
}
displayTable += "</tr>";
}
});
displayTable += "</table>";
document.getElementById("displayTable").innerHTML = displayTable;
}).getSheetData(column);
}
</script>
</head>
<body>
<h1>Display Google Sheet Web App Sort</h1>
<div id="displayTable"></div>
<script>GetRecords('');</script>
</body>
</html>
Related Posts



