2
votes

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());
1

1 Answers

2
votes

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

Modification points :

  • Use type to filterCriteria.
    • type is TEXT_CONTAINS. And the value is "Bob".
    • By this, only values including "Bob" are left.

Modified script :

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

Note :

  • This modified script supposes that you can use Sheets API.

References :

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