10 HIDDEN SECRETS of Checkboxes in Google Sheets!

Checkboxes. Simple? Let's up the ante!
This video will take you on a journey to master conditional formatting, uncover handy tips and tricks, and even show you how to use Google Apps Script to send emails directly from your Google Sheet! Perfect for beginners and seasoned users alike, this tutorial will level up your productivity game.
After you watch these videos, just start playing in your spreadsheets. If you still want to improve, make sure you subscribe for more juicy tips and tricks.
----------------------------------------------------------------------------------------------------
🔗 LINKS
----------------------------------------------------------------------------------------------------
Thanks to Ben L Collins for the radio buttons work. If you don't know him yet, you will as soon as you start searching for Google Sheets solutions online. His library is HUGE: www.benlcollins.com
This is the spreadsheet for most of the things you've seen in this video:
docs.google.com/spreadsheets/...
And if you want the script to the emails tip, check out this Google Sheet that contains the Script: docs.google.com/spreadsheets/...
----------------------------------------------------------------------------------------------------
⏳ TIMESTAMPS
----------------------------------------------------------------------------------------------------
0:00 Intro
0:10 Basic conditional formatting
1:10 Checkbox validation
2:18 On-demand checkboxes
3:30 How to block the Heads Up error
4:45 Roll a dice with LAMBDAs
6:40 How to create static timestamps
7:28 Using checkboxes as formula loaders
9:01 Dynamic graphs
10:59 Create a Select All button
13:26 Simulating Radio Buttons
14:28 How to send emails directly from Google Sheet
----------------------------------------------------------------------------------------------------
💬 GOT A QUESTION?
↓↓↓ Leave a comment down below ↓↓↓

[@MrRonluker]

I didn't find what I was looking for, but you have many great tips here. I would like the check box to move the row to the bottom of the page with the line through it when completed.

[@spauldinggg]

Some genius stuff here that I’ll be using. Thanks again. You explain things so clearly.

[@HashAliNZ]

Thanks!

[@firdausazmi1284]

Thank you so much for such helpful and 'mind-blowing' tips! This should reached millions of views!

[@HashAliNZ]

You're very welcome! Always happy to help 😄

[@michaelp8376]

That is some polished video editing my friend! Super helpful info too. I especially like the tip about the braces, that will save some time.

[@HashAliNZ]

Thanks for the compliments!

[@DeanB100AU]

Awesome - love ya work 😊

[@derickyyy]

Very helpful! I learned something new

[@HashAliNZ]

Awesome! Great to know!

[@ChrissyPips]

This was awesome BTW!

[@cric8482]

Great job, thx. My favorite : lambda and today. Soooo useful

[@HashAliNZ]

All the lambda functions are incredibly useful!

[@stefania8715]

Thanks for these tips, I'm particularly interested in the one for sending emails. I haven't tried it yet, but I was wondering if there would be the possibility of sending them with attachments, for example with PDF documents.

[@esbi1124]

Hello Mr Hash Ali, really awesome. Thanks for sharing.

[@HashAliNZ]

Happy to help

[@bazgymbalestier1126]

For the static timestamps if I have a stamp for yesterday and I add or delete a row today the rows below it will update to the current time due to the change in the formula, any ways to stop that?

[@user-ds2db1cz1n]

how would you make a sheet which would show multiple check boxes and indicate a single time stamp? example time stamp box 1 (bus) box 2 (parent pick up) box 3 (walker)

[@wijayaplastikgrosir5291]

how about checked if cell colouring with green/red manualy? thanks

[@dougscott4008]

I was with you until someone distracted me... great check mark work... would love to see a check and an X mark options... not just true or yes, but no False (X), means I've not just left it blank, but have consciously said no or false...

[@HashAliNZ]

Totally agree! I've needed to use a three way checkbox in many cases

[@user-pe2nh6wt9x]

Great help. Thank. The strike through after click on the checkbox didn't work after several tries. Not sure why. Will keep trying. Thank you.

[@HashAliNZ]

Did it work?

[@user-pe2nh6wt9x]

@@HashAliNZ Thanks for the follow up. It did work. Thank you.

[@kiergarciaarts8432]

hopefully there is an option to have it double-clicked rather than single click only.

[@dancorwin9232]

Hi! Super fantastic video. I suppose what I'm confused about is why changing the logical test inside your IF function from "y" to "len(y)" achieves that outcome of presenting the static timestamp without it dissappearing. Why is that? Doesn't that change the output of your logical test from a TRUE/FALSE value to a value of 4/5 (4 TRUE, 5 for FALSE)?

