10 HIDDEN SECRETS of Checkboxes in Google Sheets!

[@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!

[@esbi1124]

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

[@HashAliNZ]

Happy to help

[@gaanpakhi4635]

hey, thanks for the great tips! I used a lambda function to put a timestamp when an adjacent cell containing a checkbox is ticked (=TRUE). But it is very easy to unclick a checkbox by mistake since many people are handling this sheet, so that would also remove the timestamp. is there a way to ensure that the checkbox, once clicked, doesn't get unclicked? or a timestamp once added doesn't get removed? the second one would be more useful.. thanks!

[@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!

[@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!

[@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.

[@phucchauhoang7367]

you're wonderful Mr Hash Ali!!!

[@HashAliNZ]

Thanks so much ❤️

[@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 😄

[@MartyChen-l9h]

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?

[@MartyChen-l9h]

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

[@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

[@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!

[@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?

[@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

[@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 👍🏽

[@wijayaplastikgrosir5291]

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

[@cric8482]

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

[@HashAliNZ]

All the lambda functions are incredibly useful!

[@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!

[@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.

[@DeanB100AU]

Awesome - love ya work 😊

[@derickyyy]

Very helpful! I learned something new

[@HashAliNZ]

Awesome! Great to know!

[@BruceKennedy-c2u]

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)

[@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.

[@starship1701]

Create a Select all button doesn't work for me. I've double checked to make sure everything's the same, but it says #REF! Array result not expanded because it would overwrite the data in (what would be cell C4 in your example). I made the checkboxes off to the side as well, the invisible ones.

[@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

[@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!

[@BigG-e1v]

Hi, pls help me, my checkbox is in column A, and my prices are in column D, what should i do if i want that everytime i click the checkbox, the value in column D will be disregarded?thank you

[@HashAliNZ]

=if(A2, 0, D2)

[@BigG-e1v]

i'm sorry sir but where should i type this formula? coz i tried to type this in A2 & it got an error same when i type this in D2.

[@HashAliNZ]

Type it into E2

[@BigG-e1v]

Sir Hash I really appreciate your help,actually it's working, but would it be possible that everytime i click the chockbox in column A, the corresponding value in column D will be disregarded without adding another column?like what you've suggested? thank you so much for your help sir Hash

[@HashAliNZ]

Yes but you would have to 'hardcode' the value in which isn't great because prices might change in the future. But if youre sure you want then in D2 type =if(A2, 0, 15.99) And just change the 15.99 to whatever value you want.

[@philipcoulson-c2r]

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

[@ChrissyPips]

This was awesome BTW!

[@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

[@belindaperez9457]

Two questions: Can you send an email with a logo? Because I think that's absolutely crazy you can send an email with a check mark. Also, how can I create a check mark that highlights one specific name over two columns?

[@HashAliNZ]

Hi Belinda, Yes you can add a logo then assign a script to it to send an email. I'm not sure I understand your second question. Can you expand on it? Describe the setup a bit or share the spreadsheet?

[@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

[@kiergarciaarts8432]

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