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.
Since it’s impossible to create true radio buttons with checkboxes and formulas alone (although we can mimic radio button behavior with formulas), we use Apps Script to uncheck boxes as required.
Here are the radio buttons in Google Sheets in action:
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:
- Go to Tools > Script editor…
- Delete the existing myFunction() code
- Copy in the code below
- Select the onEdit function and run from within the Apps Script editor to authorize the script
- 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)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
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=2The conditional formatting is the orange that shows when a new checkbox is clicked:
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.
Here’s another great radio button tutorial from Kieran Dixon that generalizes the radio button idea to work horizontally or vertically.
Wonder how to change this from B,C,D,E to H,J,L,M
Here you go:
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 H is checked
case 8:
sheet.getRange(“I” + row + “:K” + row).uncheck();
break;
// case when column I is checked
case 9:
sheet.getRangeList([“H” + row,”J” + row + “:K” + row]).uncheck();
break;
// case when column J is checked
case 10:
sheet.getRangeList([“H” + row + “:I” + row,”K” + row]).uncheck();
break;
// case when column K is checked
case 11:
sheet.getRange(“H” + row + “:J” + row).uncheck();
break;
// cell is outside of columns H to K
default:
return;
}
}
Can I get a similar function with allowing only one check box in a column?
I have this code but it works on a single sheet named “MYSHEETNAME”
can anyone help me to make it work on another sheet for example “MYSHEETNAME1”
function onEdit(e){
// Declare variables
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var sheetName = “MYSHEETNAME”; // Make sure you change this to your actual sheet name if not using Sheet1
var columnNumber = 3; // Make sure you change this if not using ColA a.k.a Col 1 as your column of checkboxes
var headerRow = 1; // Make sure you change this if not using Row 1 as your header row
// Edited cell gets passed into function
var range = e.range;
// Returns the number of the edited row and column
var thisRow = range.getRow();
var thisCol = range.getColumn();
// Run function if checked column is edited
if (thisRow > headerRow && thisCol === columnNumber && sheet.getName() === sheetName && range.isChecked()){
console.log(“Run Function”);
oneTrueCell(sheet, thisRow, thisCol);
} else {
console.log(“Didn’t run function”) ;
}
}
cab you paste the function code of
oneTrueCell ?
How would change the code for just 2 columns? E and F
Here you go:
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 E is checked
case 5:
sheet.getRange(“F” + row).uncheck();
break;
// case when column F is checked
case 6:
sheet.getRange(“E” + row).uncheck();
break;
// cell is outside of columns E and F
default:
return;
}
}
Hello Ben, I’m using this sample with two columns and updating the columns for C and D, instead of E and F, however I’m getting the following error: TypeError: Cannot read properties of undefined (reading ‘range’)
onEdit @ Code.gs:7
Do you know how to fix? Thanks!
I’m getting the same error.
For those of you who encountered the error, as I did, I found an explanation at stackoverflow (see link below). The error is normal and is nothing to be worried about. It is only triggered because the script is run all by itself and actually needs an event that can only be triggered if you work directly on the sheet. If you go to the sheet and click checkboxes, you’ll see that it’s working flawlessly.
https://stackoverflow.com/questions/28911101/typeerror-cannot-read-property-range-from-undefined
Hi I have been reading your tutorials, I was able to create the hello world script. I an trying to create a script that will allow me to check off one box, in each row. I am completing a attendance document where you mark present or absent. Right now if you select present and absent for the same students they are counted twice. The present check box is in column F and the absent check box is in column G. The check boxes start at row F3. Can you please help me?
Hi Ben, thanks for all your articles and tutorials. I just wanted to say that the (unedited) script is returning the following error:
TypeError: Cannot read property ‘range’ of undefined
onEdit @ Code.gs:7
Ben,
I would like to edit the script where the same thing is accomplished in columns J,L,N,P. I don’t know much about script and have tried to edit the one above but am having trouble making it work. I would also like a check box at the top to check or uncheck the entire row.
I would love to replicate this but for more columns (columns B through I). I edited the script but running into errors.
See my script below:
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 + “:I” + row).uncheck();
break;
// case when column C is checked
case 3:
sheet.getRangeList([“B” + row,”D” + row + “:I” + row]).uncheck();
break;
// case when column D is checked
case 4:
sheet.getRangeList([“B” + row + “:C” + row,”E” + row + “:I” + row]).uncheck();
break;
// case when column E is checked
case 5:
sheet.getRangeList([“B” + row + “:D” + row,”F” + row + “:I” + row]).uncheck();
break;
// case when column F is checked
case 5:
sheet.getRangeList([“B” + row + “:E” + row,”G” + row + “:I” + row]).uncheck();
break;
// case when column G is checked
case 5:
sheet.getRangeList([“B” + row + “:F” + row,”H” + row + “:I” + row]).uncheck();
break;
// case when column H is checked
case 5:
sheet.getRangeList([“B” + row + “:G” + row,”I” + row]).uncheck();
break;
// case when column I is checked
case 5:
sheet.getRange(“B” + row + “:H” + row).uncheck();
break;
// cell is outside of columns B to I
default:
return;
}
}
Can you help?
Thank you so much! Such a big help for me. More powers to you!
I have 2 checkbox: 1 in column in C11 and other in E11. I want to apply similar code and make it a radio button.
I have a 5×3 table of boxes (15 boxes in all) and need a script that will uncheck all except the box picked. In this case, the cells are J3:M8 where the choices are in row 3 and column J and the checkboxes are from K4:M8.
The choice (checkbox which is true) will then feed a table with one of 15 different regression formulas (actually 30 — 2 per table).
Any help is appreciated.
I was wondering if this would be possible to be edited so that it works with ROW 10 and 11 instead of being based off columns? I also need this to target a specific sheet tab within my document so that other’s are unaffected by the script. Here is my script I’ve edited which is not currently working and a link to the sheet I am using it with. I’m leaving editing restricted just request access.
/**
* 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(row) {
// case when row 10 is checked
case 1:
sheet.getRange(11 + col).uncheck();
break;
// case when row 11 is checked
case 2:
sheet.getRange(10 + col).uncheck();
break;
// cell is outside of rows 10 and 11
default:
return;
}
}
https://docs.google.com/spreadsheets/d/1B_D3VvvZKwjQOhPJe1ljNy0RMHnw9E4OsuWB7Jo1Usk/edit?usp=sharing
Hi, did you ever find a solution for this? I’m also trying to target a specific Sheet as all checkboxes are affected…
Ta
found the solution,
Try the following line :
if (sheet.getName() === ‘Sheet1’) {
Example
function onEdit(e) {
// get event object data: sheet name, row number and column number
const sheet = e.range.getSheet();
if (sheet.getName() === ‘Sheet1’) {
const row = e.range.rowStart;
const col = e.range.columnStart;
switch (col) {
// case when column J is checked
case 10:
sheet.getRange(“K” + row + “:P” + row).uncheck();
break;
// case when column K is checked
case 11:
sheet.getRangeList([“J” + row, “L” + row + “:P” + row]).uncheck();
break;
// case when column L is checked
case 12:
sheet.getRangeList([“J” + row, “M” + row, “N” + row, “O” + row, “P” + row]).uncheck();
break;
// case when column M is checked
case 13:
sheet.getRange(“J” + row + “:L” + row).uncheck();
break;
// case when column N is checked
case 14:
sheet.getRange(“J” + row + “:L” + row).uncheck();
break;
// case when column O is checked
case 15:
sheet.getRange(“J” + row + “:L” + row).uncheck();
break;
// case when column P is checked
case 16:
sheet.getRange(“J” + row + “:L” + row).uncheck();
break;
// cell is outside of columns I to N
default:
return;
}
}
}
Hi,
I have multi row tables, only radio button like option required. But I don’t have “checkbox” columns next to each other. How can I do it?
https://docs.google.com/spreadsheets/d/1bHK26OmgHHYj6EP_A2WkxhHmueJXgUDDt_gEc7Wsg_Q/edit?usp=sharing
https://docs.google.com/spreadsheets/d/1rB-cT72ElCQPU-p4LuNt6Ym3F3eZSgOm-7fxmfbuO64/edit?usp=sharing
please tell me how to solve the problem if you need to select one checkmark from the whole array, for example, 4×4 ???
I’ve got some radio button set up using this general method, but the triggeringh sooo sloooow. It takes a second or two for the other check mark to uncheck, which is kind of confusing for the user. Also, if you do anything with the checkbox data (which ones are checked) before one finished unchecking it can cause problems. Any ideas how to speed this up?
Hi there, how do I specify a specific sheet for this functionality? my file has 6 sheets with multi select in each, but I only need it to work in one sheet
thx
Hello,
I have columns C through S (total 17 columns).
If it is possible for you, can you share this version as well?
Thank you,
Hi sir Ben,
I would like to edit the script where the same thing is accomplished in columns I,J,K,L,M,N. I don’t know much about script and have tried to edit the one above but am having trouble making it work. I would also like a check box of two different columns for different category such as for gender. Hoping for your response. Thank you in advance