Warm tip: This article is reproduced from serverfault.com, please click

indexof() partial match issue

发布于 2020-11-27 23:34:17

coding a simple website in Google App Script

have a spreadsheet in google sheet - 2 columns - 1st one has nouns and 2nd descriptive words

something lie this:

Column1 - Column2

table - wood

ball - plastic

chair - metal blue

my code searches for a keyword in the 2nd column and returns the match from the 1st column. it works fine when there is one word in column 2 but not when there are more... so search for 'wood' returns table, search for 'plastic' returns ball but search for 'metal' returns nothing and same for 'blue'

any ideas how to solve this?

 function FilterResults(keyword){
  
  //connect with spreadsheet
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("sheet1");
  var sheetData = ws.getRange(1,1,ws.getRange("A1").getDataRegion().getLastRow(),5).getValues();
  
  //create arrays of values from cells in each column   
   var Column1List = sheetData.map(function(r){return r[0]; });
   var Column2List = sheetData.map(function(r){return r[1]; });
   
   
   //make sure search tags are in string format
   var x = keyword.toString();
   
   //find 1st row(position) that has tag in it
   var position = Column2List.indexOf(x);
   
     if (position > -1){
   
   //if the search found a match return cell content according to row number
     return Column1List[position];
   }else{
     return "unavailable";
   }
Questioner
user2048695
Viewed
0
Diogo Almiro 2020-11-28 21:26:04

Your problem is in the line var position = Column2List.indexOf(x);, indexOf returns an exact match, you should use findIndex and pass a function to search your keyword:

var position = Column2List.findIndex( s => s.indexOf(x) >= 0 )

EDIT:

If you want to find an index after some index, slice would copy the array, an work around would be to add a condition to the function: (s, i) => i >= index && s.indexOf(x) >= 0

But if I understood you correctly you could simply use .filter on sheetData:

sheetData.filter(r => r[1].indexOf(keyword) >= 0 )