|
/** |
|
* Activate the filtering on the active sheet |
|
* @method setFilter |
|
* @param {Number} columnId Column number you want to filter out (start at 0) |
|
* @param {Array} filteredValues Array of value to filter out |
|
*/ |
|
function setFilter(columnId,filteredValues) { |
|
var ss = SpreadsheetApp.getActiveSpreadsheet(); |
|
|
|
var filterSettings = {}; |
|
|
|
// The range of data on which you want to apply the filter. |
|
// optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex |
|
filterSettings.range = { |
|
sheetId: ss.getActiveSheet().getSheetId() |
|
}; |
|
|
|
// Criteria for showing/hiding rows in a filter |
|
// https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria |
|
filterSettings.criteria = {}; |
|
var columnIndex = columnId; |
|
filterSettings['criteria'][columnIndex] = { |
|
'hiddenValues': filteredValues |
|
}; |
|
|
|
var request = { |
|
"setBasicFilter": { |
|
"filter": filterSettings |
|
} |
|
}; |
|
Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId()); |
|
} |
|
|
|
/** |
|
* Reset the filtering on the active sheet |
|
* @method resetFilter |
|
*/ |
|
function resetFilter(){ |
|
let ss = SpreadsheetApp.getActiveSpreadsheet(); |
|
let ssId = ss.getId(); |
|
let dataSheet = ss.getActiveSheet(); |
|
let lastRow = dataSheet.getLastRow(); |
|
let lastColumn = dataSheet.getLastColumn(); |
|
let sheetId = dataSheet.getSheetId(); |
|
|
|
var filterSettings = { |
|
"range": { |
|
"sheetId": sheetId, |
|
"startRowIndex": 0, |
|
"endRowIndex": lastRow, |
|
"startColumnIndex": 0, |
|
"endColumnIndex": lastColumn |
|
} |
|
}; |
|
var requests = [{ |
|
"setBasicFilter": { |
|
"filter": filterSettings |
|
} |
|
}]; |
|
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId); |
|
} |
|
|
|
/** |
|
* Clear / remove all filters |
|
* @method clearFilter |
|
*/ |
|
function clearFilter() { |
|
var ss = SpreadsheetApp.getActiveSpreadsheet(); |
|
var ssId = ss.getId(); |
|
var sheetId = ss.getActiveSheet().getSheetId(); |
|
var requests = [{ |
|
"clearBasicFilter": { |
|
"sheetId": sheetId |
|
} |
|
}]; |
|
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId); |
|
} |
|
|
|
/** |
|
* Get all the filtered row in an array |
|
* @method getIndexesOfFilteredRows |
|
*/ |
|
function getIndexesOfFilteredRows() { |
|
var ss = SpreadsheetApp.getActiveSpreadsheet(); |
|
var ssId = ss.getId(); |
|
var sheetId = ss.getActiveSheet().getSheetId(); |
|
var hiddenRows = []; |
|
|
|
// limit what's returned from the API |
|
var fields = "sheets(data(rowMetadata(hiddenByFilter)),properties/sheetId)"; |
|
var sheets = Sheets.Spreadsheets.get(ssId, {fields: fields}).sheets; |
|
|
|
for (var i = 0; i < sheets.length; i++) { |
|
if (sheets[i].properties.sheetId == sheetId) { |
|
var data = sheets[i].data; |
|
var rows = data[0].rowMetadata; |
|
for (var j = 0; j < rows.length; j++) { |
|
if (rows[j].hiddenByFilter) hiddenRows.push(j); |
|
} |
|
} |
|
} |
|
return hiddenRows; |
|
} |