lundi 25 janvier 2021

The most optimal way to invert checkboxes in Google Script?

Because Google Script does not support radio buttons, I tried to created 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;
  }
}



Aucun commentaire:

Enregistrer un commentaire