Set Filters In Google Sheet Using A Value

I've a Google Sheet that has 20 columns, one of the columns is 'Status'. I would like to set Filters to the sheet to filter OUT (exclude) all rows that have Status = 'Completed'. H

Solution 1:

You can try this:

functionfilterSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var aSheet = ss.getSheetByName('Target Sheet');
  var lastRow = aSheet.getLastRow();
  var lastColumn = aSheet.getLastColumn();
  var sheetId = aSheet.getSheetId();

  var filterSettings = {
    "range": {
      "sheetId": sheetId,
      "startRowIndex": 0,
      "endRowIndex": lastRow,
      "startColumnIndex": 0,
      "endColumnIndex": lastColumn    
    }
  };

  filterSettings.criteria[columnIndex] = {
    "hiddenValues": ["Completed"]
  };

  var requests = [{
    "setBasicFilter": {
      "filter": filterSettings
    }
  }];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

More info on FilterCriteria: https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria