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
How to Create Web App Report from Google Sheets - In this post, I demonstrate how to take a Web App and display an HTML report with input filters using Google Sheets. Also included in this video is the use of the QUERY formula for Google Sheets.
Convert Google Sheet into JSON Web App - In this post, I demonstrate how to convert a Google Sheet into a JSON Web App. The JSON Web App can be used as a web service to bring data into a website.
How to Filter Google Sheet in Google Web App - In this post, I demonstrate how to Filter a Google Sheet in a Google Web Application using HTML and Google Apps Script.
How to Display Google Sheet in Web App - In this post, I demonstrate how to display a Google Sheet Table in a Web App using Google Apps Script.