Enable data filters on a range with Google Apps Script

Question

Is there a way to enable data filters by script in a Google Sheet for a certain range? Currently this can be doen manually, but I do not want to select a range, then click 'Data', and then turn on filter.

I know that the filter will remain in an existing sheet. However, I try to apply a filter for a new spreadsheet that is generated via scripting.

My idea is:

function foo() {
  var spreadsheet = SpreadsheetApp.getActive();
  var infoSheet = spreadsheet.insertSheet('sheetName', spreadsheet.getNumSheets());

  infoSheet.getRange(1, 1, 5, 5). -> enable filter?
  ...
}

How can I achieve my goal programmatically?

Answer 1

A short answer would be "No". It seems that GAS supports neither getting, nor setting Filter criteria for columns, at least according to this open issue.

An alternative solution would be to do the filtering and sorting on GAS side, and push the distinct results into different columns or sheets.

Answer 2

Filters are now also available in the native Spreadsheet Service, without needing to activate the Sheets REST API via "Advanced Services".

The above Sheets REST API method, adapted for native Apps Script:

function applyFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getActiveSheet();

  var toFilter = dataSheet.getDataRange();
  var filter = toFilter.createFilter();

  // Make some criteria to filter with.
  var fcb = SpreadsheetApp.newFilterCriteria();
  /* use FilterCriteria methods */
  fcb.whenCellNotEmpty();

  // Filter the range based on the 1st column:
  filter.setColumnFilterCriteria(1, fcb.build());
}

Further reading:

• Filter Reference
• FilterCriteria Reference

Answer 3

Now you can apply filters using google sheets advanced service. First you need to turn on the Google Sheets API. For that follow the steps as mentioned in the below link:

https://developers.google.com/sheets/api/quickstart/apps-script

After that you can use the following function to apply filters.

function applyFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var dataSheet = ss.getActiveSheet();
  var lastRow = dataSheet.getLastRow();
  var lastColumn = dataSheet.getLastColumn();
  var sheetId = dataSheet.getSheetId();

  var filterSettings = {
    "range": {
      "sheetId": sheetId,
      "startRowIndex": 0,
      "endRowIndex": lastRow,
      "startColumnIndex": 0,
      "endColumnIndex": lastColumn
    }
  };
  var requests = [{
    "setBasicFilter": {
      "filter": filterSettings
    }
  }];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

Answer 4

Please use this script if you want:

1. Create a new filter
2. Create a new filter based on the existing filter and preserve criteria
3. Create a filter with default bounds: row 1, column 1, last row, last column

The Script

/**
 * Creates/Updates filter
 * Preserves previously filtered data
 * 
 * Parameters are optional
 * 
 * @param {string}  sheet   (active)
 * @param {integer} row1    (1)
 * @param {integer} row2    (last)
 * @param {integer} column1 (1)
 * @param {integer} column2 (last)
 */
function createFilter_(sets) {

  // sheet
  var ss = SpreadsheetApp.getActive();
  var s;
  if (sets.sheet) {
    s = ss.getSheetByName(sets.sheet);
  } else {
    s = ss.getActiveSheet();
  }
  
  // existing filter?
  var filter = s.getFilter();

  // finding range boundaries 4 filter
  var columns = [], criterias = [], criteria;
  var column1, column2, row1;
  var row2 = sets.row2 || s.getMaxRows();
  if (filter) {
    // remember criterias
    var r = filter.getRange();
    row1 = r.getRow();
    if (!sets.row1 || row1 === sets.row1) {
      // use old filter boundaries  
      column1 = r.getColumn();
      column2 = r.getWidth() + column1 - 1;
      for (var i = column1; i <= column2; i++) {
        columns.push(i);
        criteria = filter.getColumnFilterCriteria(i);
        criterias.push(criteria);
      }
    } else {
      row1 = sets.row1
      column1 = sets.column1 || 1;
      column2 = sets.column2 || s.getMaxColumns();
    }
    
    // remove filter
    filter.remove();

  } else {
    // create new filter
    column1 = sets.column1 || 1;
    column2 = sets.column2 || s.getMaxColumns();
    row1 = sets.row1 || 1;
  }

  // range
  var range = s.getRange(
    row1,
    column1,
    row2 - row1 + 1,
    column2 - column1 + 1
  );

  // create filter
  var newfilter = range.createFilter();

  // adjust criterias if needed
  for (var i = 0; i < columns.length; i++) {
    if (criterias[i]) {
      newfilter.setColumnFilterCriteria(
        columns[I], 
        criterias[I]);
    } 
  }

  return '?? filter for range ' + range.getA1Notation();
}

Usage

function createNewFilter() {
  var sets = {
    sheet: 'Sheet2',
    row1: 5,
    row2: 50,
    column1: 2,
    column2: 8
  }
  var result = createFilter_(sets);
  console.log(result);
}

Update existing filter:

function updateExistingFilter() {
  var sets = {
    sheet: 'Sheet1'
  }
  var result = createFilter_(sets);
  console.log(result);
}