google apps script - Add Checkbox in Sheets based on Criteria

Question

I'm looking for a way to add a functional checkbox to a cell, based on the value of another cell. I assume it may need to be script, as everything I've found so far has stated checkboxes are input only, and cannot be created with formulas.

I'm looking to create an unchecked checkbox in column C if the value in column B is a certain value (="Tech Notes"). If the value in column B does not fall under the specific criteria, no checkbox should appear at all in column C.

I did find a thread where checkboxes would appear in all cells of a column, but would be checked/unchecked based on criteria, but I'm looking for the criteria to determine whether the checkbox appears at all.

Here's a sample sheet

Is this possible? Thank you!

question from:https://stackoverflow.com/questions/65852771/add-checkbox-in-sheets-based-on-criteria

Answer 1

Since a script is acceptable, you can make a simple trigger to create and remove checkboxes upon editing the cell:

function onEdit(e) {
  if (e.range.getColumn() == 2) {
    var sheet = e.source.getActiveSheet();
    if (e.value === "Tech Notes" || 
        e.value === "Intake Process")
      sheet.getRange(e.range.getRow(),3).insertCheckboxes();
    else
      sheet.getRange(e.range.getRow(),3).removeCheckboxes();
  }
}

You don't need to execute this function manually in Google Apps Script, it will trigger everytime you edit the sheet.

Sample Data:

References:

Insert Checkboxes

Simple Triggers