So I've made the following code to search for an specific keyword on a row in a sheet:
function findNios() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('DataSheet');
var r = s.getRange(2,11,s.getLastRow() - 1,1);
var v = r.getValues();
var searchTerm = "nios";
const result = v.map(row => row[0].toString().toLowerCase().indexOf(searchTerm) > 1 ? ["Present"]: ["NO"])
r.offset(0,35).setValues(result)
}
However this is only for one keyword in specific as defined here:
var searchTerm = "nios";
And now I want to add several terms to the search, so for example I have the following substrings that I want to search and if one isn't found proceed to look to the other term:
var searchTerms = "nios", "blob": ,"centre";
Is there a way to make this work with multiple terms?
CodePudding user response:
Here is an example of how to perform a seach for multiple words. Note however that this will match a word like niossy.
I made a test sheet as shown.
Next the script.
function findNios() {
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('DataSheet');
//var r = s.getRange(2,11,s.getLastRow() - 1,1);
var r = s.getRange(2,1,s.getLastRow() - 1,1);
var v = r.getValues();
var searchTerms = ["nios", "blob","centre"];
const result = v.map( row => {
let found = ["NO"];
searchTerms.some( term => {
if( row[0].toString().toLowerCase().indexOf(term) > 0 ) {
found = ["Present"];
return true;
}
return false;
}
);
return found;
}
);
console.log(result);
}
catch(err) {
console.log(err)
}
}
The execution log
7:37:36 AM Notice Execution started
7:37:38 AM Info [ [ 'NO' ],
[ 'Present' ],
[ 'NO' ],
[ 'Present' ],
[ 'NO' ],
[ 'NO' ],
[ 'Present' ],
[ 'NO' ] ]
7:37:37 AM Notice Execution completed
Reference
CodePudding user response:
I have not tested this but it looks like it might work:
function findNios() {
var ss = SpreadsheetApp.getActiveRange();
var sh = ss.getSheetByName('DataSheet');
var rg = sh.getRange(2, 11, sh.getLastRow() - 1, 1);
var vs = rg.getValues();
var searchTerm = ["nios"];//add more words here
searchTerm.forEach((st, i) => {
let result = vs.map(row => row[0].toString().toLowerCase().indexOf(st) > -1 ? ["Present"] : ["NO"]);
result.unshift(st);
rg.offset(-1, 35 i).setValues(result);
});
}
CodePudding user response:
Try this:
function findTerms() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('DataSheet');
var r = s.getRange(2,11,s.getLastRow() - 1,1);
var v = r.getValues();
var searchTerms = ["nios", "blob" ,"centre"]
const result = v.map(row => searchTerms.some(term =>
row[0].toString().toLowerCase().includes(term))? ["Present"]:["NO"])
r.offset(0,35).setValues(result)
}
CodePudding user response:
Use or
operator |
of regex with regex.test:
const searchTerms = /nios|blob|centre/;
const result = v.map(([e]) =>
searchTerms.test(e) ? ["Present"] : ["No"])