Radio Buttons in Google Sheets: Only One Checkbox Checked

In this article, we’ll see how to make checkboxes in Google Sheets behave like radio buttons. In other words, we’ll ensure that only one can be checked at a time.

It’s impossible to do this with formulas alone, so we use Apps Script to uncheck boxes as required.

Here are the radio buttons in Google Sheets in action:

Radio Button In Google Sheets

You can see that when I check a new checkbox, any other checkboxes on that row are unchecked.

It takes a split second: you can see the row turns orange when the checked checkbox count is briefly 2, but this is simply the script working in the background.

Let’s see how to implement this with Apps Script.

Radio Buttons in Google Sheets Template

Click here to open the Radio Buttons in Google Sheets template

Feel free to make your own copy (File > Make a copy…).

Before you can use the radio buttons, you need to authorize the script to run.

To do this, open the script editor (Tools > Script editor…), select the onEdit function and run from within the Apps Script editor to grant the necessary permissions.

(If you can’t open the file, it’s likely because your G Suite account prohibits opening files from external sources. Talk to your G Suite administrator or try opening the file in an incognito browser.)

Radio Buttons in Google Sheets

To create your own radio buttons in Google Sheets, add this code to your Sheet:

  1. Go to Tools > Script editor…
  2. Delete the existing myFunction() code
  3. Copy in the code below
  4. Select the onEdit function and run from within the Apps Script editor to authorize the script
  5. Return to your Sheet to use the radio buttons
/**
 * onEdit to uncheck checkboxes as required
 */
function onEdit(e) {
  
  // get event object data: sheet name, row number and column number
  const sheet = e.range.getSheet();
  const row = e.range.rowStart;
  const col = e.range.columnStart;
  
  switch(col) {

    // case when column B is checked
    case 2:
      sheet.getRange("C" + row + ":E" + row).uncheck();
      break;

    // case when column C is checked
    case 3:
      sheet.getRangeList(["B" + row,"D" + row + ":E" + row]).uncheck();
      break;

    // case when column D is checked
    case 4:
      sheet.getRangeList(["B" + row + ":C" + row,"E" + row]).uncheck();
      break;
    
    // case when column E is checked
    case 5:
      sheet.getRange("B" + row + ":D" + row).uncheck();
      break;

    // cell is outside of columns B to D
    default:
      return;

  }
}

So how does this script work?

It uses the onEdit trigger in Apps Script to react when the user checks a checkbox. It then uses the information from that event (i.e. which checkbox was clicked) to know which checkboxes to uncheck.

You can see the lines that begin with e.range gather information about which Sheet we’re in and what the row and column coordinates of the checkbox are.

Then we use a switch statement to see if we clicked in column B, C, D, or E (i.e. column 2, 3, 4, or 5).

If we click a checkbox on either end of the row (i.e. column B or E) then we grab the continuous range on that row (i.e. C2:E2 or B2:D2) and use the uncheck method to uncheck any other checkboxes.

If the middle checkboxes are checked (i.e. column C or D) then the range we want to uncheck is no longer continuous, so we use the getRangeList method to get two ranges in A1 notation (e.g. B2 and D2:E2) and uncheck those checkboxes.

Formula To Count Checked Checkboxes

In column F of the GIF image at the top of this post, you’ll notice a formula that counts how many checkboxes are checked. It’s a simple check to ensure that the radio buttons are working correctly.

It’s a simple COUNTIF formula:

=COUNTIF(B2:E2,true)

(More info on the COUNTIF formula in lesson 3 of my free Advanced Formulas course.)

Formula To Return The Answer Column

We also added another formula to return the answer A, B, C, or D corresponding to the checkbox that is checked. (Note, this is not the column.)

It’s a straightforward INDEX and MATCH formula:

=INDEX($B$1:$E$1,1,MATCH(true,B2:E2,0))

(More info on the INDEX and MATCH formulas in lesson 10 of my free Advanced Formulas course.)

Conditional Formatting To Highlight Row Change

Conditional Formatting with Radio Buttons in Google Sheets

To add conditional formatting to highlight the whole row as it changes, we use the fact that the script takes a split second to run, so there are two checkboxes briefly checked.

The conditional formatting checks whether the COUNTIF result in column F is equal to 2, and if so, applies the formatting.

It’s applied to the whole row by using the $ sign in the conditional formatting custom formula:

=$F2=2

The conditional formatting is the orange that shows when a new checkbox is clicked:

Radio Button In Google Sheets

Generalizing The Script

Thanks to my fellow GDE Adam Morris for his extension to this radio button script, which works regardless of changes to the location of the checkboxes.

3 thoughts on “Radio Buttons in Google Sheets: Only One Checkbox Checked”

Leave a Reply

Your email address will not be published. Required fields are marked *