00:00 Oh, in this video, we are going to track everything we do in a Google sheet. Uh, in other words, we're going to create a lager.
00:08 This is one of my favorite things to do. That feels magical. It feels like you're doing something that is, uh, encompassing and crazy because like, we're going to write some Google script.
00:22 Uh, I have a product, uh, which is free, uh, over on Gumroad. If you go to actually, um, better sheets.co/products, you can get a link there to track anything.
00:36 And what that does when I created that for is like, if you want to track a daily habit, let's say you have, um, you want to stop eating fried food, or you want to work out every day or you want to do yoga or meditate every day.
00:48 And you want to track like DUI actually accomplish that in the next three to five days. Or do I do it seven days out of the next seven days?
00:56 Um, and it shows you a little heat map to there's sort of a pro version. You can get, you can see a little heat map, but that's like one thing a day.
01:03 And one action in one clip. This video, what we're going to do is basically this is very good for CRMs.
01:12 Uh, if you want to track edits in CRMs or a sheet late, like if you're doing Twitter, um, writing a tweet writing, or you're doing some kind of a copywriting, right?
01:29 Copywriting is little edits. You might want to track the edits as they go. You might be doing this with contractors.
01:38 Let's say you have a sheet that, you know, um, needs to be updated 10 times a day, or you hope, you think needs to be updated 10 times a day.
01:46 And you want to track every time there's an edit, or you want to track not just track the number of edits, but you just want to see, like, how does this thing progress?
01:54 Or you might be wanting to track like in someone doing something wrong and like, we need to correct it right.
02:02 There is a, you can click up here to open version history and you can see version different versions. But what ends up happening is like, this is really intrusive, not intrusive.
02:13 It's exhaustive to like, look through these sort of versions and saying what actually changed and it'll show you what changed, right?
02:21 That it'll show you in this green color. Totally. Okay. But like, maybe you want to track how many edits, like I said, you know, there's other reasons you might want to log everything.
02:32 So, um, here, we're going to set up, I'm going to pause the video, set up a little CRM and then we'll get started.
02:40 Okay. So we've set up a little bit of like a project manager, not really a CRM, but a project manager, or we're checking these tasks.
02:46 We've assigned them to certain individuals. We have an estimated day. We have a price. We have a cost of goods.
02:50 If like, uh, we want to know how much did our contractor actually cost or whatever. This is a pretty simple kind of database again, with that progress tracking progress of a project.
03:04 So what I like to do is I create a new tab and I call it a log log, all caps log.
03:09 What were you really need is we only really need two columns. So I'm going to delete all the other columns.
03:16 This helps me so much all the time to just delete columns. We're going to go to extensions apps script, and we are going to do a little bit of coding.
03:27 That's exciting. Uh, we are going to use the function on edit with a capital E. We are going to put E in here in other videos that I've done this before.
03:39 I'll use the event. It literally doesn't matter. Just make sure it's consistent throughout your code. Let's just do E we want to be quick on this one, I guess.
03:48 All right. What do we want to do? We only want to track, um, edits when they are on, not on the log page itself, but on sheet one.
03:56 So these are like project let's call, call it project with a capital P. We need to first find out if our edit is on, um, it's on the page.
04:10 So we're going to create a variable spreadsheet, app dot, uh, get active. It's getting active spreadsheet. Um, actually we don't even need to do this.
04:27 We have this in the event so we can go a variable name sheet equals E dot range dot think is going to be named, right.
04:44 This always gets me right after on the start. So we need is variable active sheet equals E source dot get active sheet.
04:59 And we want the range. Variable range equals EDA range. And now we need it. If, if, uh, in here and we're going to this, if active sheet is equal to project, we only want to do it if it's project.
05:22 So let's look at this. We won't execute any code yet. We will just leave it blank, but we want to log.
05:28 We want to make sure we're doing this correctly. So we're going to log sheet and plus active sheet, and then we're going to also log, we need the value.
05:46 So we want to get what is the thing that's being edited so that shouldn't be variable and it value is equal to up value should be that.
06:02 And let's go see if that's the case. So we're going to save this. And then we are going to, and it's something we're going to change this date here.
06:17 And we can go to extensions, not extensions. Yeah. Executions that always gets me. We can see completed here, or we have an odd edit.
06:28 We have, our sheet is sheet And our added value is undefined. So we've definitely done something wrong. So what I did is I went to active sheet and did eat dot source, get active sheet, duck, get named.
06:45 That's what we got to do to get the name. So we just tested this out. Let's test it again. We're gonna just do this on 1000.
06:52 So we should get 1000 and we should get project. Let's look got project that's perfect. And let's refresh and see what we got.
07:06 What's our edit value undefined as well. This might be an issue. Well, let's do 1200. Let's just change this to 1200 and see if that actually gets it.
07:21 There we go. So copying and pasting. So if we take this 1000, we paste it. I don't think that's going to come up, show up.
07:29 So we have a little bit of a bugaboo. There we go. So it's undefined whenever we copy paste, but if we actually edit the thing, go 12, let's go to 2000.
07:44 We can have just 200. So there should be two edits. There is our two edits. You can hit refresh and see what we're doing here.
08:00 There's our 2000 and fresh again. And there's a 200. So these will, this will log only times when we actually edit, not copy paste.
08:11 So if we change this to Andrew, again, it should show up and show us that the edit value is Andrew.
08:22 There we go and evaluate Andrew. And the sheet is still going to be project. Let's see it show up there and it's project.
08:32 Great. So now once we have these edits down and we want to log them, we have a few, a couple of things we want to do, right?
08:39 We want to make sure we are getting the cell, which we'll get through this range. We can do variable row equals he dot range dot row.
08:53 Think and variable column equal CLL equals E dot ranged dot column. J think it's get column. You will. We can double-check this.
09:11 We can check this by logging this as well. So RO and range. So we go a logger. Actually, we don't even need this E dot range.
09:20 We just need range. Figure that one out. So logger dot log row, and then logger dot log. Call the column, add another space here for readability so he can read it.
09:46 Okay, save it. And we can just copy paste this down. Maybe even take all of this. That's a few edits.
09:58 Let's look at what we got here. So again, we just wait and we refresh this a little bit, have a sip of coffee While we wait.
10:12 Great. So I think this undefined, what happens when we copy and pasted it, we have a row six column three, uh, we'll also have a timestamp, so we can even log that as well.
10:23 Log they're not logged, uh, the time and what this is going to be is variable. <inaudible> again, we just take another sip of coffee and wait for Google sheets to catch up.
11:05 There we go do our daytime is this Tuesday, July 5th. Great. We are rocking in her own. We've got our timestamp.
11:13 Now. All we need to do is print it. So on our track, everything on our log, we are going to write timestamp and edit.
11:23 We need two more columns. You want row. And one column want to know what edited, what was edited, what was edited?
11:35 Great. So to print this, we need active sheet. We need to know the range. We need to get the last row.
11:44 So we need variable. Last row equals um, pride sheet app, get active spreadsheet, get sheep by name, log dot, get last row.
12:07 That's all we need to do to get the last row. We have our everything else. So now we can just print it here.
12:17 We can say, um, we need to variable log equals Spreadsheet, app dot, get active spreadsheet. That gets sheet by name.
12:41 It's a log dot get range. The range is going to be, uh, the last row. Plus one, our column is going to be first.
12:52 We're going to do the timestamp. Um, and one, and we just want one, one. There should, we don't even need those extra ones.
13:00 We just need the first, the last row, add one. Then the first column and we get a set value. And our first value is going to be our timestamp.
13:15 Then we're, you're going to just take this and copy it. What's that a add the JavaScript stuff at the end, the semi-colon, but we're just going to copy it.
13:27 And instead of timestamp can change the column to two. We're going to change the timestamp to edit value. And we're going to do this two more times, uh, column three and column four, do row and call.
13:50 There we go. So now we can run this. Let's go back to here. Let's change this to 1000 and go to Europe.
14:00 So there we go. We've got a timestamp. We have our edit. What was, what did we edit? And where did we edit it?
14:05 We have six and five. So this is row six, column five. So let's do this a few times just to see what we got here.
14:15 So let's do and drew Carl, and we see the edits, Andrew, Carl, and we see what was edited row and column.
14:23 And we can always, we can always use other means to sort of Translate these right row is going to be fine.
14:33 But column three is going to be C C3 C4. Uh, it might be easier to translate this column number into a letter so that someone reading this log will know exactly what's going on.
14:47 Um, sometimes dates are going to be weird. Watch, I think is going to be hard. So that's a, yeah. So sometimes if we're putting everything in one column, like in this edit column and some of them are dates, some of them are numbers.
15:02 Some of them are names. It's going to be hard to, uh, format that. Cause I think there's one thought if I do format number date, see this 1000 also changed.
15:19 So we can't really do that. So we're just going to have to look out for, okay, if it's a 44,000 number, it's a date, unless in a year or two, it's going to turn to 45,000 and let's compare this to our open version history, which a lot of times we're going to need to deal with.
15:40 Right? So right now it's got it's in this expand detail. So all of these are edits right there and it'll highlight the edit for us.
15:50 Uh, it'll show us what it was edited at that moment. But man, this is, uh, this is horrible to deal with, right?
15:58 We would rather capture this data some other way so we can do this lot. And so in this video you have been shown the unedited function.
16:09 We showed you a little bit of coding to get the value of the edit value. We added a timestamp, we got the row, we got the column and we also set the value.
16:19 We use set value here to, um, say, what did, what happened? What did we log? And we only are doing it when the project, uh, tab is edited.
16:31 So that's pretty cool. And if this is one of your first times ever seen code or Google script, uh, you got to see the edit function and a lot of this logging, which helps me a lot of times, debug it, figure out what's wrong.
16:46 What did I do wrong? How did I type it wrong? Um, this logging helps immensely. I do want to show one thing just in case you get to the end of this, um, cheat.
16:59 So if there are no more rows to add what happens, let's see what happens. Um, we filled it up maybe a thousand times and you totally forgot about, you know, adding more rows, but let's just, let's see what happens.
17:16 If we get any errors we get completed, we are, we logging everything. There we go. So it's adding a row.
17:26 If we are, if we have no more rows, that's pretty cool. Right? So we don't have to insert a row.
17:34 We can do it. If we wish we can add this, uh, value, we can right above it. We can do something like, um, log dot, uh, row.
17:49 And I think what's going to happen then. Oops. Ah, we get some failures. When we try to do that, I think a pen row, you have to have a Rosa.
18:16 Actually, we might do insert row after, uh, and then get the last row last row that might be better to do So we don't get an error here so we can do Carl.
18:34 That's going to get a bunch of errors, but we'll see, okay. See, we have these undefined here, but we're adding in an inserting row.
18:41 We can even do this more times. This is a really cool thing that you do. Um, sometimes it can do two.
18:49 Let's do two. So now there'll be more and more rows. Now there are two rows added for every time we log.
19:00 So that's another option if you want to do it, but we don't need to. In this case, we can do this code.
19:07 So some pitfalls you might run into or some roadblocks as you saw these, uh, whenever you're copying and pasting, it's not showing the edits also bulk edits.
19:17 So let's do something like this. Let's see what happens. DoubleClick. We also get undefined here, anytime we're copying and pasting.
19:38 Um, only one of those. If we insert a row, might not show up, either do that a few times and we get an undefined.
19:57 So we don't get to actually see the action of all these logs. We just get to see if we edited, you know, something wrong or right.
20:09 There we go. You got those logs. So partly logged, but you can track everything. Anyways, contract that something happened. Someone edited the sheet at some point.
20:19 Um, and hopefully you got something cool out of this and got to learn some cool coding by.