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?

Solution 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.

Solution 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

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

Comments

[technazi]

Updated answer is below. Keep Scrolling.

[daydreamer]

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 Check this article please tanaikech.github.io/2019/11/08/… You can check if specific row is hidden with applied filter isRowHiddenByFilter