CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Speckix
Speckix

Reputation: 21

How to filter array for only exact match in Google Apps Script/Javascript

I'm trying to use Google Apps Script to filter only exact matches. I've seen similar questions, but I can't seem to apply them to my situation.

On one sheet, I have a table of information which has the item name in column A, its' ingredient in column B, and its' quantity in column C. Column A contains items named Test and Test 2. When I filter for Test 2, I get results of both Test and Test 2. Here is the code I'm using:

var costSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Cost');
var ingSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Recipe Ingredient');

// Create list of items to filter from the full list
var rows = costSheet.getLastRow();
var itemList = costSheet.getRange(2, 1, rows - 1).getValues();

// Convert itemList to array to be used in filter
itemList = itemList.join();

// Get full non-filtered data
var fullList = ingSheet.getRange('A2:C514').getValues();

// Apply filter criteria
function checkMatch(item) {
  return itemList.indexOf(item[0]) != -1;
}
filterList = fullList.filter(checkMatch);

// Clear target location, then place filtered data
costSheet.getRange('C2:E514').clearContent();
costSheet.getRange(2, 3, filterList.length, 3).setValues(filterList);

I don't have any trouble getting accurate results for multiple items from all three columns, and the only issue I have is when I try to filter an item that begins with the name of another item in the full list (e.g. filtering for Test 2 returns both Test and Test 2, when I want it to just return Test 2).

I am new to working with Google Apps Script/Javascript, hence the 'amateur' coding.

Upvotes: 2

Views: 7390

Answers (1)

Marios
Marios

Reputation: 27350

Explanation:

You are very close!

The issue is related to this line:

itemList = itemList.join();

Assuming that itemList = [["Test 2"],["Test 3"]] as a result of the getValues() method.

If you apply join you are converting the above array into the following string:

Test 1,Test 2

Therefore itemList.indexOf("Test") will return 0 which means your filter function will evaluate to true, but you don't want that since Test is not part of your array. You are mistakenly using the indexOf method of strings instead of the indexOf method of arrays.

Having said that, your goal is to use the the indexOf method of arrays. In order to do so, itemList needs to be an array and not a string.

To convert a 2D array [["Test 2"],["Test 3"]] into a 1D array [Test 1, Test 2] you can use flat.

Solution:

Change:

itemList = itemList.join();

To:

itemList = itemList.flat();

Improved Solution:

Going for the extra mile, you can shorten your code and make it more JavaScript modern like that:

function shorterFunction(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const costSheet = ss.getSheetByName('Cost');
  const ingSheet = ss.getSheetByName('Recipe Ingredient');
  const itemList = costSheet.getRange(2, 1, costSheet.getLastRow() - 1).getValues().flat();
  const fullList = ingSheet.getRange('A2:C514').getValues();
  const filterList = fullList.filter(r=>itemList.includes(r[0]))
  costSheet.getRange('C2:E514').clearContent();
  costSheet.getRange(2, 3, filterList.length, 3).setValues(filterList);
}

In this solution, I used an arrow function and includes.

Upvotes: 4

Related Questions