[@HashAliNZ]

Great question! If we just have 'y' in there, it's asking "Is there something in 'y'?" For our checkbox, the values are TRUE or FALSE. TRUE gives us our value, FALSE gives us a blank. Now when we use len(y), it's asking "How many characters are in y?". Initially, the cell is unchanged so it's blank. However, once we enter a tick in the box, it's now got a non-zero number of characters (4 for TRUE, 5 for FALSE), so the value will always have something in it. It's no longer looking for just TRUE or FALSE, but wants to know if there's a non-zero number of characters. If the number of characters does change to zero, then we'll get the blank value. Strangely difficult to explain!

[@petermelling798]

I would like to see a video about how you came to use the lambda function, like now. ;p

[@HashAliNZ]

Yep, it's on the cards. It'll be a basic rundown because there's so much that Lambda can do, not to mention all the lambda helpers. You're such a great supporter of mine that I'll pretty much cover anything you ask haha. In fact, here's a Form for you to add whatever topics you want and it will add directly into my planner: forms.gle/nmuTQKhfEXeUx83o6

[@royellison940]

Hi Hash, thanks for this very easy to understand video and clear detail. I tried the link for the email script but nothing showed, can you help, please?

[@HashAliNZ]

Oh sorry, Roy! I must've messed that up somehow. It's fixed now. The script is now in the link. Sorry!

[@codysattva]

@@HashAliNZ Hi, Hash. I was excited to try the email script but it shows an error when running: 10:14:22 PM Notice Execution started 10:14:22 PM Error TypeError: Cannot read properties of undefined (reading 'range') onEdit @ Code.gs:4 Could you please make sure you have linked to the right AppScript code? Thank you!

[@user-jh1el6pr4l]

how do i make a checkbox check if the value of a different row is less than a given number?

[@the_purple_mage]

Is it possible to make a Deselect All button that retains the ability to select the individual items in your example grocery list?

[@HashAliNZ]

Hey Prince Neptune, That's possible only using Google Apps Script. Thy added a new function in 2022 called uncheck. To use it, open up Google Apps Script, delete the myFunction demo thingy, and paste this in: function uncheckAllCheckboxes() { SpreadsheetApp.getActiveSheet().getRange("Employee!A:A").uncheck(); } Save, run, allow permissions, and you're good to go 👍🏽

[@MeshaalIsmail-pm4dz]

The select all feature doesnt work?, i have tried multiple times. Also i dont not understand why you have to make it to different tick boxes and how do you relate the two colomns

[@HashAliNZ]

The select all feature does work, it's just a bit inverted. First, you need to have all of Column B selected. Then you can click on the Select All and it'll select all the checkboxes. Then to deselect individual items, you deselect the Column B checkboxes. It's required this way with because we're not using any Apps Scripts. The formula that makes it work is in C3 to first check if the Select All is ticked, and then to check the individual checkboxes in column B. To have a select all that doesn't require two columns would need an Apps Script. Download a fresh version of the spreadsheet I created. I've added it into the Select All tab. The script is in the Select All script page. Here's the spreadsheet with the additions: docs.google.com/spreadsheets/d/135nqAt6fxLm95Hl2YWQOzjO3244TxNMBSZ1VelU7ALU/copy

[@IbaWuba]

what he is forgetting to tell you is that in the data validation of the check boxes that are subject to the select all button he changed the "unchecked" cell value to be blank (backspaced to nothing) this allows the select all button to work because it is no longer overwriting the "False" that used to be in the unchecked check boxes. Hope this makes sense

[@MAHaage]

Is there a way to auto sort the page based on an attribute like checkboxes or a certain option within a dropdown menu? I'm guessing it will need app scripts but I'm trying to sort a sheet based on priority (urgent at top down to completed at the bottom).

[@HashAliNZ]

If you're using dropdown menus, you could number them by priority. 1 is high priority, 10 is lowest. Then use the Filter feature (not function) to sort A-Z ascending

[@jesuschrist4965]

Hey, I had a question. Is it possible to make a checkbox automatically tick based off of a certain condition? For example, if a certain cell had a number above 1, the checkbox would be set to true.

[@HashAliNZ]

Definitely. Let's say the checkbox is in A2, and when you put in a number bigger than 1 into B2 we want the checkbox ticked. All we do is, in the cell with the checkbox, use the formula =B2>1 That's all there is to it!

