Compose a formula for filtering column (.whenFormulaSatisfied(……) )-CodePudding

I have a column with names. (Example: Mike worked - 3 times, then John - 2 times, then again Mike .. etc.). Groups of names are vertically separated by an empty row (Fig. 1). I want to run the filter by app script on a specific name - let's say Mike. But in the received data, I need the groups of names Mike to be vertically separated by ONLY one empty row - not like (in Fig. 2). I think it is possible to discard extra empty rows by applying the formula - I showed an example of its work in column 2 (Fig. 3). But I don't understand how to write such a formula in my code (and is it possible at all ?) ! :

var textToFilter = "Mike";
var FilterCriteria = SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied("=AND(A1:A1000="   "\""   textToFilter   "\""   ")");
filter.setColumnFilterCriteria(1, FilterCriteria);

[Fig.1]

[Fig.2]

[Fig.3]

CodePudding user response：

Just like conditional formatting, you only need to reference the starting row, which is always the second row (after the header row as mentioned in the comments by alex che), in case of a filter. The formula is automatically propagated to the filtered data range. Therefore, If the filter starts at A1, the formula should be

=OR(A2="Mike", AND(A2="", A3="Mike"))

const textToFilter = "Mike";
const FilterCriteria = SpreadsheetApp
  .newFilterCriteria()
  .whenFormulaSatisfied(
    `=OR(A2="${textToFilter}",
     AND(A2="", A3="${textToFilter}"))`
  );
filter.setColumnFilterCriteria(1, FilterCriteria);