Create a Radio Button From Checkboxes Using Google Apps Script

About this Tutorial

Let's code a Radio button that turns off all checkboxes except the one clicked.

Featured Formulas

Video Transcript

00:00 Who better sheets? Uh, so what's exciting about today's video is that it is something that has been requested of me.
00:08 It's also been requested by me. I had hoped and wished that, um, could figure this out and I had, and I did.
00:15 So what we're doing as you see, probably in the title of the video is we're creating a radio button. Um, what I mean by this is that very often we'll have checklists inside of a Google sheet and these checklists and check boxes make up the checklist.
00:34 Right? And a check box though is literally just a visual representation of true or false. Um, what that means is that each individual check box is individual.
00:44 They're not connected in any way, but sometimes we create a quiz or a checklist that we only want to select one.
00:53 In this case, for this example, I've created a rate calculator where essentially, um, an influencer or creator is, is, uh, has a rate at the top.
01:03 They put in their $1,000 or $12,000 and based on their mood, based on what they select, um, the second try, the second number is what they should actually charge.
01:19 Um, and of all of these options. I only want one selected at a time. So obviously I can put the check boxes in there and then, uh, sort of tell the user, Hey, only check one at a time, but realistically people aren't gonna check one at a time, they'll have two or three and then uncheck them or, and see, oh, it's broken how we want it to work.
01:43 Right. And I'll show you right now, is that when you select the check box, it works. And then when you select another checkbox, the other one goes away.
01:53 And this is what I would call like radio buttons are they're usually they look different than a checkbox. So like in web development, there's radio buttons and there's check boxes, check boxes like an on, off switch and radio buttons are of this selection.
02:07 You can only select one. Um, and that's what I mean by radio buttons. I don't know what your term for this is, but it's a single choice and it's automatically going to uncheck everything else.
02:20 Well, how do we do it? The first thing is we one can do this on one page, but we can also do it on a number of pages.
02:30 We can also do it on a number of times on a certain page, we were using a Google script with the on edit function.
02:38 So on edit, what that does in Google script is it says every single time there's an edit, no matter what, anywhere in the sheet, um, run this script.
02:48 Now we don't want this script to run, right. W what we're essentially doing is saying, okay, if the edit, if one of these boxes is actually edited into the true, uh, from false to true, then all the other ones, actually all of them unselect make them all false and then select the one that we really wanted to select.
03:10 And that's, what's happening. That's the magic that's happening behind the scenes with descript. Now, as, um, as a viewer of this video, you can go into the description, check out the, this sheet.
03:21 Uh, if you want to find out where the, whether the script editor is go to tools, script editor, and you'll come to this and you'll have this.
03:29 And I made some notes in here to make it easier for you to read if you've never done the scripting before, or if you want to make any changes and what the rest of this video will be, is me going through how this works line by line, and then describing and showing you how you can use this on multiple pages or maybe multiple and throne that you'll be able to see how you can do it on the same page.
03:53 Um, you need to know which, um, which ones are the check boxes that you want to have. You need to know what sheet you're on.
04:04 Um, but other than that, I think we're good to go. Um, again, I'll describe what this, uh, on edit function does and how I wrote the script.
04:16 And then I'll share with you how to make a new one. So I'll actually make a new page and we'll do the whole script again.
04:25 And I'll show you all the things you might have to change. So, first off on function on edit, this is literally a built in function in Google script.
04:34 You can only have one per sheet. So if you ever want to make any changes or add more on edit things to this, just add them within this curly brackets.
04:43 Um, we want that there should only happen on one sheet. Um, this specific thing we're doing right now, we only want to happen on one sheet.
04:51 So we had named the sheet here. Uh, I just wrote calc like CA LLC is the name of the sheet.
04:58 Uh, variable calc is equal to all of this stuff. There's a spreadsheet app, get active spreadsheet, gets sheet by name calc.
05:04 That's just the Google script way to figure out we just want on this calc sheet. Um, now we use this F use an F function inside of Google script to say that if the event E so we're using E here in on edit, we can also use event, um, any, any actual letters or texts you want, I'm going to use E because it was easier to type.
05:33 And every time you see E here, it refers to what's right here, or what's happening, what is the edit that's actually happening?
05:40 Um, we only want to make changes if the edit is on the calc sheet, right? Uh, we use E dot range, duck, get sheet that get name.
05:50 And then we say isn't equal to equal signs to calc. We use these ends to make sure that we have all of this is true.
05:58 So we also make sure that the column is in the B column, which in Google script, it's number two. So we don't use B column here.
06:06 We use literally the second column. And then we also want to make sure that the value that you're editing the edit value is true.
06:14 Meaning if I, if I do this right, we're editing to true, it does, it actually works it, but if I uncheck this, I really don't need anything to happen.
06:28 Um, so we only want to make sure that it's happening when the value is true. It's changing from false to true when it's in the second column and when it's on the calc, uh, page.
06:40 And so all of this stuff in here, make sure that every other edit that ever happens, it doesn't do what we're about to do.
06:49 It only happens if all of these are true, let's do the, the value is true. If the cheat is calc and the column is the second column, um, we can also add in, I made a sort of a bugaboo here, or there are some edge cases.
07:08 Like if you edit this, no, one's going to do this. But if I edit this to true, I think that also, so, but no one's ever going to do that.
07:17 That's like a weird edge case. Um, but if you change this to, uh, your rate, it shouldn't do anything because the value isn't true.
07:28 Right. Okay, perfect. So what actually happens? Well, first off, we need to know where all the answers and I used a variable, all answers are used calc docket range, and I literally just hard-coded in the 12th row.
07:42 Uh, and the second column and eight, um, eight rows. So starting on the 12th row, and here we can see, this is the 12th row, and there's a 1, 2, 3, 4, 5, 6, 7, 8, um, there's eight rows.
08:02 And so that is on the calc sheet, get range, starting on row 12 in column two, which is B two 12 and going down eight rows.
08:13 And what this is doing is it's getting this range and we'll why we need that is because we want all those values to be set to false.
08:22 And so here, I've created a, this is a hard coding right type in, in here, um, eight falses. So if you add a line here, if you change this eight to a nine, you're going to have to add one more false, and you do it the exact same way with these brackets and in false, but we don't need that right now, but just in case you want to add more or delete more some that's how you do it.
08:48 I think I deleted an extra bracket, third, so there's brackets on the far ends. And then each of these falses is bracketed as well.
08:57 All right. Get the road that the user actually did select this church. So we want to make sure that we, even though we're setting everything to false, we know by this on edit event, that the row that we're trying to do, and we get that by E dot range docket row.
09:15 And then we select that as true. So what's happening here is when I select true right now, nothing's happening. But now, if there's a true, here I go, uh, choose a bit sleepy.
09:28 Um, what's actually happening is everything is, is being set to false. And then it's remembering, Hey, this is the one that's needs to be true.
09:36 And it's happening. It's selecting. That is true. And that is it. That is how you create radio buttons out of check boxes.
09:45 So let's, um, show you how to do this on two sheets. So if you have multiple sheets and you want to do this, I'll show you right now.
09:56 So let's do do something here. Let's add a row or add a sheet and just duplicate it. Okay. A little hard to see him.
10:07 It's a little, um, slow. I'm gonna pause for a second. All right. Back that Tamra was getting the wet. So here we got copy of calcs.
10:16 So let's call it, um, quiz instead, right? I'm just going to do quiz, same stuff here. Actually, I'm going to delete, let's say two lines.
10:28 Let's delete these two rows, just so that there's something else different and I'll show you. So now we want this function to happen on calc.
10:36 I don't want to change anything here, but I want to also, uh, add it to the quiz sheet. So what we do first, we can take all of this.
10:48 That's inside these brackets. And I don't know if you can tell there's a curly bracket here and a curly bracket down there.
10:53 We want everything else want to take that I'm going to command C and I'm going to paste it. I'm going to just duplicate it so that we're running to here.
11:03 Now we have the top one here, and there's a second one here. So in the second one for stuff, I think I can do this where no, one second.
11:16 I think I can actually, uh, that in as a tab. Nope, I could, but I could, uh, put it away.
11:30 Um, close it. Okay. So here's calc and here's calc again, but the second one, we want to change the name to quiz.
11:39 Does she name I'm going to change also this variable. And when we do this, this variable quiz, we need to go down here and wherever we see calc as a variable, not this calc, actually, yes, This needs to be this as well.
11:56 This needs to be quiz here. And I think there's one more here. So there you go. We can look at, we just highlight this or see if there's any extras that I missed.
12:15 Nope, we're good. So now we're working on the quiz sheet. We're making sure that the range is quiz. The range is going to be a little different, right?
12:26 Because we deleted two. So first off I need to go here to the range 12. That's correct. It is on the 12th row.
12:34 It is still the second column and it goes eight. Nope. It is six rows down and we don't need eight falses.
12:42 We just need six of them. So we're going to delete all those, make sure we have that bracket. So there's a parentheses then a bracket and then our falses, which are in brackets.
12:54 And I think I'm gonna just command S and save this. And I think now this will work. So let's see.
13:02 Perfect. So some things that might happen is you might miss changing, uh, this name of the sheet in the F you might change one of, or you might miss one of these, um, variables.
13:19 So just do a command F and look for calc and just make sure there's 1, 2, 3, 4, 5 instances in this script. Again, you can change where it is in the tab by changing this column, um, by also changing where all answers are, you can change the number of, maybe we want to add more, can add more by just adding more falses, again, to add more falses, you have to do a comma co um, not curly practice and just regular bracket false in all caps.
13:53 And then if you want to add another one, do the same, uh, false is in brackets and quotations, but we're not going to do that for this one where you are, oh, we missed the back again.
14:06 It'll show, it'll tell you the syntax error. If you've missed this bracket, there you go. So that's how you would do radio buttons.
14:15 Uh, if you have any difficulties, obviously let me know, and I'm happy to help walk through them. If you're trying to add more to the same, same sheet.
14:24 So quiz, what you could do is you can do exactly what we did and copy the whole thing and duplicate it again.
14:31 You might want to, um, just do this if segment, um, that might be just enough to do it on a, um, copy and paste that because you're on the same page, but I think this is really cool because sometimes we want to do quizzes.
14:51 Sometimes we want to do checklists planners, uh, calculators. This is great for all types of sheets that you might want to build.
15:00 Thanks for watching.

