Alternatives To TextFinder Or Search For Multiple Strings
I am looking for a way to search for all results that fall between two dates. I have a simple html page with a text input that serves as a search and currently one date input. As
Solution 1:
Searching for a String contained in row with first column date between from and to dates
String in the cell must be an exact match. It doesn't search from substrings. In fact all of my testing just involved numbers.
Code.gs:
function lauchSearchDialog() {
var userInterface=HtmlService.createHtmlOutputFromFile('aq5').setWidth(1000);
SpreadsheetApp.getUi().showModelessDialog(userInterface, "Search");
}
function search(sObj) {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('LogSheet');
var rg=sh.getRange(4,1,sh.getLastRow()-1,sh.getLastColumn());
var vA=rg.getValues();
var found=[];
for(var i=0;i<vA.length;i++) {
for(var j=1;j<vA[i].length;j++) {
if(vA[i][j]==sObj.string && new Date(vA[i][0]).valueOf()>=new Date(sObj.from).valueOf() && new Date(vA[i][0]).valueOf()<=new Date(sObj.to).valueOf()) {
var ds=Utilities.formatDate(new Date(vA[i][0]), Session.getScriptTimeZone(), "E MMM dd,yyyy");
vA[i].splice(0,1,'Row:' + Number(i+4),ds);//Had to remove Dates() so that it could be returned to the client
found.push(vA[i]);
}
}
}
if(found) {
Logger.log(found);
return found;
}
}
aq5.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
$(function() {
});
function search() {
var from=String($('#b').val().replace(/-/g,'/'));
var to=String($('#a').val().replace(/-/g,'/'));
var searchfor=$('#srchtxt').val();
console.log('from: %s to: %s searchfor: %s',from,to,searchfor);
google.script.run
.withSuccessHandler(function(fA){
var html="";
if(fA.length) {
fA.forEach(function(r){
console.log(r.join(','));
html+=r.join(',')+ '<br />';
})
}else{
html="No Results Found";
}
$('#results').html(html);
})
.search({from:from,to:to,string:searchfor});
}
console.log("My Code");
</script>
</head>
<h1>Search</h1>
<textarea cols="40" rows="5" id="srchtxt"></textarea><br />
From: <input type="date" id="b"/><br />
To: <input type="date" id="a" /><br />
<input type="button" value="Search" onClick="search();" />
<div id="results"></div>
</html>
Search Sheet:
Post a Comment for "Alternatives To TextFinder Or Search For Multiple Strings"