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:
