How to filter array for only exact match in Google Apps ScriptJavascript - Stack Overflow

admin管理员组

文章数量:1414621

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.

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.

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Feb 6, 2021 at 22:26 Marios 27.4k99 gold badges3939 silver badges5858 bronze badges asked Feb 6, 2021 at 20:24 SpeckixSpeckix 2111 silver badge44 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

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.

本文标签： How to filter array for only exact match in Google Apps ScriptJavascriptStack Overflow