Posted on by

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

Web App Report Google Sheets 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.
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.

Published by codewithcurt

Leave a Reply