CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
Damien
Damien

Reputation: 373

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

I'm trying to create a custom menu in my spreadsheet that will have 2 items:

1st item:

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://script.google.com/u/0/home/projects/1rVwI5QMA35PksB9csKvMhxWpCHeLEbdKAo5UWb8y9GFGtdHpHsOwkva6/edit

https://docs.google.com/spreadsheets/d/11ptkjp5UySKc0iwZTu3QUrJVKds6t4W5eMD39XVUbME/edit#gid=0

Any help on this would be appreciated.

Upvotes: 1

Views: 733

Answers (1)

Tanaike
Tanaike

Reputation: 201338

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:

Upvotes: 2

Related Questions