Having some data in a Google Sheet, I am looking to filter it based on specific criteria and return corresponding values from another column. Additionally, I need to count the number of elements in the resulting column. Below is an example of the data:
| A | B | |
|---|---|---|
| 1 | Initials | Application Reference |
| 2 | MWB.KBB | 1001 |
| 3 | JET,JJB | 1002 |
| 4 | KBB | 100,310,041,005 |
| 5 | MKGC | 1006 |
| 6 | KBB | 1007 |
If we want to filter the data by searching for "KBB," we should retrieve all cells containing that word, which should be three (3) cells. However, currently only two cells are being returned. The first row with two elements in a single cell is not being included, but it should be. Lastly, we need to count the elements in the returned column following the criteria.
Here is the code attempted:
function filter(){
//opened ss via url
const ws = ss.getSheetByName("Sample");
const range = ws.getRange(2,1,ws.getLastRow() - 1,2).getValues();
const initial = range.map(function(n){return n[0];});
const filtered = initial.filter(filterLogic);
Logger.log(initial); // [MWP, KBB, JET, JJB, KBB, MKGC, KBB]
Logger.log(filtered); // [KBB, KBB]
}
function filterLogic(name){
if(name == "KBB"){
return true;
} else {
return false;
}
}
The above code addresses the set criteria but does not include the counting of elements for the returning value from another column post-filter application.
How can we modify this process to incorporate the first row containing the text "KBB" in the filtered data? Are there alternative methods to achieve this?