onEdit() dependent on more than 1 variable

Question

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

cnavar · Accepted Answer · 2024-03-20 22:48:06Z

2

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:

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:

edited Mar 20, 2024 at 22:48

answered Mar 19, 2024 at 20:25

cnavar

5612 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tien Over a year ago

Thanks for attempt, cnavar! I tested it and your suggestion makes column 4 the determinant column. As I'm new to using specs, I wasn't sure what to expect. I'm still doing trial and error to make the script dependent on all three columns , i.e., =AND(B<>"", C<>"", D<>"").

cnavar Over a year ago

Thanks for letting me know. I've updated my answer and added an alternative solution. This time, the column 4 is not the determinant column. You can give it a try and let me know if you have any other questions.

Tien Over a year ago

Appreciate the diligence and the wisdom to tackle it from another angle, cnavar! It works as intended.

Collectives™ on Stack Overflow

onEdit() dependent on more than 1 variable

1 Answer 1

Alternative Solution

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Alternative Solution

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related