Note - I'm new to coding and apologize in advance if I am asking something dumb or in the wrong format.

Goal - Create a table (not a list) in google sheets with radio button functionality (meaning only 1 checkbox can be selected at a time.)

Code - Here is the code that is working for a list of checkboxes (1 single range) -

function onEdit(e){
  if(e.source.getActiveSheet().getName() != "Sheet1" || e.range.columnStart != 2 || e.value == "FALSE") return;
  for(var i = 2;i<21;i  ){
    if(i == e.range.rowStart) continue;
    e.source.getActiveSheet().getRange(i,2).setValue("FALSE");
  }
} 

Question - Is there a way to apply an onEdit function to multiple separate ranges in google sheets Apps Script?

Example Sheet: In this example, you'll see there are multiple checkbox ranges in the table: D3:H3, D5:H5, D7:H7, D9:H9

Example Sheet

Many thanks in advance!

CodePudding user response：

Here's an onEdit for two different sheets

function onEdit(e){
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet1" && e.range.columnStart == 2 && e.value == "TRUE" {
    e.range.setValue("FALSE")
  }
  if(sh.getName() = "Sheet2" && e.range.columnStart == 4 && e.value) {
    e.range.offset(0,1).setValue(new Date()); //puts a timestamp in column5
  }
} 

CodePudding user response：

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();
  }
}