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