Making an Apps Script function run on checkbox edits-CodePudding

As a fairly new user of Apps Script I'm trying to add a functionality to one of my sheets where I want to hide rows based on checkbox values. I'm using two tabs - one is the 'Settings' tab where I make the selection, the other is the 'Results' tab which has the actual data where I want the rows to get hidden. I've added four helper columns in my results tab that copy the checkmark values to that sheet, so the script only has to look at the values in this particular tab. I've stolen and slightly amended the script from this URL and now my script looks like this:

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Custom Filter")
    .addItem("Filter rows", "filterRows")
    .addItem("Show all rows", "showAllRows")
    .addToUi();
}

function filterRows() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Results");
  var data = sheet.getDataRange().getValues();
  for(var i = 1; i < data.length; i  ) {
    //If column A (1st column, hence the [0]) in the 'Results' tab is unchecked (false) then hide the row.
    if(data[i][0] === false) {
      sheet.hideRows(i   1);
    }
  }
}

function showAllRows() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Results");
  sheet.showRows(1, sheet.getMaxRows());
}

It's working as it should, the only change I want to make is that I'd rather not use the menu item to filter my rows, but instead I'd like the rows to get automatically hidden as I'm making changes to my selections in the checkboxes. I've tried looking online for an answer but haven't been able to find something that fits my needs. Hopefully someone here can help me out!

CodePudding user response：

You can listen for an edit event when a user changes the value of a cell in a sheet (i.e. checks a box) using the onEdit trigger.

CodePudding user response：

Assume checkbox is in Sheet1!A1

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == Sheet1 && e.range.columnStart == 1 && e.range.rowStart == 1 && e.value == "TRUE"){
    e.range.setValue("FALSE");
    //run you function here
  }
}