Google Sheets – Automatically Add Checkboxes Based on Column Values

google sheets

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?

Best Answer

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.

Related Topic