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";
}
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 )
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 )
thanks for the solution - it works but present me with another problem... I need to run this in a loop to find all rows with matching keywords - I use while{} and with indexOf() i can specify a start position (updated in each loop from the position found vefore) - I cant figure out how to make findIndex start the search at a specific index... I could use slice I guess but with a dozen of matching results in a 1000+ rows table it might be a real hog...
I improved the answer
thanks - this did the job !