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:
functionlauchSearchDialog() {
var userInterface=HtmlService.createHtmlOutputFromFile('aq5').setWidth(1000);
SpreadsheetApp.getUi().showModelessDialog(userInterface, "Search");
}
functionsearch(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 && newDate(vA[i][0]).valueOf()>=newDate(sObj.from).valueOf() && newDate(vA[i][0]).valueOf()<=newDate(sObj.to).valueOf()) {
var ds=Utilities.formatDate(newDate(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><basetarget="_top"><scriptsrc="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><linkrel="stylesheet"href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"><scriptsrc="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script><script>
$(function() {
});
functionsearch() {
varfrom=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><textareacols="40"rows="5"id="srchtxt"></textarea><br />
From: <inputtype="date"id="b"/><br />
To: <inputtype="date"id="a" /><br /><inputtype="button"value="Search"onClick="search();" /><divid="results"></div></html>Search Sheet:
Post a Comment for "Alternatives To Textfinder Or Search For Multiple Strings"