Check Boxes/ Uncheck Boxes Google Appscript Google Sheets

Question

I am just learning script in Google Appsheet and have copied other scripts trying to adjust them to toggle my check boxes but haven't been able to get it working. Can someone help me get it scripted? Entire Columns J,L,N,P starting at row 5 need only one check box checked "True" per row at a time. Column J,L,N,P row 4 needs a master check box that will check or uncheck entire column.

I started with Ben's but wasn't able to get it edited. https://www.benlcollins.com/apps-script/radio-buttons-in-google-sheets/?unapproved=191483&moderation-hash=afecc2580f0b56c2ec9e26cdef4d1a99#comment-191483

Here's what I have so far but it doesn't do the entire column or all of the master check boxes.

function onEdit(e) {

  const as = e.source.getActiveSheet();
  const cell = e.range.getA1Notation();
  const cell_checks = ['J4','L4','N4','P4'];
  if(as.getName() == "MATERIAL LIST" && cell_checks.includes(cell) && 
e.range.isChecked())

{cell_checks.filter(val=>val!=cell).forEach(c=>as.getRange(c).uncheck())}

var spreadsheet = SpreadsheetApp.getActive();
   if (e.range.getA1Notation() == "J3") {
      spreadsheet.getRange('J4:J291').setValue('True');
   }
}

Mike Steelson · Accepted Answer · 2021-12-15T04:11:38.243

0

Modify your script as follows:

function onEdit(e) {
  const as = e.source.getActiveSheet();
  const lastRow = as.getLastRow();
  const cell = e.range;
  const col_checks = ['J','L','N','P'];
  // master row
  if(as.getName() == "MATERIAL LIST" && cell.getRow()==4 && cell.isChecked()){
    as.getRange(5,cell.getColumn(),lastRow-4,1).setValue('TRUE')
    col_checks.filter(val=>(val+cell.getRow())!=cell.getA1Notation()).forEach(c=>as.getRange(c+cell.getRow()).offset(0,0,lastRow-3).uncheck())
  }
  // individual row
  if(as.getName() == "MATERIAL LIST" && cell.getRow()>4 && cell.isChecked()){
    col_checks.filter(val=>(val+cell.getRow())!=cell.getA1Notation()).forEach(c=>as.getRange(c+cell.getRow()).uncheck())
  }
}

edited Dec 15 '21 at 04:11

answered Dec 15 '21 at 01:40

Mike Steelson

14,650
2
5
20

Thank you Mike. That works for the master function for the entire column but I would like to still be able to go down the row and check another column and have that column uncheck. An example would be: Set the default checkboxes in J, but row 7 needs to be set at L. So with all of column J checked I would click L on row 7 and it would uncheck J7 and check L7. Hope that makes sense. – Craig Dec 15 '21 at 02:07
ok, I have updated my answer – Mike Steelson Dec 15 '21 at 04:11
Thank you Mike. That works exactly how I want it to now I can use and adjust accordingly. – Craig Dec 15 '21 at 12:12

Check Boxes/ Uncheck Boxes Google Appscript Google Sheets

1 Answers1