Google Sheets Cheat Sheet

Radio Buttons In Google Sheets

I'm going to show you how to make radio buttons in Google Sheets that work both horizontally in rows and vertically in columns:

shows a user interacting with checkboxes that act as radio buttons in google sheets both horizontally along rows and vertically up and down columns

Let's get stuck in:

These are radio buttons:



You can only select one option at a time.

These are checkboxes:



You can select as many options as you want.

Creating checkboxes in Google Sheets is easy:

You make a selection, open the Insert menu and select Checkbox. You can then tick the checkboxes as you need.

shows how to add checkboxes in google sheets using the insert menu

The underlying data in a ticked checkbox is TRUE. In an empty checkbox it's FALSE.

Creating radio buttons in Google Sheets is more difficult because it isn't built-in.

Instead, you can have to build it yourself using checkboxes and Google Apps Script.

How To Create Radio Buttons With Google Apps Script

Not on desktop? You need to do some of this on desktop before you can use and create radio buttons on iPhone/iPad and Android.

When I was thinking about how to write this script I wanted it to be as flexible and easy-to-use as possible.

As such, it's entirely plug and play. Copy the script into the Script Editor, hit save and you're good to go.


First things first: You're going to need an onEdit(e) function.

This function is a simple trigger that runs every time 'a user changes the value of any cell'.

The best parts about using an onEdit(e) function are that:

  1. You don't need to get users' permission to run it (so long as it only alters the current spreadsheet)
  2. It works on mobile devices

Now we know how it will be triggered, let's take a look at the script itself:

Dark theme
Copy code
function onEdit(e) {

  //Only run radio button function if edited cell
  //changed from false to true (as happens with checkboxes)
  if (e.value === "TRUE" && e.oldValue === "false") {
    horizontalRadios(e);
  }

}

//Function to manage horizontal radio buttons
function horizontalRadios(e) {

  //Setup the clicked sheet and get its data
  const ss = e.source;
  const sheet = ss.getActiveSheet();
  const data = sheet.getDataRange().getValues();

  //Get the event data
  //ASSUMPTION MADE: only one cell is edited
  const range = e.range;
  const row = range.rowStart;
  const col = range.columnStart;

  //Convert the row and column numbers to indexes (zero-based)
  const rowIndex = row - 1;
  const colIndex = col - 1;

  //Variables for the first and last columns of adjacent checkboxes
  //Default value is first and last column
  let firstCol = 1;
  let lastCol = data[0].length;

  //Loop to the left of the event
  for (let i = colIndex - 1; i >= 0; i--) {

    //In the first cell that isn't true or false
    //Assign the first column (index + 2) and stop the loop
    if (data[rowIndex][i] !== true && data[rowIndex][i] !== false) {
      firstCol = i + 2;
      break;
    }

  }

  //If the event column isn't the same as the first column
  //Get the range to the left of the event and uncheck it
  if (col !== firstCol) {
    sheet.getRange(row, firstCol, 1, col - firstCol).uncheck();
  }

  //Loop to the right of the event
  for (let i = colIndex + 1; i < data[0].length; i++) {

    //In the first cell that isn't true or false
    //Assign the last column (same as index) and stop the loop
    if (data[rowIndex][i] !== true && data[rowIndex][i] !== false) {
      lastCol = i;
      break;
    }

  }

  //If the event column isn't the same as the last column
  //Get the range to the right of the event and uncheck it
  if (col !== lastCol) {
    sheet.getRange(row, col + 1, 1, lastCol - col).uncheck();
  }

}

Let's talk through what happens.

If the sheet is used a lot the onEdit(e) function will run A LOT.

To prevent your sheet from slowing down considerably under the weight of constantly running this function you need to make it run as quickly as possible.

This is why when any cell's value is changed the event object's (e) old and new value is compared to make sure they match with the checking of a checkbox (a change from FALSE to TRUE).

Although the checkboxes use Boolean data, the event object passes these values as strings so you'll need to compare them to "false" and "TRUE" instead of false and true.

If it doesn't match (which is the case for all other value changes in the sheet) the function ends and doesn't consume unnecessary resources.

If it does match, the event is passed to the horizontalRadios(e) function.

This function is where the magic happens.

From the changed cell, the script searches left and right for adjacent checkboxes (identified by the true and false values they contain).

When there are no more adjacent checkboxes the function grabs the range to the left and right of the event cell and unchecks all of those checkboxes.

And now you've got radio buttons!

The best bit about this script is that you can put as many checkboxes anywhere in the whole sheet and, so long as they're adjacent, the script will turn them into radio buttons automatically:

shows the flexibility of this script allows you to create radio buttons anywhere within a google sheet (so long as the checkboxes are adjacent)

Need your radio buttons to be vertical instead of horizontal?

Here's the same script re-written to make that happen:

Dark theme
Copy code
function onEdit(e) {

  //Only run radio button function if edited cell
  //changed from false to true (as happens with checkboxes)
  if (e.value === "TRUE" && e.oldValue === "false") {
    verticalRadios(e);
  }

}

//Function to manage vertical radio buttons
function verticalRadios(e) {

  //Setup the clicked sheet and get its data
  const ss = e.source;
  const sheet = ss.getActiveSheet();
  const data = sheet.getDataRange().getValues();

  //Get the event data
  //ASSUMPTION MADE: only one cell is edited
  const range = e.range;
  const row = range.rowStart;
  const col = range.columnStart;

  //Convert the row and column numbers to indexes (zero-based)
  const rowIndex = row - 1;
  const colIndex = col - 1;

  //Variables for the first and last rows of adjacent checkboxes
  //Default value is first and last row
  let firstRow = 1;
  let lastRow = data.length;

  //Loop up from the event
  for (let i = rowIndex - 1; i >= 0; i--) {

    //In the first cell that isn't true or false
    //Assign the first row (index + 2) and stop the loop
    if (data[i][colIndex] !== true && data[i][colIndex] !== false) {
      firstRow = i + 2;
      break;
    }

  }

  //If the event row isn't the same as the first row
  //Get the range above the event and uncheck it
  if (row !== firstRow) {
    sheet.getRange(firstRow, col, row - firstRow, 1).uncheck();
  }

  //Loop down from the event
  for (let i = rowIndex + 1; i < data.length; i++) {

    //In the first cell that isn't true or false
    //Assign the last row (same as index) and stop the loop
    if (data[i][colIndex] !== true && data[i][colIndex] !== false) {
      lastRow = i;
      break;
    }

  }

  //If the event column isn't the same as the last column
  //Get the range under the event and uncheck it
  if (row !== lastRow) {
    sheet.getRange(row + 1, col, lastRow - row, 1).uncheck();
  }

}

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Radio Buttons In The Google Sheets iPhone & iPad Apps

You can't insert the radio button script using the Google Sheets iOS app for iPhone & iPad because you can't access the Script Editor.

You do however have the ability to execute scripts using the onEdit(e) function and to create checkboxes (by copying and pasting).

If you've inserted the radio button script on desktop, you can then use and create radio buttons in the sheet:

shows how to interact with and create radio buttons in the google sheets ios app for iphone and ipad

Radio Buttons In The Google Sheets Android App

You can't insert the radio button script using the Google Sheets Android app because you can't access the Script Editor.

You do however have the ability to execute scripts using the onEdit(e) function and to create checkboxes (by copying and pasting).

If you've inserted the radio button script on desktop, you can then use and create radio buttons in the sheet:

shows how to interact with and create radio buttons in the google sheets android app

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.