我想通过使用Apps脚本实现以下目标:
我有一个名为“测试”的电子表格。在“测试”中,有9个标签,但我只想在“ sheet1”,“ sheet2”,“ sheet3”中搜索。
我想通过使用Apps脚本实现以下目标:
我有一个名为“测试”的电子表格。在“测试”中,有9个标签,但我只想在“ sheet1”,“ sheet2”,“ sheet3”中搜索。
您要搜索一个在三张纸中的任何一张中都出现的课程名称,然后返回一些相关的课程数据。
您已经描述了基于ui的动态数据输入和报告格式。我将其留给您发展。该答案在“搜索”表上使用了非常简单的“搜索”表格。有一个单元格可输入搜索词,并有空格显示结果。
这些图像显示了之前和之后:
空白搜索表单
热门搜索结果
该脚本的基础是:
var datasheets = ["Sheet1","Sheet2","Sheet3"];
var sheet = ss.getSheetByName(datasheets[d]);
var data = sheet.getRange(startrow,1,LR-startrow+1,5).getValues();
var courses = data.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]
var result = courses.indexOf(searchdata);
searchresults.push(data[result][3]);// ID
if (resultcounter ==0){
searchresults.push("No matches"); // message
function so5868680301() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
// define the search sheet
var sheetname= "search";
var searchsheet = ss.getSheetByName(sheetname);
// get the search term
var searchdata = searchsheet.getRange("B2").getValue();
//Logger.log(searchdata);//DEBUG
// define the search results output range
var searchoutput = searchsheet.getRange(7,5);
// clear the search results
searchoutput.clearContent();
// the sheets to be searched
var datasheets = ["Sheet1","Sheet3"]
//Logger.log(datasheets.length);// DEBUG
// the start row on the data sheets
var startrow = 5;
var resultcounter = 0;
// loop through the sheets
for (var d =0;d<datasheets.length;d++){
var sheet = ss.getSheetByName(datasheets[d]);
//Logger.log(sheet.getName());// DEBUG
// get the Last row on this sheet
var LR = sheet.getLastRow();
// define a range for the sheet
var data = sheet.getRange(startrow,5).getValues();
//Logger.log(data);//DEBUG
// get the course list in column A as a separate array
var courses = data.map(function(e){return e[0];});//[[e],e]
// search the courses array for the serachterm
var result = courses.indexOf(searchdata);
// if the result is -1,then the sraech term couldn't be found,// otherwise the result is the instance number in the courses array
if (result !=-1){
Logger.log(courses); //DEBUG
Logger.log(result); //DEBUG
// create an empty array
var searchresults=[];
//push the search results onto the array.
//sheet name
searchresults.push(courses[result]);// course name
searchresults.push(sheet.getName());// sheet name
searchresults.push(data[result][1]);// status
searchresults.push(data[result][2]);// owner
searchresults.push(data[result][3]);// ID
//Logger.log(searchresults);//DEBUG
searchoutput.setValues([searchresults])
resultcounter = resultcounter +1;
}
}
if (resultcounter ==0){
var searchresults=[];
searchresults.push("No matches"); // message
searchresults.push("");
searchresults.push("");
searchresults.push("");
searchresults.push("");
//Logger.log(searchresults);//DEBUG
searchoutput.setValues([searchresults])
}
}