mercredi 27 janvier 2021

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

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.




Aucun commentaire:

Enregistrer un commentaire