A general purpose function like this could help:
/**
* @typedef {Object} startEnd
* A object denoting limits of a row or column
* @property {number} start
* @property {number} end
*/
/**
* @typedef {Object} runcriteria
* @description Describes which rows/columns to run this function
* @property {startEnd} row
* @property {startEnd} column
*/
/**
* A criteria checker for event object e
* @author TheMaster
* @see https://stackoverflow.com/a/73681716
* @param {GoogleAppsScript.Events.SheetsOnEdit} e
* @param {Object<string,Array<runcriteria>> } run_in
* @returns {{result:boolean,editedSheet:GoogleAppsScript.Spreadsheet.Sheet}}
*/
const isEditInMyCriteria_ = (
e,
run_in = {
Sheet1: [
/*Run in Sheet1!D2:I9 or Sheet1!!A9:Infinity*/
{
/*2:9*/ row: { start: 2, end: 5 },
/*D:I*/ column: { start: 4, end: 9 },
},
{ /*Anywhere after row9*/ row: { start: 9 } },
],
Sheet2: [/*Run everywhere in Sheet2*/ {}],
Sheet3: [
{ /*Only in rows 5 to rows10 */ row: { start: 5, end: 10 } },
{
/*or D15:E25*/ row: { start: 15, end: 25 },
column: { start: 4, end: 5 },
},
],
}
) => {
const criteriaSheetNames = new Set(Object.keys(run_in)),
editedRange = e.range,
editedSheet = editedRange.getSheet(),
editedSheetName = editedSheet.getName(),
isEditedSheetInRun_in = criteriaSheetNames.has(editedSheetName),
runcriteria = isEditedSheetInRun_in && run_in[editedSheetName],
{ rowStart, columnStart } = editedRange,
gte = (a, b, e) =>
a >= (b[e]?.start ?? -Infinity) && a <= (b[e]?.end ?? Infinity);
return {
result:
runcriteria &&
runcriteria.some(
(obj) => gte(rowStart, obj, 'row') && gte(columnStart, obj, 'column')
),
editedSheet,
};
};
To test,
const onEdit = e => console.log(isEditInMyCriteria_(e).result)
To use,
const onEdit = e => {
const {result, editedSheet} = isEditInMyCriteria_(e);
if(result){
//This edit satisfies all my criteria
//let's do some damage...
//uncheck all of D3:H3, D5:H5, D7:H7, D9:H9
editedSheet.getRangeList(["D3:H3", "D5:H5", "D7:H7", "D9:H9"]).uncheck()
//recheck current checkbox
e.range.check();
}
}