Courses

Sheet Stories / Video Notes + ADDED: Email Notifications

00:00:00

Fast FAQS

ChatGPT Clone in Google Sheets Part 2

Create an Internal Google Sheets Add-on

Why Different Cell References in AI Integration in Sheets?

Show Sheet Tabs Based on Edit

Add Title Case to Google Sheets

Getting Started Coding in Apps Script

How to Power Testimonials with Google Forms and Sheets

Seek Errors When Coding Apps Script

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

ChatGPT Clone in Google Sheets Part 1

Embed a Number in a Website from a Google Sheet

Create Navigation Like A Book or Presentation

Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet

00:29:08

Hold a Giveaway Raffle in a Google Sheet

Quickstart Tutorial OpenAI API in Google Sheets

Capture Emails from Website Form to a Google Sheet (Without Zapier)

Embed a Headline in a Website from Google Sheets

Create a new Spreadsheet from just a Name in a Sheet.

00:05:21

Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?

00:05:43

Email Yourself a Cell from a Google Sheet, Every Day

OpenSea Data Inside Sheets

Create an Email Campaign Stats Calculator

00:35:13

Twitter App Clone in a Google Sheet

Dylan Asks: How to Automatically Delete Rows If Cell Contains Value

Highlight Row as You Move Your Cell Selection

