Google Sheets range(s) to allow varying numbers of checkboxes

Question

I have a sheet where I need to limit the number of checkboxes allowed within a range. Like this

H219 to H225 allows only one checkbox to be checked.

H228: H335 allows three checkboxes.

H340:H347 Allows two checkboxes.

This script works when I use it once, but when i add it multiple times and change the range it seems to stop working.

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='GOALS') {
    const mcpr=1;
    const mcpc=2;
    const arrayrange='h219:h225';
    const arg=sh.getRange(arrayrange);
    const avs=arg.getValues();
    const ulr=arg.getRow();
    const ulc=arg.getColumn();
    const lrr=ulr+arg.getHeight()-1;
    const lrc=ulc+arg.getWidth()-1;   
    if(e.range.columnStart<=lrc && e.range.rowStart<=lrr && e.value=="TRUE") {
      let rc=avs[e.range.rowStart-ulr].filter(function(e){return e;}).reduce(function(a,v){ if(v){return a+1;} },0);
      if(rc>mcpr){e.range.setValue("FALSE");e.source.toast('Sorry maximum checks per row is ' + mcpr);};
      let cc=avs.map(function(r,i){return r[e.range.columnStart-ulc];}).filter(function(e){return e}).reduce(function(a,v){if(v){return a+1;}},0);
      if(cc>mcpc){e.range.setValue('FALSE');e.source.toast('Sorry maximum checks per column is ' + mcpc);};          
    }
  }
}

//

function onEdit(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='GOALS') {
    const mcpr=1;
    const mcpc=3;
    const arrayrange='h236:h244';
    const arg=sh.getRange(arrayrange);
    const avs=arg.getValues();
    const ulr=arg.getRow();
    const ulc=arg.getColumn();
    const lrr=ulr+arg.getHeight()-1;
    const lrc=ulc+arg.getWidth()-1;   
    if(e.range.columnStart<=lrc && e.range.rowStart<=lrr && e.value=="TRUE") {
      let rc=avs[e.range.rowStart-ulr].filter(function(e){return e;}).reduce(function(a,v){ if(v){return a+1;} },0);
      if(rc>mcpr){e.range.setValue("FALSE");e.source.toast('Sorry maximum checks per row is ' + mcpr);};
      let cc=avs.map(function(r,i){return r[e.range.columnStart-ulc];}).filter(function(e){return e}).reduce(function(a,v){if(v){return a+1;}},0);
      if(cc>mcpc){e.range.setValue('FALSE');e.source.toast('Sorry maximum checks per column is ' + mcpc);};          
    }
  }
}

Thank you so much, I have searched far and wide and this was the best script i could find, i just need it to work in about 6 places within the same sheet, with each range allowing a different number of checkboxes.

question from:https://stackoverflow.com/questions/65930158/google-sheets-ranges-to-allow-varying-numbers-of-checkboxes

Answer 1

I believe your current situation and goal as follows.

• You have a Google Spreadsheet that the checkboxes are put to the cells H219:H225, H228:H335 and H340:H347.
• You want to give the limitation to the number for checking the checkboxes in each range.
• For example, H219:H225, H228:H335 and H340:H347 have the limitations of 1, 3 and 2, respectively.
• You want to achieve this using Google Apps Script.

In this case, in order to achieve your goal, I would like to propose a sample script using an array including the ranges and limitations. The script is run by the OnEdit simple trigger.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and set the variables of obj and sheetName, and save it. When you use this script, please check the checkboxes in the ranges H219:H225, H228:H335 and H340:H347. By this, the script is run by the simple trigger of OnEdit.

function onEdit(e) {
  // Please set the ranges and limitations.
  const obj = [
    {range: "H219:H225", limit: 1},
    {range: "H228:H335", limit: 3},
    {range: "H340:H347", limit: 2},
  ];
  const sheetName = "Sheet1"; // Please set the sheet name of the sheet including the checkboxes.
  
  const range = e.range;
  const editedColumn = range.getColumn();
  const editedRow = range.getRow();
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName) return;
  obj.forEach(({range}, i) => {
    const temp = sheet.getRange(range);
    const startRow = temp.getRow();
    const startColumn = temp.getColumn();
    obj[i].startRow = startRow;
    obj[i].endRow = startRow + temp.getNumRows() - 1;
    obj[i].startColumn = startColumn;
    obj[i].endColumn = startColumn + temp.getNumColumns() - 1;
  });
  for (let i = 0; i < obj.length; i++) {
    if (editedRow >= obj[i].startRow && editedRow <= obj[i].endRow && editedColumn >= obj[i].startColumn && editedColumn <= obj[i].endColumn) {
      const n = sheet.getRange(obj[i].range).getValues().filter(([h]) => h === true).length;
      if (n == obj[i].limit + 1) {
        range.uncheck();
        // Browser.msgBox("Number of checked checboxes are over the limitation."); // If you want to open the dialog, you canm use this.
      } else if (n > obj[i].limit + 1) {
        Browser.msgBox("Checed checkboxes of existing checkboxes have already been over the limitation number of " + obj[i].limit);
      }
      break;
    }
  }
}

Result:

When above script is used, the following result is obtained.

Note:

• This sample script is run by the OnEdit simple trigger. So when you directly run the script with the script editor, an error occurs. Please be careful this.

References:

• Simple Triggers
• Event Objects