Google Sheets - Allow only one checkbox to be checked when the boxes are placed in specific cells across a wide range

Question

I need a simple script for Google Sheets to allow only one of 6 checkboxes to be checked at a time. The end user will check a checkbox and should see the one they checked before turn unchecked or FALSE when they check the new one. The "problem" for a newbie like is me that the checkboxes will be asymmetrically located. They are in cells D14, D30, J14, J32, P14 and P30. I also need to duplicate this sheet a number of times, so preferably the script should work on any of the duplicates I make the same way. The cells will always be these six cells in every sheet.

I've seen many posts about this subject and tried to use the example scripts to my problem without success, so I want to ask specifically about my spreadsheet.

Here is a link to a draft version of my sheet with the locations of the checkboxes:

https://docs.google.com/spreadsheets/d/1PtxetS-Gyv7LyMD19yF3NuWZ24RvqUjyK42SmQTttiU/edit?usp=sharing

Thank you, Lassi

Answer 1

You can set up an onEdit() trigger that will:

1. Check whether the cell edited is a checkbox (by verifying its range).

2. Check that the new value for the cell is TRUE (the checkbox has been enabled).

3. In case both of 1 and 2 apply, unset the rest of the defined checkboxes in the same sheet.

Note that the execution of the script may be a bit slow (due to Google Apps Script limitations) and that the script will work independently for each of the Sheets in your workbook.

Code

var CHECKBOX_CELLS = ["D14", "J14", "P14", "D30", "J32", "P30"];

function onEdit(e) {
  var range = e.range;

  var checkboxIndex = CHECKBOX_CELLS.indexOf(range.getA1Notation());

  if (checkboxIndex > -1 && range.getValue() == true) {
    var sheet = range.getSheet();

    for (var i=0; i<CHECKBOX_CELLS.length; i++) {
      if (i==checkboxIndex) continue;

      sheet.getRange(CHECKBOX_CELLS[i]).setValue(false);
    }
  }
}