Radio buttons in Google Sheets using Apps Script

A radio button is a user interface element that lets users select a single option from a number of different (and usually mutually exclusive) options.

An example of radio buttons in a Google Form.

In this tutorial, I will show you how to build radio-button-like functionality in Google Sheets using Google Apps Script. Below is a sneak peek at the finished product. Notice that whenever a checkbox is checked, other checkboxes are automatically unchecked. This ensures that only a single checkbox is checked at a given time.

Prerequisites

This tutorial assumes that you're familiar with the following concepts:

Why would you ever want radio buttons in a Google Sheets spreadsheet?

While radio buttons are commonly used in forms, there are some use cases where you might want radio-button-like functionality in Google Sheets.

For example, consider a spreadsheet that contains a list of employees that work on a team.

A screenshot of Google Sheets that shows a spreadsheet with 4 columns. Column A contains checkboxes. Columns B,C and D contain First name, Last name and email addresses respectively.

You've written some code using Google Apps Script to automate checking the status of the employee and updating the spreadsheet with this information.. The way it works is the following:

Select the employee whose status you want to check.
Run the script to check and update their status from a custom admin menu.
The script will run and will let you know once it has completed updating status via a toast message.

The problem is ... you must ensure that only a single checkbox has been checked!

The script can only (!) process one employee at a time and it errors out if multiple checkboxes have been checked! This becomes frustrating for you since your spreadsheet (unlike the simple example that I'm using in this tutorial) has hundreds of rows and you need to hunt down the other pesky rows that have the checkbox checked.

You really want to solve this problem and you know exactly how to solve it. Just replace the checkboxes with radio buttons. That's it! Except, there's one problem - Google Sheets does not support radio buttons :(.

However, never fear when there you have Apps Script. In just a few lines of code, you can extend Google Sheets and implement this feature yourself!

Three steps to implement Radio Buttons in Google Sheets using Google Apps Script

Set up an Edit trigger so your Apps Script code is run whenever any change is made to your spreadsheet.
Have this code check if the edit involved a checkbox being checked.
If yes, uncheck all other checkboxes.

Step 1 — Set up an Edit trigger so your Apps Script code is run whenever any change is made to your spreadsheet

You can set up triggers manually too but it is easier to set them up using code. To set up your trigger, run the following function from the Apps Script editor.

Note

The code below assumes that you do not have any other triggers set up so please edit it if you do.

function setUpEditTrigger() {
 var triggers = ScriptApp.getProjectTriggers();
 if(triggers != null && triggers.length > 0)
   return;
 ScriptApp.newTrigger("ensureSingleCheckboxChecked").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

Step 1 — How does the code work?

The code first checks if your project has any imstallable triggers that are pre-installed. If yes, then it does nothing. This check is in place to prevent creating multiple edit triggers inadvertently. This check can be improved to be more specific and I will cover that in a future tutorial.

var triggers = ScriptApp.getProjectTriggers();
 if(triggers != null && triggers.length > 0)
   return;

Assuming no triggers are pre-installed, the script creates an edit trigger that will run the ensureSingleCheckboxChecked function upon every edit in the spreadsheet.

ScriptApp.newTrigger("ensureSingleCheckboxChecked").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();

Step 2 —Check if the edit involved a checkbox being checked

function ensureSingleCheckboxChecked(e) {
 if(e != null &&
    e.range.rowStart === e.range.rowEnd &&
    e.range.columnStart === 1 &&
    e.range.columnEnd === 1) {
   //Edit occurred in column A and a single cell was edited
   if(e.oldValue === "false" && e.value === "TRUE") {
     //A checkbox was checked
     uncheckOtherCheckboxes(e.range.rowStart);
   }
 }
}

Step 2 — How does the code work?

First, the code checks if the edit occurred in column A (the column containing checkboxes) and if a single cell was edited.

if(e != null &&
    e.range.rowStart === e.range.rowEnd &&
    e.range.columnStart === 1 &&
    e.range.columnEnd === 1) {


}

If yes, the code checks if a checkbox was checked. That is it checks if the cell's value changed from false to TRUE.

Note

Even though the values associated with the checkbox states might be boolean, they are passed as strings as part of the edit event. This is why the code checks for === "false" and === "TRUE".

if(e.oldValue === "false" && e.value === "TRUE") {
     //A checkbox was checked
     uncheckOtherCheckboxes(e.range.rowStart);
}

Step 3 — Uncheck all other checkboxes and ensure only a single checkbox is checked

function uncheckOtherCheckboxes(rowToIgnore) {
 var range = SpreadsheetApp.getActive().getSheetByName("Employees").getRange("A:A");
 var values = range.getValues();
  values.forEach(function (row, index) {
   if(index != 0 && rowToIgnore != (index + 1)) {
     values[index][0] = false;
   }  
 });
 range.setValues(values);
}

Step 3 — How does the code work?

This code sets all of the values in column A to false with the exception of (1) the column header (index != 0) and (2) the row where the checkbox was most recently checked (rowToIgnore != (index + 1)).

function uncheckOtherCheckboxes(rowToIgnore) {
 var range = SpreadsheetApp.getActive().getSheetByName("Employees").getRange("A:A");
 var values = range.getValues();
  values.forEach(function (row, index) {
   if(index != 0 && rowToIgnore != (index + 1)) {
     values[index][0] = false;
   }  
 });
 range.setValues(values);
}

Here is the full code to ensure that only a single checkbox has been checked, which is radio-button-like functionality in Google Sheets

//@OnlyCurrentDoc
function setUpEditTrigger() {
 var triggers = ScriptApp.getProjectTriggers();
 if(triggers != null && triggers.length > 0)
   return;
 ScriptApp.newTrigger("ensureSingleCheckboxChecked").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
}

function ensureSingleCheckboxChecked(e) {
 if(e != null &&
    e.range.rowStart === e.range.rowEnd &&
    e.range.columnStart === 1 &&
    e.range.columnEnd === 1) {
   //Edit occurred in column A and a single cell was edited
   if(e.oldValue === "false" && e.value === "TRUE") {
     //A checkbox was checked
     uncheckOtherCheckboxes(e.range.rowStart);
   }
 }
}

function uncheckOtherCheckboxes(rowToIgnore) {
 var range = SpreadsheetApp.getActive().getSheetByName("Employees").getRange("A:A");
 var values = range.getValues();
  values.forEach(function (row, index) {
   if(index != 0 && rowToIgnore != (index + 1)) {
     values[index][0] = false;
   }  
 });
 range.setValues(values);
}

Conclusion

In this tutorial, I showed you how to implement radio-button-like functionality in Google Sheets using Google Apps Script. In just a few lines of code, you can implement really useful features that make you more productive! That is the power of Apps Script.

Since Google Sheets does not support radio buttons, we used checkboxes instead and used Apps Script to ensure that only a single checkbox in a column can be checked.

If you had difficulty understanding any concept in this tutorial or if you could not get the code to work, I'd love to hear from you using the form below so I can make it more useful. Thank you!

Future work

There are a few ways to improve the code and I might write a future post to cover some of these topics.

For example, the code assumes that the checked and unchecked values are true and false respectively. You might be using custom values (like 'Yes' and 'No') in your checkbox and you will need to adapt your code accordingly. A better way to do this is to explicitly check if the cell contains a checkbox and get the values corresponding to its states via Apps Script itself.

Thanks for reading!

Sign up to be notified when I publish new content

By signing up you agree to the Privacy Policy & Terms.