I'm trying to create a custom menu in my spreadsheet that will have 2 items:
1st item:
- Disable all the active filters in a specific sheet.
- Show the last empty row in column B (in my example sheet: cell B15)
- Add the value "> Add Content here <" in the last empty cell from column B
2nd item: Filtering the data using filter from column B & C and keep only the data for the last 60 days (It would be nice if the code is dynamic).
I've started to work on the first item, but I'm stuck at the second part. Here is a sample of my spreadsheet with GAS to help you understand what I want to achieve.
https://docs.google.com/spreadsheets/d/11ptkjp5UySKc0iwZTu3QUrJVKds6t4W5eMD39XVUbME/edit#gid=0
Any help on this would be appreciated.
CodePudding user response：
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, 8and2022, 9are shown. But, when you want to show2022, 7,2022, 8and2022, 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());