Home > Back-end >  FilterCriteria on Google Script to filter using checkboxes
FilterCriteria on Google Script to filter using checkboxes

Time:12-23

Im currently trying to move in bulk all the rows from a sheet where the checkbox is selected, I was trying to use "newFilterCriteria" but the one I use (found online) check on specific words while I want to use checkboxes.

The code so far does:

Get the source page, declare the filter criteria

  function create_filter(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Fridge"); // DECLARE THE SOURCE PAGE

  var range = sheet1.getRange("A5:J"); // DECLARE THE RANGE TO BE FILTERED LATER

  var filter = range.createFilter();

  var Filter_Criteria1 = SpreadsheetApp.newFilterCriteria().withCriteria(true); // HERE IS THE PROBLEM, THE ORIGINAL CODE SAYS "newFilterCriteria().whenNumberGreaterThan(1000);" BUT INSTEAD OF A NUMBER, I NEED A FILTER BASED ON CHECKBOXES BEING EITHER TRUE OR FALSE

  var add_filter1 = filter.setColumnFilterCriteria(1,Filter_Criteria1);

  Logger.log("Filter has been added.");

  var range = sheet1.getDataRange();

  var new_sheet = ss.insertSheet(); // CREATE THE DESTINATION TAB
  new_sheet.setName("TEST"); // NAME THE DESTINATION TAB AS "TEST"

  range.copyTo(new_sheet.getRange(1,1));

  filter.remove();
}

Any suggestions or help? Thank you! I tried looking around but havent get to find the right way to filter with the checkboxes.

Something else: Not sure if I can avoid an iteration since there are many rows to copy and it would be a slow process I think, is there a way to say something like a query such as "select all rows where column 1 is true"?

The image is just an example of the table.

Thanks!

CodePudding user response:

Use whenTextEqualTo true, for filtering in checkboxes:

const Filter_Criteria1 = SpreadsheetApp.newFilterCriteria().whenTextEqualTo('TRUE').build()

CodePudding user response:

Move Checked

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let a = [];
  let d = 0;
  vs.forEach((r, i) => {
    if (r[0] == true) {
      a.push(r)
      sh.deleteRow(i   2 - d  );
    }
  });
  if (a) {
    ss.insertSheet('Test').getRange(1, 1, a.length, a[0].length).setValues(a);
  }
}
  • Related