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 Video:

Video Notes:

  • Legacy Apps Script Editor Used in Video
  • Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
  • For Further Details on Deploying a Web App Click Here
  • Here is another way to Filter a Table in Web App. Click Here

Code in Video:

//function doGet(e) {
//  var htmlOutput =  HtmlService.createTemplateFromFile('FilterWhole');
//  htmlOutput.search = '';
//  return htmlOutput.evaluate();
//}

function doGet(e) {
  var htmlOutput =  HtmlService.createTemplateFromFile('FilterHeaders');
  htmlOutput.miles = '';
  htmlOutput.color = '';
  return htmlOutput.evaluate();
}

//function doPost(e) {
//  
//  Logger.log(JSON.stringify(e));
//
//  var search = e.parameter.search;
//  
//  var htmlOutput =  HtmlService.createTemplateFromFile('FilterWhole');
//  htmlOutput.search = search;
//  return htmlOutput.evaluate();     
//
//}

function doPost(e) {
  
  Logger.log(JSON.stringify(e));

  var htmlOutput =  HtmlService.createTemplateFromFile('FilterHeaders');
  htmlOutput.miles =  e.parameter.miles;
  htmlOutput.color =  e.parameter.color;
  return htmlOutput.evaluate();     

}

function getSheetData()  { 
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName('Data'); 
  var dataRange = dataSheet.getDataRange();
  var dataValues = dataRange.getValues();  
  return dataValues;
}

function getUrl() {
 var url = ScriptApp.getService().getUrl();
 return url;
}
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Web App Filter Whole</h1>
    <?var url = getUrl();?>
    <form method="post" action="<?= url ?>" >
    <label>Search</label><br>
    <input type="text" name="search" value="<?= search ?>" />
    <input type="submit" name="searchButton" value="Search" />
    </form><br>
    
    <table border="1" cellpadding="5px" >
    <?var tableData = getSheetData();?>
    <?for(var i = 0; i < tableData.length; i++) { ?>
      <?if(i == 0) { ?>
        <tr>
        <?for(var j = 0; j < tableData[i].length; j++) { ?>
        <th><?= tableData[i][j] ?></th>
        <? } ?>
        </tr>
      <? } else { ?>
        <? var rowArray =  tableData[i];
        var stringSearch = rowArray.toString().toLowerCase();
        if(stringSearch.indexOf(search.toLowerCase()) != -1 || search == '') { ?>
        <tr>
        <? for(var j = 0; j < tableData[i].length; j++) { ?>
          <td><?= tableData[i][j] ?></td>
          <? } ?>
        </tr>
      <? }} ?>
    <? } ?>
    </table>
    
  </body>
</html>
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Web App Filter Headers</h1>
    <?var tableData = getSheetData();?>
    <?var url = getUrl();?>
    <form method="post" action="<?= url ?>" >
    <label>Color</label><br>
    <input type="text" name="color" value="<?= color ?>" /><br>
    <label>Miles</label><br>
    <input type="text" name="miles" value="<?= miles ?>" /><br>
    <input type="submit" name="searchButton" value="Search" /><br>
    </form><br>
    
   <? if(color != '' || miles != '')
    {
        var output_array = [];
        tableData.forEach(function(value, index) {
        
          if(index == 0)
          {
            output_array.push(value);
          }
          else if(index > 0 && color != '' && miles != '')
          {
            if(value[0].toLowerCase() == color.toLowerCase() && value[2] == miles)
            {
              output_array.push(value);
             }
            }
            else if(index > 0 && color != '')
            {
              if(value[0].toLowerCase() == color.toLowerCase())
              {                       
                output_array.push(value);
              }            
            }
            else if(index > 0 && miles != '')
            {
              if(value[2] == miles)
              {                       
                output_array.push(value);
              }           
            }                           
        });
    }
    else
    {
      var output_array = tableData;    
    }  ?>
    
    
    
    <table border="1" cellpadding="5px" >
    <?for(var i = 0; i < output_array.length; i++) { ?>
      <?if(i == 0) { ?>
        <tr>
        <?for(var j = 0; j < output_array[i].length; j++) { ?>
        <th><?= output_array[i][j] ?></th>
        <? } ?>
        </tr>
      <? } else { ?>
        <tr>
        <? for(var j = 0; j < output_array[i].length; j++) { ?>
          <td><?= output_array[i][j] ?></td>
          <? } ?>
        </tr>
      <? } ?>
    <? } ?>
    </table>

    
  </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.
Sort Google Sheet Web App 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.
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