Google Apps Script Filtering

Question

I have some data in google sheet which I want to filter based on a certain criteria and return a corresponding value from another column. Lastly, count the number of elements in the returned column. Here is a sample data:

Sample 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

Let's say I want to filter the data by searching for "KBB". I want to get all cells that contain the word "KBB" which should be three (3) cells. However, I am only getting two in return. The 1st row that contain two elements in a single cell is not included but it should be included. Lastly, count the elements in the returned column based on the criteria.

Here's the code I have tried:

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 is only for the criteria. Not included is the counting of elements for the returned value from another column after the filter is applied.

What should I do so I can include the first row that contains the text "KBB" as well in my filtered data. Is there any other way around this?

Make sure to add input and expected output as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. — TheMaster, Oct 18 '22 at 16:15

Cooper · Answer 1 · 2022-10-18T16:29:12.423

1

Code:

function searchForKBB(n = "KBB") {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  let o = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).createTextFinder(n).matchEntireCell(false).findAll().map(rg => [rg.getA1Notation()]);
  o.unshift(["Ranges"]);
  osh.getRange(1,1,o.length,o[0].length).setValues(o)
}

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

Results:

Ranges
A2
A4
A6

edited Oct 18 '22 at 16:29

answered Oct 18 '22 at 16:06

Cooper

59,616
6
23
54

the code definitely gets me the desired number of results (3 for KBB). however, the resulting value (A2, A4, A6) replaces the items in the second column which is not what I wanted to do. What I wanted was to return the corresponding value from the second column for every KBB found in the first column. Still, thank you for the answer! – JayM Oct 19 '22 at 03:21
I was just displaying the output you can move that anywhere you wish or just not use it. I don't recall you specifying where you wanted your output. – Cooper Oct 19 '22 at 10:36

Nikola Grujičić · Answer 2 · 2022-12-02T11:03:59.663

Maybe you can do this:

```
function getAllKBBs(){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var ss1 = ss.getSheetByName("YOUR_SHEET_NAME");
      var range = ss1.getRange(1,1,ss1.getLastRow(),4).getValues();
      output = whenTextContains("KBB", range, 1, 1);
      Logger.log(output.length);
} ```

where whenTextContains() function is in this repository https://github.com/NikolaPlusEqual/GoogleAppsScriptFilters/blob/main/Functions

Or, you can copy this into you code and call above function:


function letterToColumn(letter){
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++)
  {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}
////// source for letterToColumn() function :
////// https://stackoverflow.com/questions/21229180/convert-column-index-into-corresponding-column-letter

var setData = {}

function whenTextContains(txt, rng, col, targetCol = 0){
  if (typeof col == "number" && typeof txt == "string"){
    setData.col = col;
    setData.txt = txt;
  }
  else{
    return;
  }
  var output = rng.filter(wtc);
  if(targetCol == 0){
    return output;
  }
  else if(typeof targetCol == "number"){
    var result = output.map(function (item) {
      return item[targetCol-1];
    });
    return result;
  }
  else if(typeof targetCol == "string"){
    var targetnum = letterToColumn(targetCol);
    var result = output.map(function (item) {
      return item[targetnum-1];
    });
    return result;
  }
  else{
    return;
  }
}

function wtc(ar){
  var txt = setData.txt;
  var col = setData.col - 1;
  var str =  ar[col].toString();
  return str.includes(txt);
}

Google Apps Script Filtering

2 Answers2