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.
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.
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 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!
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
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
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
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)
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?
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.
@@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!
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...
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?
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)?
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!
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).
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
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.
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!
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 😅
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
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; } }
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.
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 👍🏽
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
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.
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.
@@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!
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
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
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
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.
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.
Some genius stuff here that I’ll be using. Thanks again. You explain things so clearly.
Thanks!
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.
Thanks for the compliments!
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.
Thank you so much for such helpful and 'mind-blowing' tips! This should reached millions of views!
You're very welcome! Always happy to help 😄
Hello Mr Hash Ali, really awesome. Thanks for sharing.
Happy to help
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?
Oh sorry, Roy! I must've messed that up somehow. It's fixed now. The script is now in the link. Sorry!
@@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!
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.
Did it work?
@@HashAliNZ Thanks for the follow up. It did work. Thank you.
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
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
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
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)
Great job, thx. My favorite : lambda and today. Soooo useful
All the lambda functions are incredibly useful!
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?
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.
@@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!
@@ChrissyPips Hey Chrissy! I made this specifically for you! ruclips.net/video/ks5fBoJBLJM/видео.html
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...
Totally agree! I've needed to use a three way checkbox in many cases
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?
how about checked if cell colouring with green/red manualy?
thanks
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)?
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!
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).
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
hopefully there is an option to have it double-clicked rather than single click only.
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.
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!
Great video. I wasn't able to access the file with the script to send emails however. Did the link die?
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 😅
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
@@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.
how do i make a checkbox check if the value of a different row is less than a given number?
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;
}
}
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.
Very helpful! I learned something new
Awesome! Great to know!
Is it possible to make a Deselect All button that retains the ability to select the individual items in your example grocery list?
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 👍🏽
I would like to see a video about how you came to use the lambda function, like now. ;p
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
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.
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.
@@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!
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
Hi. This tip is for Google Sheets, not Excel. For Excel check out Leila's video and let her know I sent you: ruclips.net/video/DNl9DzNwjv8/видео.html
This was awesome BTW!
Awesome - love ya work 😊
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
Yep! I'll send it through in the next hour!
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
=if(A2, 0, D2)
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.
Type it into E2
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
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.