I managed to create script to delete rows when it is filtered but not managed to create clear content function when its filtered. Code as below:
var sheet = SpreadsheetApp.getActive().getSheetByName('data');
var lastRow = sheet.getLastRow()
var lastColumn = sheet.getLastColumn()
function deleterow_d3() {
var RANGE = sheet.getRange(1, 1, lastRow, lastColumn)
var rangeVals = RANGE.getValues();
if (sheet.getFilter() != null) {
sheet.getFilter().remove()
}
var filteredRange = RANGE.createFilter()
var millis_per_day = 1000 * 60 * 60 * 72; // 3 days
var today = new Date();
var criteria = SpreadsheetApp.newFilterCriteria().whenDateEqualTo(new Date(today.getTime() - millis_per_day)).setHiddenValues(['']).build();
sheet.getFilter().setColumnFilterCriteria(5, criteria); // which column to filter from, criteria is the date
var deletedrows = lastRow - 3
sheet.deleteRows(2, deletedrows) // to delete rows
sheet.getFilter().remove()
}
Data is filtered by date then all content within the filter is removed. Instead deleting the rows, i want to clear the content only.
Try to change:
sheet.deleteRows(2, deletedrows) // to delete rows
to:
for (var i = 2; i < deletedrows; i++) {
if (!sheet.isRowHiddenByFilter(i)) {
sheet.getRange(i,1,1,lastColumn).clearContent();
}
}
It should work fine for several rows, for a couple dozens rows maybe. But if you have several dozens or hundreds rows to clear this approach is not efficient, it will need a more complicated solution.
Update
Here is the example how it can be done in more smart way:
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var three_days_ago = new Date().setHours(0,0,0,0) - 1000 * 60 * 60 * 72;
var data = range.getValues();
data.forEach((row,i) => {
if (new Date(row[4]).getTime() == three_days_ago)
data[i] = new Array(row.length);
})
range.setValues(data);
}
It takes all the cells from the sheet as a 2d array, cleans rows of the array if date in 5th column == three days ago, and puts the array back on the sheet.