CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Lionel
Lionel

Reputation: 59

Google Sheets: Script to sort and filter

I have this sheet:

Personal finances sheet

I have this script to "onEdit" sort the A and B columns:

function onEdit(event){

  const ssApp = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ssApp.getSheetByName("Adispo");
  const editedCell = sheet.getActiveCell();
  const columnToSortByDate = 2;
  const columnToSortByCheckbox = 1;
  const tableRange = "A5:G1000";

  if(editedCell.getColumn() == columnToSortByDate || editedCell.getColumn() == columnToSortByCheckbox){   
    let range = sheet.getRange(tableRange);
    range.sort( [
      { column : 1, ascending: false },
      { column : 2, ascending: false }
    ] );
  }
}

Now I would like not to sort the A column, but filter it, in order not to show the checked checkboxes:

Wanted filtered view

It seems there's something to do with newFilterCriteria and setColumnFilterCriteria, but I really can't figure it out.

May someone help me?

Thanks!

Upvotes: 0

Views: 1494

Answers (1)

RemcoE33
RemcoE33

Reputation: 1610

This will get you started. The reference you already found yourself. I think this is enough for you to adjust to your needs.

function createFilter() {
  //Creating new criteria
  const filter = SpreadsheetApp.newFilterCriteria()
    .whenTextEqualTo('FALSE')
    .build();

  //Defining the sheet and range.
  const range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data').getRange('A1:B');

  //Get current filter and remove (you cannot set a filter when a filter is active)
  range.getFilter().remove();

  //Setting new filter on column 2 (B)
  range.createFilter()
    .setColumnFilterCriteria(2, filter);

}

Upvotes: 1

Related Questions