The most optimal way to invert checkboxes in Google Script?

Question

Because Google Script does not support radio buttons, I tried to create a workaround. Though I have learned the basics of Python, I'm new to Javascript/Google Sctipt. I finally got my code to work but I feel the result is far too clumsy for such a simple task. How to optimize it?

Here is the working sample: https://docs.google.com/spreadsheets/d/1bcMj3Yxewo4ZUgnhg0z46NyqJYBfxm-6ocvmEHLwtWE/edit?usp=sharing

And here's my code:

const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
function onEdit(e) {
  var invertedRange = { // C4:D5 is the range to follow
    top : 4,
    bottom : 5,
    left : 3,
    right : 4
  }
  var thisRow = e.range.getRow();
  var thisCol = e.range.getColumn();
 
  // Invert the checkboxes in the range
  if (thisRow <= invertedRange.bottom && thisRow >= invertedRange.top) {
    if (thisCol <= invertedRange.right && thisCol >= invertedRange.left) {
      var changeArray = ss.getRange(invertedRange.top, invertedRange.left, 2, 2).getValues();
      var invertedArray = [];
      var rPos = 0; // first row of the 2x2 matrix
      var valueToAdd = true;
      for (var readRow = invertedRange.top; readRow <= invertedRange.bottom; readRow = readRow + 1) {
        var cPos = 0; // first column of the 2x2 matrix
        var invertedPart = [];
        for (var readCol = invertedRange.left; readCol <= invertedRange.right; readCol = readCol + 1) {
          if (thisRow == readRow && thisCol == readCol) {
            var valueToAdd = changeArray[rPos][cPos]; // do not invert the checkbox that was already manually changed by user edit
          } else {
            var valueToAdd = !changeArray[rPos][cPos]; // invert all other checkboxes in the range
          }
          var invertedPart = invertedPart.concat(valueToAdd); // create an array from a pair of checkbox values
          cPos = cPos + 1;
        }
        invertedArray[rPos]=invertedPart; // add the pairs into an array
        rPos = rPos + 1;
      }
      ss.getRange(invertedRange.top, invertedRange.left, 2, 2).setValues(invertedArray); // set the chackbox values
    } return;
  }
}

question from:https://stackoverflow.com/questions/65892183/the-most-optimal-way-to-invert-checkboxes-in-google-script

Answer 1

Give Checkboxes Radio Group Behavior

This onEdit function provide radio button group behavior for any range. You are required to enter the range in A1 Notation by setting the value of cbrg and selecting which direction you want the groups to be in either row or col. It will turn all the other cells in the row or column that you select by clicking it to true. It doesn't support validation values other than true or false.

 function onEdit(e) {
  //e.source.toast('Entry');
  const sh = e.range.getSheet();
  const group = 'col';//change which direction that you wish to group in
  const cbrg = "C4:D5";//specifying the range the starting column and row must be greater than one. 
  const rg = sh.getRange(cbrg);
  const w = rg.getWidth();
  const h = rg.getHeight();
  const cs = rg.getColumn();
  const ce = cs + w - 1;
  const rs = rg.getRow();
  const re = rs + h - 1;
  //console.log('rs:%s,re:%s,cs:%s,ce:%s', rs, re, cs, ce);
  if (sh.getName() == 'Sheet1' && e.range.columnStart > cs - 1 && e.range.columnStart < ce + 1 && e.range.rowStart > rs - 1 && e.range.rowStart < re + 1 && e.value == 'TRUE') {
    if (group == 'row') {
      //e.source.toast('row');
      let cA = new Array(w).fill(false);
      cA[e.range.columnStart - cs] = true;
      sh.getRange(e.range.rowStart, cs, 1, cA.length).setValues([cA]);
    }
    if (group == 'col') {
      //e.source.toast('col');
      let idx = e.range.rowStart - rs;
      let cA = [];
      for (let i = 0; i < h; i++) {
        if (i == idx) cA.push([true]);
        else cA.push([false]);
      }
      sh.getRange(rs, e.range.columnStart, cA.length, 1).setValues(cA);
    }
  }
}

The one below performs a little faster and is fairly easy to just set the top row (trow), bottom row (brow), left column (lcol), right col (rcol) and the radio group direction 'row' or 'col'. It requires quite few less function calls which are time consuming.

function onEdit(e) {
  //e.source.toast('Entry');
  const sh = e.range.getSheet();
  const trow=4;//top row
  const brow=5;//bottom row
  const lcol=3;//left column
  const rcol=4;//right column
  const group = 'row';//change which direction that you wish to group in
  //console.log('trow:%s,brow:%s,lcol:%s,rcol:%s', trow, brow, lcol, rcol);
  if (sh.getName() == 'Sheet1' && e.range.columnStart >= lcol && e.range.columnStart <= rcol && e.range.rowStart >= trow && e.range.rowStart <= brow && e.value == 'TRUE') {
    if (group == 'row') {
      //e.source.toast('row');
      let cA = new Array(rcol-lcol+1).fill(false);
      cA[e.range.columnStart - lcol] = true;
      sh.getRange(e.range.rowStart, lcol, 1, cA.length).setValues([cA]);
    }
    if (group == 'col') {
      //e.source.toast('col');
      let idx = e.range.rowStart - trow;
      let cA = [];
      for (let i = 0; i < brow-trow+1; i++) {
        if (i == idx) cA.push([true]);
        else cA.push([false]);
      }
      sh.getRange(trow, e.range.columnStart, cA.length, 1).setValues(cA);
    }
  }
}

Animation:

I changed the range on the animation so I could fit it into the smallest image possible.