// Sheets · Apps Script

Add a checkbox to a cell in Google Sheets.

How to insert a checkbox into a Google Sheets cell using Apps Script, read its true/false value in onEdit, and avoid the data-validation gotchas that trip up first attempts.

“I want to programmatically insert a checkbox into a Sheets cell and react to it being checked or unchecked, without clicking through the Insert menu by hand.”

The script

copy · paste · trigger
checkbox_setup.gs
Apps Script
// Insert a checkbox in B2, then react when it's toggled

function insertCheckbox() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange('B2');

  var rule = SpreadsheetApp.newDataValidation()
    .requireCheckbox()
    .build();

  cell.setDataValidation(rule);
  cell.setValue(false);
}

function onEdit(e) {
  var range = e.range;
  if (range.getA1Notation() !== 'B2') return;

  var isChecked = range.getValue();
  if (isChecked === true) {
    range.getSheet().getRange('C2').setValue('Task complete');
  } else {
    range.getSheet().getRange('C2').clearContent();
  }
}
Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

A checkbox is data validation, not a widget

When you insert a checkbox through the Sheets UI (Insert > Checkbox), Sheets applies a data validation rule to the cell — specifically, it calls the equivalent of `requireCheckbox()` on a `DataValidationBuilder`. The cell's value is not some opaque checkbox object; it is the boolean `true` when checked and `false` when unchecked. That distinction matters the moment you try to read or set it programmatically.

To do this in Apps Script, chain `newDataValidation().requireCheckbox().build()` and pass the result to `cell.setDataValidation(rule)`. You still need to call `cell.setValue(false)` afterward — `setDataValidation` attaches the rule but does not initialize the value, and an empty cell displays as unchecked but its value is `null`, which will bite you on the first comparison.

The first time I hit this, my `onEdit` handler was comparing the cell value to the string `'TRUE'` — because I had seen `.getValue()` return that string from a non-checkbox boolean column elsewhere. Checkbox cells return the actual boolean `true` or `false`, not the string. Use strict equality (`=== true`) and save yourself the debug session.

Reading the toggle in onEdit

The simple trigger `onEdit(e)` fires on every manual edit, and `e.range` is the cell that changed. For a checkbox, the edit event fires once per click: checked produces `true`, unchecked produces `false`. There is no intermediate or partial state.

Guard the function immediately with a cell address or sheet name check. Without it, every edit anywhere in the spreadsheet runs your logic. `range.getA1Notation()` returns things like `'B2'` for a single cell or `'B2:B10'` for a multi-cell selection. If you inserted checkboxes across a column, compare `range.getColumn()` and `range.getSheet().getName()` instead of a hardcoded A1 notation string.

One practical note: `onEdit` is a simple trigger, which means it cannot call services that require authorization (sending email, writing to Drive, calling an external URL). If your response to the checkbox toggle needs any of those, you have to install a trigger via `ScriptApp.newTrigger('myFunction').forSpreadsheet(ss).onEdit().install()` and accept the authorization prompt.

Custom checked and unchecked values

By default, `requireCheckbox()` uses `true` and `false`. If you need the cell to store something like `'yes'` / `'no'` or `1` / `0`, pass those values to the overloaded form: `requireCheckbox('yes', 'no')`. The first argument is the checked value, the second is unchecked.

This is useful when you are feeding the column into a formula that expects text, or when you are logging to a system that treats the raw cell values as strings. The visual checkbox still renders the same way — Sheets does not change the widget appearance based on the stored type. What changes is what `getValue()` returns in your script, so update your comparisons accordingly.

I keep a small utility function in a shared utils file that handles both flavors. Passing the wrong checked/unchecked type is one of those bugs that only surfaces in the edge case where someone clears and re-checks a cell after a formula has already read it once — worth being explicit up front.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions
Why does my checkbox cell show as unchecked but getValue() returns null?
setDataValidation() attaches the rule without writing a value. Call cell.setValue(false) immediately after to initialize it. A null value looks unchecked in the UI but fails a strict === false comparison in your script.
Can I insert checkboxes into a range all at once instead of one cell at a time?
Yes. getRange('B2:B20').setDataValidation(rule) applies the same rule to every cell in the range in a single API call. Follow it with getRange('B2:B20').setValue(false) to initialize the whole column.
My onEdit runs but isChecked is always false even when I check the box — what's wrong?
Check whether you are reading e.range.getValue() or the wrong cell. If the checkbox is in B2 but your range guard resolves to a different address (mismatched sheet, off-by-one row), you are reading an adjacent cell that never changes. Log e.range.getA1Notation() to confirm which cell triggered the event.
Does onEdit fire when a script sets the checkbox value with setValue()?
No. The simple trigger onEdit only fires on manual user edits, not programmatic changes. If your own script calls cell.setValue(true), onEdit does not run. You need to call your response logic directly from within the same script function.

Related scripts