设置过滤器在谷歌工作表使用值(Set Filters in Google Sheet using a

我已经有20列，其中一列是“状态”谷歌的工作表。 我想过滤器设置到片材以滤除（排除）具有状态=“已完成”的所有行。 我该怎么做？ 在此先感谢您的帮助。

这里是我到目前为止的代码...

function filterSheet() {
  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

      //how do I include condition here to filter out rows where columnIndex 2 (column C) = 'Completed'?

    }
  };

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

Answer 1:

你可以试试这个：

function filterSheet() {
  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);
}

在排序的更多信息： https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria

文章来源: Set Filters in Google Sheet using a value