Hey there stranger!

Sign up to get access.

Celebrate Your Completed Checklists

About this Tutorial

Learn how to use apps script to add emoji animations when you complete a checklist in Google Sheets.

Video Transcript

0:01 Hey, I wrote this code recently because somebody was asking when there's this to-do List that I created when we're done We can say done here, but we can also animate some like emojis around it to say hey like party up in here right party when done here's the app script that does that let me walk through
0:20 the app script in a hot second but first we also want to show that when we uncheck it the emojis go away so this is really cool if you have some extra space to spend this is really cool if you want to have something extra i'm also here in the formula saying if count true is 11 meaning the counting from
0:39 5 to 15 here is all true then we will say done if it's not true for all the times that we have not everything done it'll say to do so that's really cool we can re textualize or or recontextualize what this list is it's a to-do list and then when it's all done it's a done list right and obviously if you've
1:00 seen some other videos about this kind of checklist i have also up here this really cool formula that is essentially saying get a random to-do item that is not yet done so we're using this filter we're using if na to say like if there's nothing then don't show anything we're using round rand at okay 
1:25 so we're using rand times count all so we're counting all of the ones that are false and then we're doing getting a random number there and then we're using this index to say go to the row that's that random number I think that's a really cool thing and then we're wrapping it with if and a again saying
1:43 you know if if we're getting some not applicable error just don't show anything other errors will show up if you have some other errors but that means we get this reference errors sometimes go through and we will mostly get this discard replies because that's a random one here once we check this off 
2:01 we'll get another random one because we're editing it this rand is always going to be changing based on if you do an edit so literally every single edit you do will change this rand but that also means a checkbox is an edit and it will get a random one here it'll sometimes it does that but the really
2:23 cool thing i want to show you here is this app script and when we get everything done it changes to blank up here but then party we get to write some party emojis i think it's really cool all right let's walk through this first we're using the function on edit we can only have one on edit in the entire
2:38 sheet so sometimes if you're adding a bunch of app script and you find that your on edits are not working which happened to me actually recently i had a i had three different functions that were on edit on edit is a simple trigger that all we have to do is write that function on edit we don't have to
2:52 really name this function we just call it on edit and that means every single time we have an edit just like Thank you.
2:57 This rand is being redone this function will happen so we only want this function to happen when all of these check boxes are checked true so what we're doing here is we're going to get the column number we're going to get the spreadsheet the spreadsheet name we're going to get the range b5 to b15 so
3:16 if you want to have a different range here you can edit this we're getting all the values there in this range we're also getting the active sheet and then we're saying okay go through all of the duns that we say here this this range of b5 to b15 go through all of that if there's any that are not true
3:38 meaning unchecked return false but if all of them come back as true which is means this is we're going to say check true meaning they are all checked through we are done and so we're using this variable is it done to then say after that true or false true is it done to me all of them are checked off 
3:57 or false there's at least one unchecked one there so now if our edit this column if our if we're editing in the second column and we're also in the active sheet called random to do which is the the name of the tab down here but also if it's not done yet then we're actually writing emojis over but the
4:19 emoji right now is going to be nothing it's it's it could be two double quotes here actually or two single quotes here now if the column is the same two and the sheet is random to do but it is done then we're going to write all of the emojis this whatever emoji you pick here it could be thumbs up or 
4:41 this one's a party one we're going to use this for loop here to write on the fourth column the first 15 rows this emoji and that's how we get this and if we uncheck it we will it will un unwrite it or actually it'll write over it nothing so in these here it's nothing so watch out for this kind of thing
5:04 if you trying to do this make sure you're writing and unwriting on a column that you don't have any data on you don't have any formulas on because it will set the value and it will clear out any content you may have there and I think it's a really fun thing to do I think it's a really funny thing to 
5:20 do too to add sort of more . Party atmosphere or celebratory atmosphere to your to-do list to your checklist you can have multiple checklists checking here you can have multiple is it done's multiple things within this one on edit function if you wish but for here in this one go ahead and grab it this
5:44 is just for better sheets members so go grab this sheet down below and check out the subscript hopefully you learned something new today