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!