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.

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:

• Basic coding concepts and the basics of coding using Google Apps Script

• Range in Google Sheets

• Reading from and writing to a Range in Google Sheets

• Checkboxes in Google Sheets

• Triggers in Google Sheets

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.

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.

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

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

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);
   }
 }
}

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


}

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);
}

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.

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!