[@ChrissyPips]

In Google Sheets, is there a way to link check boxes on different sheets to duplicate one action? EX: Like if I check a box in sheet 1 on a list, and that same list is on sheet 5, will that box also check on sheet 5?

[@HashAliNZ]

Hi Chrissy, there's two ways to do this The First way is a one-way relation. That means if you check the box on Sheet1, it will update on Sheet5 however you can't go over to Sheet5 and tick it manually. It will only be controlled by the checkbox in Sheet1. The second would require a Google App Script. Quite a bit more complex, but it can be a two-way relation. That is, if one box is ticked then the other one will be too. And if a box is unticked, it will control the other one. Let me know if you need a video showing how that works.

[@ChrissyPips]

@@HashAliNZ You are amazing! I would love the information for option 2 with the 2 way script as that is the exact functionality that I am looking to have!

[@HashAliNZ]

@@ChrissyPips Hey Chrissy! I made this specifically for you! kzbin.info/www/bejne/oaSYl3Wlf6d_gK8

[@MikoajGano]

Great video. I wasn't able to access the file with the script to send emails however. Did the link die?

[@MikoajGano]

Ugh, just now I need to get a script to automate the emails, and as many tutorials there are for it, none seem to do what I actually need - except form what you showed here, but the link to the code is busted :/ Edit: it was on screen for just a second but the code is actually shown 😅

[@HashAliNZ]

Hey! Sorry for the late reply - been out of the country. Here's the spreadsheet with the Apps Script: docs.google.com/spreadsheets/d/1BDEHDhR3ZvGPfHLw97WLvmaztBJnwYkGO3Q73Rx1nFg/copy

[@MikoajGano]

@@HashAliNZ Ha ha, thank you! I was able to get it working already, but I'm sure the link will help the next person. Wish you all the best.

[@nightcoremodified1730]

Hi sir Hash I want to check up or our research paper if you have read it and we need your opinion to it thank you so much sir

[@HashAliNZ]

Yep! I'll send it through in the next hour!

[@mperacchi]

I tried the LAMBDA function with NOW() for timestamp in Excel but it doesn’t work. Any idea on how to solve it? If I change something in the sheets the time changes. Thanks

[@HashAliNZ]

Hi. This tip is for Google Sheets, not Excel. For Excel check out Leila's video and let her know I sent you: kzbin.info/www/bejne/en_Panewg9ydrJo

[@sitilatipahbintiyunuskpm-g7109]

hello sir..im having problem on script of simulating radio button.. the script like below... is there anything problem with it? its say TypeError: Cannot read properties of undefined (reading 'range') onEdit @ Code.gs:4 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 D18 is checked case 2: sheet.getRange("E18" + row + "F18" + row + "G18" + row + "H18" + row).uncheck(); break; // case when column E18 is checked case 3: sheet.getRangeList(["D18" + row + "F18" + row + "G18" + row + "H18" + row]).uncheck(); break; // case when column F18 is checked case 4: sheet.getRangeList(["D18" + row + "E18" + row + "G18" + row + "H18" + row]).uncheck(); break; // case when column G18 is checked case 5: sheet.getRangeList(["D18" + row + "E18" + row + "F18" + row + "H18" + row]).uncheck(); break; // case when column H18 is checked case 6: sheet.getRange("D18" + row + "E18" + row + "F18" + row + "G18" + row).uncheck(); break; // cell is outside of columns B to D default: return; } }

[@HashAliNZ]

Hi Siti, You don't need to hit the run button in Apps Script to get this to work. As long as your target is correct, you should just be able to activate the checkbox and it should just work.

[@user-fj3oe7rx9t]

check-all checkbox doesn't work for me. i copied the code exactly and can't reproduce the same result. if i select all, all the options check, but i'm still able to untick separate boxes (although the select-all formula still prevails and all options are still showing as ticked in another test cell range. ie. I'm not able to block myself from editing boxes if select-all is checked.

[@HashAliNZ]

In the provided workbook, the Select-All checkbox is two columns - if you find that it's not working right, you can highlight those two columns and change the text or background colour so you can investigate what's going on.

[@user-fj3oe7rx9t]

@@HashAliNZ thank you, tried that but still can't get it to disable checking if check-all has been selected. Feels like there's a missing part explaining the link between left and right column. I understand that select-all button changes everything in left column to TRUE, but how does it affect the right column is what i don't understand. Thanks once again for the support!