How to have only one box checked instead of two on Google Apps Script?

Question

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[1];
  if (sheet.getActiveCell() = "E11"){
    sheet.getRange('E12').activate();
    sheet.getCurrentCell().setValue('FALSE');
    sheet.getRange('E13').activate();
    sheet.getCurrentCell().setValue('FALSE');
    sheet.getRange('E14').activate();
    sheet.getCurrentCell().setValue('FALSE');
  }
  if (sheet.getActiveCell() = "E12"){
    sheet.getRange('E11').activate();
    sheet.getCurrentCell().setValue('FALSE');
    sheet.getRange('E13').activate();
    sheet.getCurrentCell().setValue('FALSE');
    sheet.getRange('E14').activate();
    sheet.getCurrentCell().setValue('FALSE');
  }
}

So I want to make it so that if a TRUE statement is inputted, the other checkbox will be FALSE and viceversa.

Somato

getValue & setValue, plus a JavaScript comparison. Note that you can simply recorded macros by removing / replacing the activate() + CurrentCell() logic with the more direct logic: obtain range -> do thing to range – Goggin

Answer 1

The official GAS documentation contains lots of useful examples, so please refer to it whenever you are stuck. The example below might help you. This is for the scenario where both checkboxes are directly below each other (rows 1 & 2) in column 1.

function onEdit(e){

  //Checkbox coordinates. 
  var checkboxColumn = 1;
  var checkboxRows = [1, 2];
  var sheetName = "YOUR_SHEET_NAME";

  //Get the edited cell value 
  var value = e.value;

  //Get the old value
  var oldValue = e.oldValue;

  var editedRange = e.range;
  var editedSheet = editedRange.getSheet();

  if (editedSheet.getName() == sheetName && editedRange.getColumn() == checkboxColumn && checkboxRows.indexOf(editedRange.getRow()) != -1) {  

    //Get the row coordinate of the other checkbox
    var checkboxRow = checkboxRows.filter(function(rowNum) { return rowNum != editedRange.getRow();})[0];

    //set its value to the old value of the edited checkbox
    var range = editedSheet.getRange(checkboxRow, checkboxColumn).setValue(oldValue);

  }

}

Fill

Answer 2

Here's an alternative implementation of @Anton's answer that uses the RangeList class to support arbitrarily oriented "radio button"-grouped checkboxes, provided they're all on the same worksheet (the RangeList range sources must be on the same worksheet).

function onEdit(e) {
  const sheetName = 'some sheet name',
      rbLocations = [
        {r: 1, c: 1}, // A1
        {r: 2, c: 4}, // D2
        {c: 1, r: 8}, // A8
        ...
      ];

  if (!e) return; // only run for sheet edits.

  const toggle = e.oldValue;
  if (toggle === undefined)
    return; // require edit to be of a single cell.

  const sheet = edited.getSheet();
  if (sheet.getName() !== sheetName)
    return; // require edit to be of the desired sheet.

  const edited = e.range,
      eRow = edited.getRow(),
      eCol = edited.getColumn(),
      otherRBs = rbLocations.filter(
        function (loc) {
          return loc.r !== eRow || loc.c !== eCol;
        }
      ).map(
        function (l) {
          // Create an R1C1 notation string.
          return "R" + l.r + "C" + l.c; 
        }
      );

  // Acquire and use a RangeList to set the same value to all the other checkboxes.
  sheet.getRangeList(otherRBs).setValue(toggle);
}

References

• Array#filter
• Array#map
• Sheet#getRangeList
• Event objects - edit

Goggin