Google Sheets Filter View Script

S_hottl1 0
Viewed 26

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.

google-sheets google-apps-script
1 Answers

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

Justin Edwards0
Related