如何更简便地更新当前活动筛选器范围并通过onOpen()函数重应用筛选设置？

Hey Roman, great question! Your initial plan to save filter criteria, remove the existing filter, and reapply the settings is totally valid—but we can streamline this code to be cleaner and more efficient without losing any functionality.

Simplified Implementation #

Instead of manually handling every column (including those without filters), we can collect only the columns that have active criteria, then quickly reapply them after resetting the filter. Here's how:

function onOpen() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const existingFilter = activeSheet.getFilter();
  
  // Exit early if there's no filter to reapply
  if (!existingFilter) return;
  
  // Save criteria only for columns that have filter settings
  const filterCriteria = new Map();
  const filterRange = existingFilter.getRange();
  const totalColumns = filterRange.getNumColumns();
  
  for (let colIndex = 1; colIndex <= totalColumns; colIndex++) {
    const criteria = existingFilter.getColumnFilterCriteria(colIndex);
    if (criteria) {
      filterCriteria.set(colIndex, criteria);
    }
  }
  
  // Reset the filter and reapply saved criteria
  existingFilter.remove();
  const newFilter = filterRange.createFilter();
  
  filterCriteria.forEach((criteria, column) => {
    newFilter.setColumnFilterCriteria(column, criteria);
  });
}

Why This Works Better #

• Less unnecessary work: We only store criteria for columns that actually have filters, skipping empty columns entirely.
• Cleaner logic: Using a Map keeps our column-criteria pairs organized, making the reapplication step straightforward.
• 1:1 replication: This preserves every detail of your original filter settings—no manual reconfiguration needed.

Quick Note on "Refreshing" Filters #

Unfortunately, Google Apps Script doesn’t have a built-in "refresh filter" method that updates results without resetting the filter. That’s why removing and re-applying the saved criteria is the most reliable way to ensure updated cells are properly filtered when the sheet opens.

This script will run automatically whenever someone opens the spreadsheet, ensuring your filter settings are always applied to the latest data. Just make sure you’ve authorized the script to run in your spreadsheet!

内容的提问来源于stack exchange，提问作者Roman