CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Amit Jain
Amit Jain

Reputation: 3

How to activate a filter only when a cell is not blank?

Below is a script that activates and creates a filter when cell 'B2' is edited. But I want to add a clear function to clear all filters when cell 'B2' is Blank. This script should work when cell 'B2' is not empty:

function onEdit(e){
  var sheetName = "Sheet1";
  var editCell = "B2";

  var range = e.range;
  var sheet = range.getSheet();
  if (range.getA1Notation() == editCell && sheet.getSheetName() == sheetName) {
    updatefilter(sheet);
  } else {
    // do something
  }
}

function updatefilter(sheet) {
  sheet.getRange('B3').activate();
  var criteria = SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied("=(B3:B=$B$2)").build();
  var filter = sheet.getFilter();
  if (!filter) {
    filter = sheet.getRange("A3:H").createFilter();
  }
  filter.setColumnFilterCriteria(2, criteria);
};

Any idea how to add a "Clear All" Filter when the cell is blank?

Here is a working link to a Spreadsheet -

https://docs.google.com/spreadsheets/d/1HT-I59RZ0qe-1jzQ2TcRsWva50I74FKSo4PvpxujH2g/edit?usp=sharing

Upvotes: 0

Views: 793

Answers (1)

Marios
Marios

Reputation: 27348

Explanation:

You can add an if condition to check whether the user deleted the current value of B2 or replaced it with a non-empty value.

  • If the user replaced the value of B2 with a non-empty value, execute the updatefilter function.

  • if the user deleted the current value of B2, clear the filter in Sheet1.

Solution:

function onEdit(e){
  var sheetName = "Sheet1";
  var editCell = "B2";

  var range = e.range;
  var sheet = range.getSheet();
  if (range.getA1Notation() == editCell && sheet.getSheetName() == sheetName) {
     
     // *** New code starts ***
     if(e.range.getValue()!=''){
        updatefilter(sheet);
     }
     else {
        sheet.getFilter().remove();
     };
     // *** New code ends***
}
}

function updatefilter(sheet) {
  sheet.getRange('B3').activate();
  var criteria = SpreadsheetApp.newFilterCriteria().whenFormulaSatisfied("=(B3:B=$B$2)").build();
  var filter = sheet.getFilter();
  if (!filter) {
    filter = sheet.getRange("A3:H").createFilter();
  }
  filter.setColumnFilterCriteria(2, criteria);
};

Upvotes: 1

Related Questions