Google Sheets Filter View Script-CodePudding

I want to create filter views automatically for each value in the group column in this spreadsheet.

Is this possible via a script? I think that previously Sheets did not support filterview programatically.

CodePudding user response：

Here is a google sheet's script that I tested. It will hide all rows in your spreadsheet where the group column value does not match the value entered into Cell "D1"

Just change the datasource for the variable "filterValue" to whatever location you want to get your filter value from.

function filterRows() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var data = sheet.getDataRange().getValues();
  var filterValue = sheet.getRange("D1").getValue();
  sheet.showRows(1,sheet.getMaxRows());
  for(var i = 1; i < data.length; i  ) {
    if(data[i][2] != filterValue) {
      sheet.hideRows(i   1);
      }
    }
}

In this script the number 2 in data [i][2] represents the column index of the group column in your example spreadsheet, so if the group column in your actual spreadsheet is in a different location, you will need to change the number 2 to the correct index

Here is a developer source with some additional information on filtering from google sheets: spreadsheet.dev

CodePudding user response：

You can use the method createFilter on a range to filter:

let ss = SpreadsheetApp.getActiveSheet();
let range = ss.getRange("A1:C20");

// Creates a new filter and applies it to the range A1:C20 on the active sheet.
function createFilter() {
  range.createFilter();
}
// Gets the filter and applies criteria that only shows cells that aren't empty.
function getFilterAddCriteria() {
  let filter = range.getFilter();
  let criteria = SpreadsheetApp.newFilterCriteria()
    .whenCellNotEmpty()
    .build();
  filter.setColumnFilterCriteria(2, criteria);
}

To add the criteria you can use the methods on FilterCriteriaBuilder