0

I need to automatically add checkboxes in column F but only when column G contains a value in the same row. Conversely, I want a checkbox to disappear or be disabled when the cell in column G in the same row is emptied.

A search of Stack Exchange does not show suitable solutions. Tried using an onEdit(e) script to create and delete checkboxes but that failed.

How do I easily add and remove checkboxes in rows depending on whether column G contains a value or not?

0
0

Option 1: conditional formatting

Use conditional formatting to set the text color of checkboxes to white to make them invisible when the cell in column G in the same row is blank. An invisible checkbox cannot be toggled accidentally by clicking it — Sheets will issue a warning if that happens. Note that you can still toggle a checkbox by selecting the checkbox cell and pressing Space.

You can automatically make checkboxes invisible when column G is blank by applying this conditional formatting custom formula rule to the checkbox range F2:F:

=isblank(G2)

With this formatting, the checkboxes only become visible when a value is inserted in column G in the same row.

Option 2: an onEdit() script

You can also add and remove checkboxes with a script like this:

function onEdit(e) {
  const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
  const watchColumn = 7;
  const checkboxColumn = 6;
  if (e.range.columnStart !== watchColumn
    || !e.source.getActiveSheet().getName().match(watchSheets)) {
    return;
  }
  const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, checkboxColumn);
  if (e.value) {
    checkboxCell.insertCheckboxes();
  } else {
    checkboxCell.clearDataValidations();
    checkboxCell.clearContent();
  }
}

This approach has the benefit over the invisible checkboxes described in option 1 that pressing Space in an empty cell in the checkbox column will not toggle the underlying value there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.