Clear content for filtered data-CodePudding

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.

CodePudding user response：

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.