mercredi 1 septembre 2021

google script using checkboxes like radio buttons

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:

screenshot

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