How to update filters on dynamic data tables-CodePudding

I have some filters on columns D:K, and which show dynamic data depending on validation validation box as you can see in the gif below. When I choose "Active" 6 rows appear, then when I choose "Sold", more than 6 rows appear. However the new rows are out of filters range.

Problem: How can I update the filters automatically upon data validation change to include more or less rows?

Example sheet: https://docs.google.com/spreadsheets/d/1KthGhCQ0Mm2LFBk_eh_LS7jyXadVuyJpfu9sYm7SzpA/edit?usp=sharing

CodePudding user response：

I believe your goal is as follows.

You want to refresh the basic filter when the dropdown list is changed and the values of columns "D" to "K" are updated.

In your situation, I thought that this sample script of this thread might be useful. But, in your situation, when the modification points of the script might be a bit complicated. So, here, I would like to propose the modified script of the sample as a sample script.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet, please set the sheet name and the cell A1Notation of the dropdown list, and save the script. When you run this script, please edit the dropdown list. By this, this script is automatically run.

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const dropDownCell = "B3"; // Please set the range of dropdown list.

  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.getA1Notation() != dropDownCell) return;

  // Here, the existing basic filter is refreshed.
  const filter = sheet.getFilter();
  if (filter) {
    const range = filter.getRange();
    for (let i = range.getColumn(), maxCol = range.getLastColumn(); i <= maxCol; i  ) {
      const filterCriteria = filter.getColumnFilterCriteria(i)
      if (filterCriteria) {
        filter.setColumnFilterCriteria(i, filterCriteria);
      }
    }
  }
}

About const dropDownCell = "B3";, unfortunately, from your showing sample image, I cannot understand the cell range of the dropdown list. So, please set this and save the script.

References:

Added:

From your provided sample Spreadsheet, when I saw it, it seems that the values are not filtered while you add the basic filter. And, when the dropdown list is changed, the basic filter is refreshed by my script. From this situation, I thought that you might have wanted to set the basic filter to the data range. If my understanding is correct, please test the following sample script.

Sample script:

function onEdit(e) {
  const sheetName = "My Orders"; // Please set your sheet name.
  const dropDownCell = "B4"; // Please set the range of dropdown list.

  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.getA1Notation() != dropDownCell) return;

  // Here, the existing basic filter is refreshed.
  const filter = sheet.getFilter();
  if (filter) {
    filter.remove();
  }
  const values = sheet.getRange("D3:K"   sheet.getLastRow()).getValues();
  const row = values.length - [...values].reverse().findIndex(r => r.findIndex(c => c.toString() != "") > -1)   2;
  sheet.getRange("D3:K"   row).createFilter();
}

In this sample script, when you change the dropdown list, the basic filter is set to the data range of "D3:K".