Search if multiple substrings are present on a row

silentninja89 0
Viewed 31

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?

google-sheets google-apps-script
4 Answers

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.

enter image description here

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

TheWizEd0

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);
  });
}
user30hu0

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)
}
userFDSs0

Use or operator | of regex with regex.test:

const searchTerms = /nios|blob|centre/;  
const result = v.map(([e]) =>
   searchTerms.test(e) ? ["Present"] : ["No"])
TheMaster0
Related