Create a Timer with Apps Script

LinkTree in a Google Sheet

00:11:22

Password Protecting Data In a Google Sheet

Automatic Weekly Backup of Google Sheets

Create a CPM Custom Function (Create Better Calculators!)

Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script

00:12:29

What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users

Sync Two Tabs Without ImportRange()

Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.

00:00:00

Password Protecting Data In a Google Sheet Part 2 The Basics

Benoit Asks: How to Convert Case

00:07:35

Learn to Code in Google Sheets, For Programmers

Add a Checkbox to Turn on Dark Mode

00:05:10

Write Your First Script

00:08:31

Find Keywords in Any Column. Create quick search dropdown to find keywords

00:09:37

Basic CRM - Add a Powerful Script To Move Row Based on Status

How To Improve: 1,000 Business ideas: Business Idea Generator

00:11:20

Let's Make a Bookmarklet!

00:12:37

Troubleshooting Bitly in a Google Sheet Script

00:07:07

Unique Features - Design a Better Dashboard Part 2

00:04:13

How To Set Up Stripe Webhook to Google Sheets with Google Script

00:22:10

How to Edit a Macro

00:08:22

Sheet Stories / Video Notes + Clear 24 Hour Old Videos

00:35:10

Add A Timestamp to Task Lists (without Now Formula)

00:07:44

Make your Custom Functions Like Native Functions | Custom Function Autocomplete

00:17:58

Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop

00:23:26

New Syntax for WhatChanged Formula in Google Script

00:07:14

How to Record Macros

00:06:20

2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)

00:09:53

Deep Inside Dark Habits Google Script

00:18:54

How to Trigger Macros Daily

00:06:58

5 Ways to Create Coupon Codes | Create UUIDs

Create a Radio Button From Checkboxes Using Google Apps Script

00:15:01