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?


1 Answer 1


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:


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:

* 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.2, written by --Hyde, 26 March 2024
  //  - 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.');
  try {
    const specs = [
        sheet: /^(Sheet1|Sheet2|Sheet3)$/i,
        column: 3,
        checkboxes: {
          rowStart: 2,
          rowEnd: 13,
          columnStart: 12,
          columnEnd: 42,
    let sheet;
    const spec = specs.find(spec =>
      spec.column === e.range.columnStart
      && spec.checkboxes.rowStart <= e.range.rowEnd
      && spec.checkboxes.rowEnd >= e.range.rowStart
      && (sheet || (sheet = e.range.getSheet())).getName().match(spec.sheet)
    if (!spec) return;
    const rowStart = Math.max(spec.checkboxes.rowStart, e.range.rowStart);
    const rowEnd = Math.min(spec.checkboxes.rowEnd, e.range.rowEnd);
    const numRows = rowEnd - rowStart + 1;
    const numColumns = spec.checkboxes.columnEnd - spec.checkboxes.columnStart + 1;
    const checkboxCells = sheet.getRange(rowStart, spec.checkboxes.columnStart, numRows, numColumns);
    if (e.value) {
    } else {
  } catch (error) {
    throw error;

This approach has the benefit over the invisible checkboxes described in option 1 that checkboxes only get inserted when required, which prevents accidentally toggling an invisible checkbox by pressing Space in a seemingly empty cell.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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