Google脚本过滤范围为一个值 [英] Google Script Filter Range for one value

问题描述

我想为第1列中仅包含特定值"Bob"的行筛选一个Google表格范围.到目前为止，我的代码可以让我滤除行中具有Bob的行1.我需要相反的是，我想返回第1列中仅包含"Bob"的行.因此，在其中filterSettings不是hiddenValues或类似
"filterSettings ['criteria'] [columnIndex] ==！ {'hiddenValues':[filterCriteria]"

I would like to filter a google sheet range for rows that only contain a particular value of "Bob" in Column 1. My code so far lets me filter out the rows that have Bob in Col 1. I need to opposite, I want to return the rows that only have "Bob" in col 1. So where the filterSettings is not the hiddenValues or something like
"filterSettings['criteria'][columnIndex] == !{'hiddenValues': [filterCriteria]"

我的查询类似于这一个，但是我不想使用过滤条件排除该行，我希望这些是剩下的唯一行.

My query is similar to this one, but I don't want to exlude the row with the filter criteria, I want these to be the only remaining rows.

Code.gs

var ss = SpreadsheetApp.getActiveSpreadsheet();
var filterCriteria = 'Bob';


    var filterSettings = {};

    filterSettings.range = {
        sheetId: ss.getSheetByName('Sheet1').getSheetId()
                            };

    filterSettings.criteria = {};
        var columnIndex = 0;
    filterSettings['criteria'][columnIndex] = {
        'hiddenValues': [filterCriteria]
                                               };

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

推荐答案

在示例示例中，您希望排除"Bob"以外的值.如果我的理解是正确的，那么该修改如何?

In your sample case, you want to exclude the values except for "Bob". If my understanding is correct, how about this modification?

• 使用type至filterCriteria.
  • type是TEXT_CONTAINS.值是鲍勃".
  • 通过这种方式，仅保留包括"Bob"在内的值.
  • Use type to filterCriteria.
    • type is TEXT_CONTAINS. And the value is "Bob".
    • By this, only values including "Bob" are left.

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    
    var filterCriteria = {"type":"TEXT_CONTAINS","values":[{"userEnteredValue":"Bob"}]}; // Modified
    
    var filterSettings = {};
    filterSettings.range = {sheetId: ss.getSheetByName('Sheet1').getSheetId()};
    filterSettings.criteria = {};
    var columnIndex = 0;
    
    filterSettings['criteria'][columnIndex] = {'condition': filterCriteria}; // Modified
    
    var request = {setBasicFilter: {filter: filterSettings}};
    Logger.log(JSON.stringify({'requests': [request]}))
    Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
    

    注意:

    • 此修改后的脚本假定您可以使用Sheets API.
    • SetBasicFilterRequest
    • ConditionType
    • SetBasicFilterRequest
    • ConditionType

    如果我误解了您的问题，请告诉我.我想修改它.

    If I misunderstand your question, please tell me. I would like to modify it.

    这篇关于Google脚本过滤范围为一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！