Skip to content

Instantly share code, notes, and snippets.

@clawfire

clawfire/Code.gs

Created Mar 11, 2021
Embed
What would you like to do?
/**
* 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;
}
function clearChecking() {
// On récupère la feuille Check-in
let checkingSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('🕰 Check-In');
// On selectionne cette feuille
SpreadsheetApp.setActiveSheet(checkingSheet);
// On utilise la fonction setFilter pour filtrer toute les valeurs qui sont TRUE
// En conséquence on ne verra plus que les FALSE et null
setFilter(3, ['TRUE']);
// On récypère toutes le n° des lignes qui ont été filtrées (donc = TRUE)
let checkingRows = getIndexesOfFilteredRows();
// On supprime le filtre car on en a plus besoin
clearFilter();
// S'il n'y a rien à supprimer on sort.
if (checkingRows.length == 0) return null;
// On affiche la tableau de ligne dans la console.
// On voit qu'on a une progression croissante 1-2-3-4
Logger.log("Lignes à supprimer %s", checkingRows);
// On inverse les valeurs pour avoir un ordre décroissant 4-3-2-1
checkingRow = checkingRows.reverse();
// On itère sur chaque élément en commensant par la dernière ligne
for (item in checkingRows) {
// On corrige la valeur de la ligne
let row = checkingRows[item] + 1;
// On log dans la console la ligne qu'on va supprimer
Logger.log("Supression de la ligne %s", row);
// On supprime la ligne
checkingSheet.deleteRow(row);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment