Google Apps Scripts / Google Sheet - Disable all the active filters, Show a specific cell and filter again based on criteria

I believe your goal is as follows.

• You want to put a value of > Add Content here < to the 1st empty row of column "B".
• You want to show the rows that the year and month values of columns "B" and "C" are the last 60 days.
• You want to achieve this filter using the basic filter.

When I saw your sample script, I found the following sample script.

// first item - working
function prepareForContentAddition() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Dashboard');
  var filter = sheet.getFilter();
  if (filter !== null) {  
    var range = filter.getRange(); 
    var firstColumn = range.getColumn();
    var lastColumn = range.getLastColumn();
    for (var i = firstColumn; i < lastColumn; i++) {
      filter.removeColumnFilterCriteria(i);
    }
  }
// Show the last empty row / blank cell in Column B and add "> Add Content here <" in it
  var lastRow = sheet.getRange('Dashboard!B3:B').getLastRow();
  sheet.getRange(lastRow, +1,1).activate();
}

// Apply 2 months filtering starting from today
function backToFiltering() {
}

When this script is modified, how about the following modification?

Modified script:

function prepareForContentAddition() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Dashboard');
  var filter = sheet.getFilter();
  if (filter !== null) {
    var range = filter.getRange();
    var firstColumn = range.getColumn();
    var lastColumn = range.getLastColumn();
    for (var i = firstColumn; i < lastColumn; i++) {
      filter.removeColumnFilterCriteria(i);
    }
  }

  // I added below script.
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) { // Ref: https://stackoverflow.com/a/44563639
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };
  var row = sheet.get1stNonEmptyRowFromBottom(2);
  sheet.getRange(row + 1, 2).setValue("> Add Content here <");
}

And

function backToFiltering() {
  const date = new Date();
  date.setDate(date.getDate() - 60);
  const year = date.getFullYear();
  const month = date.getMonth() + 1;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Dashboard');
  var filter = sheet.getFilter();
  if (filter !== null) {
    filter.setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenNumberGreaterThanOrEqualTo(year).build()).setColumnFilterCriteria(3, SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(month).build());
  }
}

• In this modification, I think that your expected situation can be obtained. 2022, 8 and 2022, 9 are shown. But, when you want to show 2022, 7, 2022, 8 and 2022, 9, please modify them as follows.

  • From

      filter.setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenNumberGreaterThanOrEqualTo(year).build()).setColumnFilterCriteria(3, SpreadsheetApp.newFilterCriteria().whenNumberGreaterThan(month).build());
    

  • To

      filter.setColumnFilterCriteria(2, SpreadsheetApp.newFilterCriteria().whenNumberGreaterThanOrEqualTo(year).build()).setColumnFilterCriteria(3, SpreadsheetApp.newFilterCriteria().whenNumberGreaterThanOrEqualTo(month).build());
    

References:

• Class FilterCriteriaBuilder
• setColumnFilterCriteria(columnPosition, filterCriteria)