Automatically Refreshing Basic Filter on Google Spreadsheet using Google Apps Script

GasTips / Spreadsheet
Google Apps Script / Spreadsheet

Gists

This is a sample script for automatically refreshing the basic filter on Google Spreadsheet using Google Apps Script.

Description

A sample situation is as follows.

In this sample, the basic filter is set to columns “B” and “D”.

  • Column “B”: When the checkbox is checked, the row is hidden.
  • Column “D”: When the cell value is multiples of 3, the row is hidden. In this case, the custom function =MOD(E2,3)<>0 is used.

For example, under the condition that the basic filter is set to columns “B” and “D”, even when a checkbox of “B3” is checked, unfortunately, the basic filter is not automatically refreshed. In this case, it is required to manually refresh it.

In this post, I would like to introduce the method for automatically refreshing the basic filter using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet and set your sheet name, and save the script.

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const sheet = e.range.getSheet();
  if (sheet.getSheetName() != sheetName) return;
  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);
      }
    }
  }
}
  • In this sample, it supposes that your sheet in the Spreadsheet has the basic filter. Please be careful about this.

Testing

When this script is run, the following result is obtained. You can see that when the columns “B” and “D” are edited, the basic filter is automatically refreshed.

Reference

 Share!