CODEWITHSUNDEEP
google-apps-scriptfilter
Sherry
Sherry

Reputation: 25

How to prevent filter from hiding specific row and header?

I have come out with below function to filter the row according to the drop down list in cell G2. However, it also hide my header as well. Imgur

Is it possible to exempt the row with "all sites" in column D from the filter? I need it to be shown at all time no matter what i select from the drop down list. before filter :Imgur

my goal is to achieve: selection made in drop down list will show all sites and those row containing what was chosen. Example: Hub 01 was selected from drop down list. Location in column D that does not include hub 01 will be hidden. Only showing row for all sites and contain Hub 01. Imgur

function hideRow () {

var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('Risk register'); // the sheet name

var cell2 = sheet.getRange('G2').getValue();

var criteria = SpreadsheetApp.newFilterCriteria().whenTextContains(cell2);
sheet.getFilter().setColumnFilterCriteria(4, criteria);
}

Upvotes: 1

Views: 420

Answers (1)

Tanaike
Tanaike

Reputation: 201388

I believe your goal as follows.

  • You want to reflect the basic filter to the range of "D4:D" on the sheet of Risk register.
  • You want to retrieve the value for searching from the cell "G2".
  • When the value of "G2" is "All site", you want to show the rows which are "All site" in the column "D".
  • When the value of "G2" is "Hub 01", you want to show the rows which have "Hub 01" and "All site" in the column "D".

For this, how about this answer?

Modification points:

  • In order to reflect the basic filter to the range of "D4:D", the filter is set to the range of "D3:D".
  • In order to show the rows which have "Hub 01" and "All site", the custom formula is used.
    • In this case, I referred this thread. Ref
  • In order to run the script when the dropdown list is selected, the OnEdit event trigger is used.
    • In this case, the simple trigger can be used.

Modified script:

Please copy and paste the following script to the script editor and save it. And, please change the value of the dropdown list of the cell "G2" on the sheet of Risk register. By this, the script is run.

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() == "Risk register" && range.getA1Notation() == "G2") {
    const value = e.value;
    let criteria = SpreadsheetApp.newFilterCriteria();
    criteria = value.toLowerCase() == "all site"
      ? criteria.whenTextEqualTo(value)
      : criteria.whenFormulaSatisfied(`=REGEXMATCH(D4,"All site|${value}")`);
    const sheetFilter = sheet.getFilter();
    if (sheetFilter) sheetFilter.remove();
    sheet.getRange("D3:D").createFilter().setColumnFilterCriteria(4, criteria.build());
  }
}

Note:

  • This script is run by the OnEdit event trigger. So when you directly run the function of onEdit() by the script editor, an error occurs. Please be careful this.
  • Please use this script with V8.

References:

Upvotes: 1

Related Questions