替代TextFinder或搜索多个字符串 [英] Alternatives to TextFinder or search for multiple strings
问题描述
我正在寻找一种搜索介于两个日期之间的所有结果的方法.我有一个带有文本输入的简单html页面，该文本输入用作搜索，当前输入一个日期.照原样，它将搜索与给定日期上的文本匹配的所有结果，如果没有输入任何文本，则仅搜索日期，反之亦然.我无法弄清楚如何以相同的方式在两个日期之间进行过滤.我试图获取值，并为这两个日期之间的所有结果使用.filter，这是可行的，但是结果不是在范围数组中返回，而是在值(字符串)数组中返回.我的代码获取每个结果的行，并将该行中的其他值排列在一起.因此，我相信我的结果需要是一个范围而不是一个值.
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 is, it searches for all results that match the text on the given date, if no text is input, then it searches just for the date and vice versa. I can't figure out how to filter it between two dates in the same way. I've tried to get the values and use .filter for all results between those two dates, which works, however the results are not returned in a range array, but instead an array of values (strings). My code gets the row of each result and arrays other values from that row together. Therefore, I believe my result needs to be a range not a value.
当前代码.gs
function getValuesFromSS(search) {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FormResponse');
var lastRow = ss.getLastRow();
var ranges = [];
if ("name" in search && search.name != "") {
var ranges = ss.getRange(2, 4, lastRow - 1, 2).createTextFinder(search.name).findAll();
if (ranges == "") {
ranges = ss.getRange(2, 8, lastRow - 1, 1).createTextFinder(search.name).findAll();
if (ranges == "") {
ranges = ss.getRange(2, 50, lastRow - 1, 1).createTextFinder(search.name).findAll();
}
}
}
if ("date" in search && search.date != "") {
var dateRanges = ss.getRange(2, 6, lastRow - 1, 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 if (search.name == null) {
ranges = dateRanges;
}
}
我已经尝试过了:
function filterTest(startdate, enddate) {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FormResponse');
var lastRow = ss.getLastRow();
var dates = ss.getRange(2, 6, lastRow - 1, 1).getDisplayValues();
dates= dates.filter(function(date) {
if (date >= startdate && date <= enddate){
return true;
}
});
return dates;
Logger.log(dates);
}
哪个返回值数组，但不允许我获取结果的行号.这是我其余的代码，如果有人在乎的话:
Which returns an array of values, but does not let me get row numbers of the results. Here is the rest of my code, if anyone cares:
var issues = '<td style="font-weight:bold;border-bottom:2px solid black;text-align:center">Issues</td>';
var names = '<td style="font-weight:bold;border-bottom:2px solid black;text-align:center">Name</td>';
var nums = '<td style="font-weight:bold;border-bottom:2px solid black;text-align:center">Emp#</td>';
var dates = '<td style="font-weight:bold;border-bottom:2px solid black;text-align:center">Date</td>';
var tnums = '<td style="font-weight:bold;border-bottom:2px solid black;text-align:center">Truck</td>';
var trnums = '<td style="font-weight:bold;border-bottom:2px solid black;text-align:center">Trailer</td>';
var urls = '<td style="font-weight:bold;border-bottom:2px solid black;text-align:center">Link</td>';
//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][3].replace(/["']/g, ""); //column C
var empnum = values[0][4].replace(/["']/g, ""); //column D
var date = values[0][5]; //column E
var tnum = values[0][7].replace(/["']/g, "");
var trnum = values[0][49].replace(/["']/g, "");
var url = values[0][66];
var tkissue = values[0][48].replace(/["']/g, "");
var trissue = values[0][63].replace(/["']/g, "");
var mechveri = values[0][67];
var issue = '';
if (trissue !== "" || tkissue !==""){
if (trissue !== "" && tkissue !== ""){
issue = "<b>Truck:</b> <br>" + tkissue + "<br><br><b>Trailer:</b> <br>" + trissue}
else if (tkissue !== "") {
issue = "<b>Truck:</b> <br>" + tkissue}
else if (trissue !== "") {
issue = "<b>Trailer:</b> <br>" + trissue}
if (mechveri == "") {
issue += "<br><br><b>Status:</b><br> This issue has not been resolved yet. Please see a mechanic for further information."}
else if (mechveri !== "") {
issue += "<br><br><b>Status:</b><br> This issue has been resolved. Please see the DVIR for further information."}
}
if (trnum == "") {trnum = "N/A"};
if (issue !== "") {
issues+=Utilities.formatString("<td><input type='button' value='VIEW' class='issbtn' onclick='alert2(\"" + issue + "\",\"Known Issues:\")'></button></td>");
}
else if (issue == "") {
issues+=Utilities.formatString("<td>None</td>");
}
names+=Utilities.formatString("<td>" + empname + "</td>");
nums+=Utilities.formatString("<td>" + empnum + "</td>");
dates+=Utilities.formatString("<td>" + date + "</td>");
tnums+=Utilities.formatString("<td>" + tnum + "</td>");
trnums+=Utilities.formatString("<td>" + trnum + "</td>");
urls+=Utilities.formatString("<td><a class='button' href='https://drive.google.com/uc?export=view&id=" + url + "' target='_blank'>DVIR</a></td>");
}
return {
nth: issues,
first: names,
second: nums,
third: dates,
fourth: tnums,
fifth: trnums,
sixth: urls
}
}
基本上，这只是为我的html页面设置表格信息，因此我可以对其进行调用. 我也尝试过regex，但是我不太熟练.
Basically, it's just setting table information for my html page so I can call it up. I've also tried regex, but I am not as skilled with it.
我再次尝试将其转换为返回两个日期之间的所有结果，作为我的html页面的输入.欢迎任何帮助.谢谢！
Once again, I'm trying convert this to return all results between two dates as input from my html page. Any help is welcome. Thank you!
推荐答案
搜索包含在行中的字符串，其中第一列日期在起始和终止日期之间
单元格中的字符串必须完全匹配.它不会从子字符串中搜索.实际上，我所有的测试都只涉及数字.
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>
搜索表:
这篇关于替代TextFinder或搜索多个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
