google-apps-scriptcheckboxspecifications

onEdit() dependent on more than 1 variable


I found this code written by --Hyde that I understand half of how it works but it works perfectly. It is a trigger to create checkboxes in one cell of column A if one cell of column B is not empty. I am trying to understand it enough to make it dependent on not only one column but three columns (B, C, and D). I'm aware that the 'specs' constant would need to change to add the two other columns and the 'spec' constant would need to follow that. I'm also aware that the 'checkboxCell' constant wouldn't change. I've fiddled around with them without success.

Could someone enlighten me, please?

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
* Inserts and removes checkboxes depending on whether another column is blank.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  // version 1.1, written by --Hyde, 30 December 2022
  //  - see https://webapps.stackexchange.com/q/153531/269219
  if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
  const specs = [
    { sheet: "sheet_name", column: 2, rowStart: 2, checkboxColumn: 1, },
  ];
  let sheet;
  const spec = specs.filter(spec =>
    spec.column === e.range.columnStart
    && (sheet = e.range.getSheet()).getName().match(spec.sheet)
  )[0];
  if (!spec
    || spec.column !== e.range.columnStart
    || spec.rowStart > e.range.rowStart) {
    return;
  }
  const checkboxCell = sheet.getRange(e.range.rowStart, spec.checkboxColumn);
  if (e.value) {
    checkboxCell.insertCheckboxes();
  } else {
    checkboxCell.clearDataValidations();
    checkboxCell.clearContent();
  }
}

Solution

  • Here's the updated code:

    /**
    * Simple trigger that runs each time the user hand edits the spreadsheet.
    * Inserts and removes checkboxes depending on whether another column is blank.
    *
    * @param {Object} e The onEdit() event object.
    */
    function onEdit(e) {
      // version 1.1, written by --Hyde, 30 December 2022
      //  - see https://webapps.stackexchange.com/q/153531/269219
      if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
      const specs = [
        { sheet: "Sheet1", column: 4, rowStart: 2, checkboxColumn: 1, },
      ];
      console.log(specs);
    
      let sheet;
      const spec = specs.filter(spec =>
        spec.column === e.range.columnStart
        && (sheet = e.range.getSheet()).getName().match(spec.sheet)
      )[0];
      if (!spec
        || spec.column !== e.range.columnStart
        || spec.rowStart > e.range.rowStart) {
        return;
      }
      const checkboxCell = sheet.getRange(e.range.rowStart, spec.checkboxColumn);
      if (e.value) {
        checkboxCell.insertCheckboxes();
      } else {
        checkboxCell.clearDataValidations();
        checkboxCell.clearContent();
      }
    }
    

    The only thing I change here aside from Sheet1 as sheet name, is the column from 2 to 4.

    Here's the result:

    enter image description here

    Alternative Solution

    Instead of pushing through the old script, I opted to create this new one instead and ensured that it is dependent on all three columns B, C and D.

    function onEdit(e) {
      let sheet = e.source.getActiveSheet();
      const checkboxCell = sheet.getRange(e.range.rowStart, 1);
      let editedRow = sheet.getRange(e.range.rowStart, 2, 1, 3).getValues().flat();
      editedRow = editedRow.filter(x => x != '');
      if (editedRow.length == 3) {
        checkboxCell.insertCheckboxes();
      } else {
        checkboxCell.clearDataValidations();
        checkboxCell.clearContent();
      }
    }
    

    Result:

    enter image description here