00:00 Hey, better sheet members, uh, just going through dark habits. It's a sheet I made last year and everyone gets it for free.
00:08 That is a better sheet member. So you can go and check out this video. Uh, I'll put a link in the description called creating a dark mode theme in there.
00:17 Uh, right there. There's copy sheet and code for driving free. If you haven't watched that video, it goes through all of the sheet.
00:25 So I'll include a link to the sheet, uh, and a link to this video, just to go through the actual sheet that you want to see.
00:36 I wanted to go deep into this sheet and into the code actually, because it does something very, very simple, but it takes one script.
00:48 And I think you can use this script in a lot of different ways. So I wanted to share this script with you.
00:54 I wanted to walk through the script. Let's show you what it does first. So when somebody wants to track say a lack of, of drinking alcohol, see, I got sober two years ago.
01:04 And something similar to this, a you telegram about actually that asked me, did you drink today? Yes or no. And I said, no, every single day I've been doing that for two years and this has helped me.
01:16 And I really think that, uh, within the first two weeks I felt completely different. I did not want to break the streak.
01:24 And so something as simple as this clicking next to, um, your vice, your bad habit, I think it could really help others.
01:33 And I really hope it helps a lot of better sheet members. If you are one, if you're trying to break some kind of bad habit, um, this is a perfect time to do it.
01:41 Just use a sheet and then start tracking that you're actually not doing nothing you're, you're trying to stop doing. But second, um, again, I think this code once, you know the code, I think the code will help you, um, bring a little bit of magic to your sheets, um, do something that you might not have done before, do something, quote, unquote, automatically.
02:02 But I like to call it automagically or bring some magic to your sheet. So, um, yeah. Let me show you what it does.
02:08 So literally there's a check box here under the beer mug. I click it and then it says, great job, no alcohol.
02:16 Okay. And what that did was I go over to data and it popped in a, uh, vice. There is right there at the top.
02:28 So it, it put this date here, it put the emoji and the name, all of that with only one click, right?
02:37 All I did was click this and we can see it happen again again, uh, it's happening with a script. So we don't really see it.
02:45 See it, see it. Now there's 2, 1 10, 1 10. That's what it's doing. It's taking whatever day, you're clicking this on grabbing that it's clicking getting the emoji that you've selected for the thing that you're trying to give up and name, text for it.
03:00 So how does it do that? It uses the greatest function, I think in Google script on edit why this is so cool is because any time that a user take some action and edits the sheet in some way, uh, this script captures that and says, okay, now that the user edited it, the sheet what to do, and you can write anything you want to do tap, and you can, uh, create emails that get sent.
03:32 You can track things like add timestamps. You can take the thing that someone entered and morphing and changing and putting somewhere else.
03:40 Um, lots of really cool stuff. I love on edit and use this all the time to bring a little bit of magic to a sheet.
03:46 And so let me walk through step-by-step, we'll go pretty quickly. I won't like I tried not to drag a hundred too much.
03:56 Um, but just a few more minutes of this video. So on edit, we have this E in, um, inside these parentheses, what that is, is an event and it's a name and it's a text you get to select.
04:08 So every time you see this Eve, uh, it, you see e.orange here, you see here, um, that is whatever you text.
04:17 You want to put it in, you can put the word event, you can put the, some other word. Um, if you have, if you know, like what kind of contents going like text, but you just have to make sure that it's the same thing wherever you're using it.
04:32 So what we're first doing is we're saying we want some variables, right? We want to know where is this E or this event I'm going to call it event, but it's eat.
04:41 Um, what is the range? What is the, like a, is it which column ABC is it? Which row 1, 2, 3, we're going to figure out what the range is.
04:50 And we want that variable because we want to know where is it? Uh, we want to know, uh, w what spreadsheet is that all we want to know that it's on a particular spreadsheet, and we want to know which tablets on this.
04:59 That's an important goal to see really quickly. Um, here we see what is the active sheet. So, um, the very first thing I do in a lot of on edit functions, once we have a few variables, right?
05:15 We know the range. We want to know what sheets it's on. We want to know, get the name. So literally type an active sheet, which is this east star source that get active sheet, go get the name, that get name.
05:28 And if that, if that name of the sheet that it's on is equal to track. And we type that in.
05:35 We said, track right here, over here, track, if that's true, we also need to get these two other things. But if all of these things are true, we want to make sure the range get range dot, get row equals six.
05:49 Why is that? Because this is the sixth row. And then we also want to know that the value shirt. So if some, for some reason we Marked this and it was actually like not taken off, we want to unmark it and we don't want the addict to happen.
06:05 So that's it. We want to make sure if you're marking it true, you're getting the right row six and you're on the right sheet.
06:11 All of these need to be true in order to go on. If not, let's look at where it goes. It does nothing to the next, uh, curly bracket is down here and right there, it's nothing.
06:22 So if I do any other edits, if I, uh, edited over here, if I call it tracks, for some reason, it, it, every time I edit this function will start in the happen.
06:38 But this event, that's going to get the range. It's not going to be the right range. It's not going to be the right, uh, name of the sheet.
06:44 It's not going to be the right value. Either value is not going to be true. It's going to be tracked to whatever value I typed into the sheet.
06:51 Um, it's console dot log. You can skip because it's literally just, it was when I was testing this sheet, this script out, I wanted to make sure, like, what is the actual range that we're getting in and look at it and see what it is of the speed out range.
07:05 And so you can always see, uh, the console, look at it and executions let's look at the last time we won't have it for last one.
07:19 There it is. So to debug stuff. Ah, so this is what it's actually getting. So getting all this, and you can tell the bottom, we'll go down here, Rowan road, start six, six.
07:42 That's that range. Then the next thing that we console log is this emoji. And so you can see that here.
07:49 Um, let's go back to executions. Uh, go down here. We'll see down here. Um, this emojis, there is none here, but that's because we didn't have an emoji here.
08:07 It is that mug. So where that comes from, we'll go back to the editor and look at it is verbal emoji.
08:15 So we're saying on the sheet, spreadsheet dot, get active sheet, get range. We want to know what the range is.
08:22 Range is feed out range, get row minus one. So what I did here is I said, whatever row we're on, which is six.
08:31 Anytime we pass all of these right check, the six is true. All of this is true. So we know, we know this is six, but we don't, I didn't want to have to change this later if I ever turned this row.
08:44 So we just said, get the row of minus one, get the one right above it and grab that column range, docket column get value.
08:53 So we got the value of this cell, like doing this right here. And then we logged the emoji just to make sure that we got it all right.
09:04 We got the emoji, we got the range. Now we want to make sure that we put the data, the data we get on the sheet called data.
09:15 This is something that I just typed in data. You can name a sheet anywhere like maybe log L O G, maybe it's logger, whatever it is, uh, it is going to be the same code spreadsheet, abduct get active doc, get sheet by name.
09:30 And you're just going to put it in the name of the sheet that you want the data to go to here.
09:34 And we want to make sure we insert a row after the fifth row. So this was interesting discussion with myself.
09:41 We're kind of doing this as I could have just put this on the last row. But most of the time when we're dealing with dates and like logging, whenever we review that information, we almost always look at it in reverse chronological order.
10:02 We almost never look at things in chronological order. We want to know what was the last thing that happened. And what that means is we want to know what is the first item on our list of things that are in reverse chronological order?
10:14 What is the nearest thing? So I inserted a row and put the latest thing up here at the top. In this case, we had a track, we clicked one of these, right?
10:25 If we click this one, okay. Back to data, see, now we have that. So that's the last thing, but we wanted that on top.
10:35 Now, uh, one of the things you could have done, or I could have done is I could have not had any of this information here, so I could have just had at the top, the first thing that ever happened.
10:47 Um, and then I kind of said like, you know, just insert at the very top. But what I thought of is as a new user, as someone is selling this sheet and getting another user to use this sheet, I really want to be X over explain what each of these tabs does on the tablet itself.
11:07 Um, and not rely on the star here and not rely that someone is going to read every single thing that I ever put out this, or watch every video that when they go to this data, they will be able to see right away, what is this?
11:19 And what does it mean? So I wanted to have this explanation and I want them to have some labels here.
11:24 Um, so you might consider that, like, when you're working on your own sheet, you can sort of leave a lot of information to be, uh, imagined, or, uh, maybe a note somewhere else.
11:36 But if you're trying to make a sheet that someone is going to buy it and use then, um, consider overexplaining and being really forefront and at the forefront of this, um, not really like kind of hidden in a note, but I really wanted everyone to see that, to know what that data is.
11:54 So there's where, why we did the insert row. Then we did formatted deep and we literally, this was like one of the most like copy, pasted, uh, scripts I ever over yet when I I've always Googled like how to get date in Google script, I always get this utilities, that format, date, new date, and then you can format the datas, which, um, if you don't do this, if you don't use format data, if you just use, um, new date and you don't put the format in, then there'll be like a timestamp with the name of the, with the date.
12:31 So I just wanted the date. I did not need a time. Uh, that's some sort of an improvement I could probably make later on is like, if this date is the same as the last date, that was, and be like, Hey, you're like, let the user know.
12:44 Um, but needed to like really quickly get through this. I did make this sheet in like within a day, um, digging into that range.
12:53 So we set the value, um, in row six column one, we set the timestamp format of date and then column two in row six, we set the emoji.
13:08 Now we need to work the word. So where are the words? Right? If I sort of shot myself in the foot a little bit here, it's on goals.
13:18 Sorry, on track. I wanted to get as least number of words as possible. I want to it's like when you open it, like, you don't need to read anything.
13:27 You just see it, click it done. I wanted to like minimalize minimize the amount of like brain power. It takes to just click this.
13:37 And I thought, just show the emoji. Cause like at the time you're tracking this every day, you will remember what this emoji means, but like the cheek doesn't the sheet has it, here are the goals.
13:51 So that's why I took three lines here. Variable goals equals I took the, got the goal sheet, got the column, got the entire column.
14:01 And then I figured out what column added, Hey, got the value based on the row, right? Cause word row range, docket column is whatever the range is of the event.
14:23 I think I'm explaining that right range of the event. Uh, this is why the tracker puts out the emojis horizontally.
14:35 And so this one is a beer emoji and it is in column one, but on goals, it lists them from certain row, six downwards.
14:46 So vertically. So this was me, uh, sort of trends posing from the word row, which is, uh, we're getting the column that the tracker is in.
15:02 We're using that number and we're adding five because if this is like a weird math thing, right? If this is row a column, one is a column one.
15:14 Then it's going to be one plus five. If it is in the second column, number two, it's going to be five plus two, which is seven.
15:24 And then we're going to grab the column B or two. And right here, we'd say column two. So we started transposed the number added fi by adding five and putting in the row column, we've got that value.
15:38 So now we have the word that that emoji means. Um, and then we set that word, which is the third column in the sixth row because we've just inserted that six row there.
15:52 And then we let the user know great job. The last thing we do once we tell the user, Hey, you've checked that thing.
16:00 We've, we've added all the stuff to the rows. Um, we attracted, we still have some cleaning up to do. And so the last very, very last thing we do is on this track sheet, we find out what is the column?
16:15 You clicked on the checkbox and we set it to false. And so it looks, this code all happens, not magically, but by code, but it looks and feels like magic.
16:28 When we click it, we click it on the script happens. Great job, no alcohol, like, okay. And boom, we are done.
16:37 So how do we get that? I probably didn't explain this, but how do we get that? Okay. Is what is it?
16:46 One seven? Oh, it is in the second to last, um, thing, uh, line here. We have spreadsheet, app dot, get UI dot alert.
16:56 And that isn't, that is that alert with this text gives us this entire new Y, which is this, uh, which will show up here with an okay button X and the text that we want.
17:11 And it gives us the text from this word, this emoji that came in and click okay. And it all happens magically.
17:22 Uh, so that is the dark habits, streak checking code. And that is it. That is the entire script that is used.
17:30 And so you can copy and use this script, uh, to do some pretty awesome things, track things, set things, some things on somethings off, let users know what's going on with this, uh, UI and this alert, this button pop up.
17:46 So I think there's some really cool things you can add to your sheets with script. And because now you have the habits available to you to use, and please, please, please share it with anyone who really needs it, who might, uh, get something out of this, right.
18:02 Um, who might be able to stop a really bad habit, um, as, as great as you know, you can set different goals as great as no alcohol, you have different emojis for different things like gambling, um, smoking different types of journey, fast food, TV, and pizza.
18:24 If you want to just do general emojis, you have this sort of no, in this green, black square, all that kind of stuff.
18:31 And you can see streets here. I'm really excited about this. I really liked this little street tracker here as well, but I'm not going to go through this in that video.
18:38 I'm going to not go through that in this video. Thank you for watching this, this deep, deep, deep dive into the street code, dark habits, code and script.
18:48 If you have any questions and if I skipped one of these lines, let me know, happy to answer them, but.