How to search and filter a Google Sheet based on two parameters in two different columns

Viewed 856

I have a sheet with 46 columns and what will be an indefinite amount of rows. I am looking for a way to produce a web app with a simple UI that lets me search by two criterion and filter out all results that don't match those criterion. The first is a name/emp# search, which would fall in columns 3 and 4 respectively. The other is a date search. The idea being that I can search for all instances of that persons name or number that fall on that date and return it as an html table or some likewise even and reasonably spaced presentation.

Below is the code I've currently experimented with. My knowledge is pretty limited and I want to understand how this works, so keeping it as simple as possible would be ideal. I have been able to create a one box search using TextFinder, but I've heard that can be slow, and this sheet as the potential to be large. I also can't figure out how to further filter it a second time with another category. I've tried taking the results of the first textfinder and running them through another textfinder but that didn't work.

code.gs

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate();
}

function getValuesFromSS(search) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var lastRow = ss.getLastRow();
  var range = ss.getRange(1,3,lastRow,2); //define range for column D
  var ranges = range.createTextFinder(search).findAll();
  var names = '';
  var nums = '';
  var dates = '';
  var urls = '';

  //loop through each range
  for (i = 0; i < ranges.length; i++) {

    var row = ranges[i].getRow();
    var lastCol = ss.getLastColumn();
    var values = ss.getRange(row, 1, 1, lastCol).getDisplayValues(); //get all values for the row
    var empname = values[0][2]; //column C
    var empnum = values[0][3];  //column D
    var date = values[0][4];  //column E
    var url = values[0][46];

    names+=Utilities.formatString("<td>" + empname + "</td>");
    nums+=Utilities.formatString("<td>" + empnum + "</td>");
    dates+=Utilities.formatString("<td>" + date + "</td>");
    urls+=Utilities.formatString("<td>" + "<a href='https://drive.google.com/uc?export=view&id='" + url + "'>DVIR</a>" + "</td>");
}

return {
  first: names,
  second: nums,
  third: dates,
  fourth: urls
}
}

index.html

<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script type="text/javascript">

    function setPageValues () {
    var search = document.getElementsByName('searchtext')[0].value;
      google.script.run.withSuccessHandler(disp).getValuesFromSS(search);
    }

    function disp(values){
      document.getElementById("results1").innerHTML = values.first;
      document.getElementById("results2").innerHTML = values.second;
      document.getElementById("results3").innerHTML = values.third;
      document.getElementById("results4").innerHTML = values.fourth;
    }
  </script>
</head>
<style>
table {
    border-collapse: collapse;
    }
tr { 
  display: block; 
  float: left; 
  }
td {
    border: 1px solid Black;
    display: block; 
    }
</style>
<body>
<input type="text" name="searchtext">
<input type="button" value="Search" onclick="setPageValues();">
<br>
<div name="resultbox">
<table>
<tr id="results1">
</tr>
<tr id="results2">
</tr>
<tr id="results3">
</tr>
<tr id="results4">
</tr>
</table>
</div>
</body>
<script>
</script>
</html>

Here is the sample spreadsheet link: https://docs.google.com/spreadsheets/d/1scFOr4nUXwm7brvlMPHFJhyLei3TI-uvoOVugsx-TeA/edit?usp=sharing

As I mentioned, I want to search for the name -or- emp# in one box and the date in the other and filter only results that match both to display in html. Thank you!

2 Answers
  • You want to search not only "Driver Name" and "Driver Number", but also "Date" in your Spreadsheet.
    • In this case, there are cases that only "Driver Name" or "Driver Number" is inputted. And there are cases that only "Date" is inputted.
  • You want to achieve this using Web Apps with Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Modification points:

  • In this modification, the date-input tag was added to HTML side.
  • When the "Driver Name"/"Driver Number" and "Date" were inputted, those values are sent to Google Apps Script side as an object.
  • At Google Apps Script, those values are searched using TextFinder.
    • The process cost of TextFinder is lower than that of searching each row. So I used for searching both "Driver Name"/"Driver Number" and "Date".

Modified script:

When your script is modified, please modify as follows.

HTML side: index.html

From:
function setPageValues () {
  var search = document.getElementsByName('searchtext')[0].value;
  google.script.run.withSuccessHandler(disp).getValuesFromSS(search);
}
To:
function setPageValues () {
  var search = document.getElementsByName('searchtext')[0].value;
  var date = document.getElementsByName('date')[0].value;
  var obj = {};
  if (!search && !date) alert("No search values.");
  if (search) {
    obj.name = search;
  }
  if (date) {
    obj.date = date;
  }
  google.script.run.withSuccessHandler(disp).getValuesFromSS(obj);
}

And

From:
<input type="text" name="searchtext">
<input type="button" value="Search" onclick="setPageValues();">
To:
<input type="text" name="searchtext">
<input type="date" name="date">
<input type="button" value="Search" onclick="setPageValues();">

Google Apps Script side: code.gs

From:
var range = ss.getRange(1,3,lastRow,2); //define range for column D
var ranges = range.createTextFinder(search).findAll();
To:
var ranges = [];
if ("name" in search && search.name != "") {
  ranges = ss.getRange(1, 3, lastRow, 2).createTextFinder(search.name).findAll();
}
if ("date" in search && search.date != "") {
  var dateRanges = ss.getRange(1, 5, lastRow, 1).createTextFinder(search.date).findAll();
  if (ranges.length > 0) {
    ranges = ranges.filter(function(r1) {return dateRanges.some(function(r2) {return r1.getRow() == r2.getRow()})});
  } else {
    ranges = dateRanges;
  }
}
  • In this case, when Web Apps is run, you can see the 2 input tags.
    • When you input only "Driver Name"/"Driver Number", the values retrieved by searching "Driver Name"/"Driver Number" are displayed.
    • When you input only "Date", the values retrieved by searching "Date" are displayed.
    • When you input "Driver Name"/"Driver Number" and "Date", the values retrieved by searching "Driver Name"/"Driver Number" and "Date" are displayed.

Note:

  • In this modified script, your shared Spreadsheet is used. Please be careful this.
  • For the output, your current script and format were used.

Reference:

If I misunderstood your question and this was not the direction you want, I apologize.

Use a query function to dynamically display the results on change.

Use names range1 and named range2 for filter guides to run the query selections.

Related