Multivalue checkbox in Google sheets

Question

I want to have a checkbox that have three states (instead of True and False, it will have, let’s say, 1,2,3). Visually, it can be ticked, unticked or partially ticked, or different colors set by me.

Use case: I want to have 31 of these checkboxes, one for each day in a month. Each represent a task I need to accomplish. States are: Done (green or ticked), Not Done (red) and Couldn’t do (gray).

I know I can solve it by a combobox but prefer a checkbox.

Is that possible? How?

Answer 1

Use an onEdit(e) script, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.'
    );
  }
  threeStateCheckboxes_(e);
}


/**
* Toggles a checkbox between three states: checked, unchecked, and gray.
*
* @param {Object} e The onEdit() event object.
*/
function threeStateCheckboxes_(e) {
  // version 1.0, written by --Hyde, 24 January 2022
  //  - see https://webapps.stackexchange.com/a/162507/269219
  if (e.value === undefined || !e.value.match(/^(TRUE|FALSE)$/)) {
    return;
  }
  const rule = e.range.getDataValidation();
  if (rule.getCriteriaType() !== SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
    return;
  }
  if (e.oldValue === 'false') {
    e.range.setValue(null);
    e.range.setBackground('grey');
  } else {
    e.range.setBackground(null);
  }
}

Answer 2

Google Sheets checkboxes have three possible states:

• Blank (it looks as unchecked)
• Unchecked. The default value is False.
• Checked. The default value is True

Conditional formatting using a custom formula could be used to set the cell background color.

If you are using the default values the following formulas can be used:

• For blank use =A1=ISBLANK()
• For unchecked use =AND(A1<>ISBLANK(),NOT(A1))
• For checked use =A1

Answer 3

As an alternative to The selected answer you can use Dropdowns (edit: it seems that this is what you are referring to as "Combo box" in the original question. I'm planning to leave this answer up, but could take it down).

Go Insert>Drop-down (from the right-click menu or the menubar) to create a drop down. You can then select the desired range and add the three options with the required colour and label:

This gives the required options, although annoyingly the cell is empty by default (see this question for a discussion of default dropdown values) :