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