How to Sort Google Sheet on Google Web App

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:

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

JSON Web App 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.
Filter Google Sheet in Web App 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.
Display Google Sheet in Web App 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.

Leave a Reply