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?

score 16 · Answer 1 · answered May 05 '18 at 21:40

16

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:

answered May 05 '18 at 21:40

tehhowch

9,645
4
24
42

Once applied, how do we get the filtered data back into the app-script. When I do `filter.getRange().getValues()`, I get all the data, instead of the filtered ones – daydreamer Oct 21 '19 at 15:36
@daydreamer Check this article please https://tanaikech.github.io/2019/11/08/retrieving-values-from-sheet-filtered-by-slicer-in-spreadsheet-using-google-apps-script/ You can check if specific row is hidden with applied filter `isRowHiddenByFilter ` – Стас Пишевский Jul 25 '20 at 16:38

score 3 · Answer 2 · answered Sep 01 '17 at 13:51

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);
}

score 1 · Accepted Answer · answered Aug 29 '13 at 19:47

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.

answered Aug 29 '13 at 19:47

eksperts

149
4

4

Updated answer is below. Keep Scrolling. – technazi Feb 01 '19 at 00:59

Max Makhrov · Answer 4 · 2022-04-08T09:31:13.183

Please use this script if you want:

Create a new filter
Create a new filter based on the existing filter and preserve criteria
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);
}

Enable data filters on a range with Google Apps Script

4 Answers4

The Script

Usage

Linked