使用值在Google表格中设置过滤器 [英] Set Filters in Google Sheet using a value

问题描述

我有一个Google表格，其中有20列，其中一列是状态".我想将过滤器"设置为工作表，以过滤出(排除)所有状态为已完成"的行.我该怎么做?预先感谢您的帮助.

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'. How can I do it? Thanks in advance for your help.

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

Here's the code I have so far...

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

推荐答案

您可以尝试以下操作:

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

有关FilterCriteria的更多信息: https://developers.google. com/sheets/api/reference/rest/v4/FilterCriteria

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

这篇关于使用值在Google表格中设置过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！