Array of checkboxes to toggle on when another checkbox is checked

Question

I'm creating a google sheet for Pathfinder characters (2nd ed). The skill system is represented by ranks and is displayed on the standard character sheet as a row of checkboxes. If a rank is checked then all the ranks below it should also be checked and the boxes above should become unchecked.

In the example below, since 'E' is checked then 'T' must also be checked. If the player were to then check 'L' then 'M' should also become checked. In the opposite case, if the player were to uncheck 'T' then 'E' should also become unchecked.

In effect, there are only five possible combinations.

One can give a formula for checkboxes and I've used this one for 'T'. And I understand how one could cascade such formulas. But the formula isn't going to work in this case.

=IF(I11,TRUE,FALSE)

I could use a drop-down to the right of the checkboxes and set their states based on that, but I don't like the way that looks. It breaks the translation from character sheet to the spreadsheet.

I want something like an event that I can use to set values based on user action.

I don't think you can do what you want like this. If a cell has a formula in it, to determine if it should be checked or unchecked, then the user cannot also check/un-check the box. — IMTheNachoMan
@IMTheNachoMan, Agreed, but is there some mechanism to run a script if the user has clicked on a check box? — Display name
Ah. Yes you could do that. You can create an onEdit trigger to do what you need. Not sure how/why I didn't think of that. Do you have a sample Google Sheet? I can try to throw some code this weekend. — IMTheNachoMan

Raserhin Raserhin · Accepted Answer · 2020-03-05T15:31:29

I made a solution for you in Apps Script.

function onEdit(e) {
  var ss = e.source;
  var cell = e.range;

  var trainedCol = 1; // A
  var expertCol = 2;  // B
  var masterCol = 3;  // C
  var legendCol = 4;  // D

  if(cell.getColumn() >= trainedCol && cell.getColumn() <= legendCol){
    if(e.value == "TRUE"){
      // When you Check it checks all the column on the left 
      var newRange = cell.offset(0, - (cell.getColumn() - trainedCol) , 1, cell.getColumn() - trainedCol);

      newRange.check();
    }

    if(e.value == "FALSE"){
      // When you uncheck it unchecks all the columns on the right 
      var newRange = cell.offset(0, 1 , 1, legendCol - cell.getColumn());

      newRange.uncheck();

    }
  }
}

You can test this solution in this spreadsheet.

Basically the logic behind is two options.

When you check a box, it will check all the other boxes to the left
When you uncheck a box, it will uncheck all the other boxes to the right

If you look at your code you will notice that I have hardcoded the positions of the columns (trained, expert, master, legend), and assumed that the checkboxes would be next to each other where trained is the first and legend the last.

So if your checkboxes are gonna be in another column just change the numbers in the code.

But for the case you have provided this solution should suffice you.

Array of checkboxes to toggle on when another checkbox is checked

1 Answers

Reference