I have a spreadsheet and need a app script that automatically applies a filter to a specific range and hides values in a defined column.

Screenshot of Spreadsheet

I have previously used the below script, however, the filter range applies to the entire sheet.

My new requirement is for the filter range to only be applied to a specific range (in this case B3:L)

function setFilter() {
  var ss = SpreadsheetApp.getActive();
  var tab = ss.getSheetByName("Sheet1")
  var filter = {};
  filter.range = {
    sheetId: tab.getSheetId()
  };

  filter.criteria = {};
  // Which column to add the filter to
  var columnIndex = 0;
  filter['criteria'][columnIndex] = {
    'hiddenValues': ["✘"]
  };
  var request = {
    "setBasicFilter": {
      "filter": filter
    }
  };
  Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}

How can I specify the range using this existing script?

I have attempted adding the following, but this does not work:

  var range = spreadsheet.getRange("B3:L")

CodePudding user response：

I believe your goal is as follows.

• You want to create a basic filter to cells "B3:L". When the cell value is ✘, you want to hide the row.
• You want to achieve this using Sheets API with Google Apps Script.

In order to achieve your goal, when your showing script is modified, how about the following modification?

Modified script:

function setFilter() {
  var ss = SpreadsheetApp.getActive();
  var tab = ss.getSheetByName("Sheet1")
  var filter = {};
  filter.range = {
    sheetId: tab.getSheetId(),
    startRowIndex: 2,
    startColumnIndex: 1,
    endColumnIndex: 12
  };
  filterSpecs = [];
  for (var i = filter.range.startColumnIndex; i <= filter.range.endColumnIndex; i  ) {
    filterSpecs.push({ columnIndex: i, filterCriteria: { hiddenValues: ["✘"] } });
  }
  filter.filterSpecs = filterSpecs;
  var request = {
    "setBasicFilter": {
      "filter": filter
    }
  };
  Sheets.Spreadsheets.batchUpdate({ 'requests': [request] }, ss.getId());
}

• In this case, please set the gridrange to the range.
• Please set hiddenValues: ["✘"] to each column.
• When this script is run, when the cell value is ✘, the row is hidden. And, the basic filter is set to "B3:L".

Reference:

• SetBasicFilterRequest