I am trying to use checkboxes like radio buttons in Google Sheets. I started with the helpful script at https://www.benlcollins.com/apps-script/radio-buttons-in-google-sheets/ But it only does rows, which unfortunately won't work for me. Kudos to Ben for that code too.
So using Ben's code as a starting place I want to have the app uncheck boxes that are part of a group and only leave the selected one checked.
I have set the demo up like a multiple choice questions and answers things, with two questions (as in screen shot below:
my code is:
/**
* onEdit to uncheck checkboxes as required
*/
function onEdit(e) {
// get event object data: sheet name, row number and column number
const sheet = e.range.getSheet();
if(sheet.getName()!='questions'){return} // makes sure we only impact the one sheet we want to adjust
const row = e.range.rowStart;
console.log(row);
if (row < 8) {
var block1 = row;
}
else if(row > 8 && row < 14) {
var block2 = row;
}
switch(block1) {
// case when row 3 is checked
case 1:
sheet.getRange("B4:B7").uncheck();
break;
// case when row 4 is checked
case 2:
sheet.getRangeList(["B3","B5:B7"]).uncheck();
break;
// case when row 5 is checked
case 3:
sheet.getRangeList(["B3:B4","B6:B7"]).uncheck();
break;
// case when row 6 is checked
case 4:
sheet.getRangeList(["B3:B5","B7"]).uncheck();
break;
// case when row 7 is checked
case 5:
sheet.getRange("B3:B6").uncheck();
break;
// cell is outside of rows 3 to 7
default:
return;
}
switch (block2) {
// case when row 10 is checked
case 1:
sheet.getRange("B11:B13").uncheck();
break;
// case when row 11 is checked
case 2:
sheet.getRangeList(["B10","B12:B13"]).uncheck();
break;
// case when row 12 is checked
case 3:
sheet.getRangeList(["B10:B11","B13"]).uncheck();
break;
// case when row 13 is checked
case 4:
sheet.getRangeList(["B10:B12"]).uncheck();
break;
// cell is outside of rows 3 to 7
default:
return;
}
}
Note that I have multiple sheets in the workbook, which therefore requires the test at line 7.
Currently the only cell that works the way I would expect is B7.
Banged my head for a bit on this, so reaching out for some guidance.
Aucun commentaire:
Enregistrer un commentaire