Script to toggle multiple checkboxes in Google Sheets using one master checkbox

Question

I am trying to toggle multiple checkboxes in Google Sheets using one master checkbox.

I figured out separate scripts that can check or uncheck four boxes (say cells L2:L5) at once when run:

function Toggle1() {
  var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('L2:L5').activate();
  spreadsheet.getActiveRange().setValue('TRUE');
};

function Toggle2() {
  var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('L2:L5').activate();
  spreadsheet.getActiveRange().setValue('FALSE');
};

Is there a way to activate these based on a single checkbox placed elsewhere in the sheet, say A1? So, if A1 is checked, then L2:L5 will check. Alternatively, if A1 is unchecked, L2:L5 will uncheck.

My common sense tells me I have to use an if statement somewhere in the script. I am unclear how to do this in a script context.

Thank you for any and all help!

See stackoverflow.com/questions/52375604/only-one-true-checkbox and stackoverflow.com/a/51898268/9337071 — tehhowch
Possible duplicate of How to have only one box checked instead of two on Google Apps Script? — tehhowch
Thank you for your help. I tried to follow these solutions, but they are far beyond my skill level. — Z.Doe
Welcome. How to have only one box checked instead of two on Google Apps Script? looks like it is what you need (more or less). What aspect of this solution is beyond your skill level? I'm just trying to understand what you were expecting by way of an answer to your question because I don't think there is a less complex solution. — Tedinoz
Thank you, as I replied below -- in general I need to read more about the onEdit function before I understand. I solved my original problem with a master button instead of a master checkbox. Seems to work. — Z.Doe

Cooper Cooper · Accepted Answer · 2019-03-02T19:28:23

Try this:

For having checkboxes is A2:A5 and L2:L5:

function onEdit(e) {
  if(e.range.getSheet().getName()!='Sheet83')return;//sheet name 
  if(e.range.columnStart==1 && e.range.rowStart < 6 && e.range.rowStart > 1) {
    e.range.offset(0,11).setValue(e.value);
  }
}

Script to toggle multiple checkboxes in Google Sheets using one master checkbox

1 Answers