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