00:00 We're creating a word track counter today. This is for authors who have a goal. We have a goal, and we want to get to, let's say, a hundred thousand words, and we want every day to track our goals.
00:11 We are going to do this all on one page so that we can sort of just keep it all together, but I would highly recommend putting the log that I put here on another tab and making it a little cleaner, but this is going to be an automatic tracker.
00:28 we're going to put today. A word count here, and then we want to track every single day, so we'll have a log here of date and count, and then we're going to see how far away from our goal we are.
00:43 So we may want to move these things around, but we might want to make it look a little nicer to begin with.
00:51 Let's use quicksand, which is 12. Let's view without the grid lines so that we can make it a bit nicer.
00:59 Instead of, I'll put 100,000 here. We'll do this. And we will wrap this with a border. Actually, yeah, be able to edit that today.
01:17 Let's wrap this with a border as well. A thick border. And do the same here. So those are the two things you're going to enter.
01:28 And here's where our log is going to be. Let's try to make this a bit nicer. We have our log here.
01:42 We can merge this. Now, every time, not every time we put in today, but, like, sometimes we're going to add 100 words and then come back later and we're like, oh, we did another 100 words today.
01:54 Okay. We can do that. Or, oh, we did 1,000 words, we took a break, and then we did another 1,000 words.
01:59 So we have 2,000 today. So we're going to just think we're going to put in the date, everything today. Let's make it quote-unquote automatic or at least automated or semi-automated, as I like to say.
02:13 We're going to have a user click here and log this date. Or, what we might want to do is log this at the end of the day so that the user doesn't have to do anything except put this in, and the next day it'll be clear.
02:32 We'll put in yesterday here. Actually, we'll put in something else. Let's just log a few things. And we're going to make, from C2, we're going to put in the date in A6 and the count in B6.
02:45 So anytime at the end of the day we're going to have this automatic, let's say, automatic tracker. So our function is going to track, we are going to take C2, we will call this C1, this tracker, or dashboard, well, we'll say just tracker.
03:06 So variable word count equals spreadsheet app dot get active spreadsheet, spreadsheet, get sheet by name, we're going to get the sheet tracker, we're going to get range, C2, get the value from there, and And then we're going to put the value into, I'm sure we can probably update this.
03:31 append here, append row, we're going to append two things, a time stamp and the word count. We're going to then need a time stamp.
03:49 Uhm, the way that I want to do this is utilities And the date I want is whatever date is today.
04:01 But the time zone I want to make sure is, let's say, GMT minus six. And we want the format to just be year, year, year.
04:17 I think it's month, month and day. So it's reverse, like, year, month, then day. We're going to run this and see if we need to authorize and see if there's any errors We've got no errors and we got the date and we have a count.
04:41 So what we can do is set this up on a timer. Basically over on the left side, go to triggers, create a new trigger function to run.
04:51 We're going to run that same thing we just did. We're going to call it time-driven, not from the spreadsheet, and we're going to choose a day.
05:04 Let's do at the end of the night, or early in the morning. Early in the morning is going to give us a different time, a day, sorry.
05:14 So at the, before midnight, sometime between 11 and midnight, we'll get our writing done and put in our tracker there.
05:21 So anytime we have a number here, it's going to log it here. Oh, I need to authorize again. But sometimes the key is we're going to make one more change to the function, because we want to clear out this, uhm, C2.
05:42 Totally forgot to do that. But we can edit this code, and that trigger is going to still work. So we want to take this, and we're going to, instead of C2, clear.
05:55 Actually, clear content. So now when I run this, you'll see that it's going to log again, but, uhm, let's actually change this.
06:09 Let's say this was yesterday. Oh, we have a wrong, uhm, uh, format here. Oh, had three Ds there. Okay, let's run this again, and see, now it is clearing today's number.
06:28 So, actually, I want to move this over to today, keep it a little bit more constrained. Not constrained. Simple. We want to now update where it's going.
06:41 It's not C2, it's B2. And also here, B2. Okay, that function will still work. Let's change this to yesterday, as if we did two thousand words.
06:55 Yesterday and today. And what I want to do is I want to make sure that this log is. Running, fine.
07:03 But we want to get a count. A running count of where we are. So instead of a hundred thousand here, we're going to do equals sum.
07:13 B6 to B. There we go. And I want in B3, this is going to sound crazy, but I want a little motivational quote here.
07:25 A little text that says, like, keep on going. Keep working at it. Do your work every day. Maybe it's, like, quotes from a person that is well-respected in the writing world.
07:45 Or it's some quote about something. Let's attach AI for this. I'm going to go to snippets. This is going to seem crazy to do, but we will.
07:58 but we'll just get AI. Let's see, open API. AI, actually, omni. That's what we want. We want this function. I'm going to add it down here.
08:13 We have AI, we have a prompt. We need to get an API key. So I'm going to go to API keys, and open AI, create a new key.
08:25 We're going to call this writer. We're going to get some writing. And I'll delete this API key by the time you see this sheet.
08:30 So, thank you. Get your own. Okay. Then we need a prompt, and we want to say, function, get AI, quote.
08:43 What we're going to do is say, uhm, we have a goal. Actually, we're going to run, return, quote. we're going to run variable quote equals.
09:02 AI, and now we want a prompt, some prompt here. Let's write the prompt, variable, some prompt equals, and we're going to say, uhm, we want to get, our goal is A2, variable goal.
09:20 we have that here. When we give it our goal, we want to give it our progress progress is going to be A3, so we're going to say, uhm, we are writing, and our goal is, plus goal, plus words.
09:58 We have so far gotten to plus progress, words. Write a motivational quote from a famous writer that speaks to our progress and how we can finish our goal.
10:32 Our writing goals. Let's see if this simple one can get something. When we return this quote, we want to write it in, let's say, B3.
10:42 Uhm, so instead of return quote, we will do this. spreadsheet, active, B3, set value, quote.
10:59 We also want to make sure we get the word wrap correct, Thank you. I think it's going, ah, we'll do that later.
11:04 Let's run it and see. Get AI quote. Uhm, this is what we've done. Let's add some more data here, uhm, in a different way.
11:21 Let's say days equals count all A6 to A. A5. Count if, so we do count if B7, B is greater than 0.
11:48 There. So that's how many days, uhm, Let's merge this to 4,000 words in two days and actually add that to our progress.
12:03 In days. We'll get this value is going to be a four days. Perfect. So we now know we can give AI our goal, our progress of words, and the number of days we've gone and done it.
12:27 We can probably do some more math and say something like, oh, at this pace, you will be gold. Going this, doing, getting this, uhm, if you continue going at your current pace, you can be done in X number of days.
12:42 Could probably do that, uhm, without AI. Uh, so let's say, let's just see what quote it got, it does. Let's, let's get our AI quote.
12:55 We're going to need more permissions. That's fine. We only have to do this the first time we do Thank you very much.
13:03 Let's see if there's any errors. It looks complete. This is quite a quote. Uhm, we need a word wrap. Absolutely.
13:14 Oh, we just, little by little, one travels far. You've made great progress already with your 4,000 words. Just keep moving forward and you'll reach your goal of 100.
13:22 Every word counts. Keep up the great work. Let's see if AI will do the math for us. only return the correct quote and the writer or speaker of the quote.
13:39 Only add a sec, a, one line that tells me the amount of days left if I continue to write at the current pace.
13:56 Again, we can probably do this with a little bit of JavaScript instead of getting the AI quote, but, I want to see what it's going to do.
14:08 Scariest moment is always just before you start. Stephen King, you have 48 days left at this current pace. That's fantastic.
14:17 I want to set this formatting correctly to always text wrap. I think that's always going to be an issue. But let's add this when we do our log, so get AI quote.
14:33 So our log is here. We're going to log clear this content and we're just going to write get AI quote.
14:37 So when this track is this timer goes, uh, every day, it's going to run the AI quote as well. So let's see, run.
14:52 We didn't do anything. So let's say we did this in the past. Uhm, on 31st. This doesn't exist. You can even delete all of these.
15:09 And let's put in, um, we did a thousand words today. Let's go back and run our track again. Again, that'll be doing it every day.
15:19 I mean, night does not matter how slowly you go. As long as you do not stop at this place, you'll reach your goal in 57 more days.
15:25 What is the actual answer? Cause before it was 48, but did we lower, we may, maybe lowered our You our time.
15:34 So, let's see. Um, let's just give it the answer here. So this is equal to this minus this. Uh, so we have 95,000 words left.
15:47 That's probably what it was. Just delete it. Um, we have done, divided by this 5,000 divided by three, right?
15:58 Because, perfect, 57 days left. So we can concatenate this, concatenate this, with, uhm, days left.
16:15 Give it a space. So now we do not have to ask for it to be in the, uhm, in the quote.
16:26 So, we know 57 days left. At this pace. So every time we enter, we're gonna get a different pace, right?
16:40 So let's take it out of the quote, the, uh, prompt. Only return the quote, the writer, there, perfect. Uhm, that'll update, that will update.
16:53 Let's make sure this is maybe our first day on the 30th. We're gonna put in a big number here, like, oh, we got through 5,000 words today.
17:02 Run it already. Our tracker, again, our automatic tracker, as if we did it. There it is, 5,000, 36 days left.
17:11 We have done 4 days in a row, 4 days. We have 10, done 10,000. We have 90,000 to go, right?
17:20 So what I think this is doing, right, is in one place it's allowing us to just consistently put in a word count.
17:29 We don't have to think about the day or whatever. We don't even have to think about it. Without logging it, we just have to go to B2, put in today's word count, and leave it.
17:39 The automation is going to take place where every time we open this up to put our word count in, we're going to get a different, uhm, maybe not a different quote, but we're going to get a quote from someone.
17:51 We're going to see our progress. We're going to see how many days we've done, more than zero. We're going to see how many days left every day, see this number go down, and we see in the same view all of our work that we've done each and every day.
18:08 This is what's going to give us that motivation, that intrinsic motivation, right? There's an external motivation of like, oh, here's some interesting person that's successful, and I can sort of live up to that.
18:18 But our goal of 100,000 words is going to be completed by these steps here, and we need to see those.
18:23 I think earlier in this video I mentioned you may want to put this log on another page, but in fact, this log in and of itself, just being here.
18:33 No matter what, even if we skip days, it's going to give us motivation to keep going and put in something every day, even if it's 100 words, right?
18:44 We're going to put in 100 here, run our automation again, and see, right? That hundred words is way better, a thousand times better than zero here.
19:03 So, it's intrinsically giving us that motivation. And we got a really funky number, so we may want to round this.
19:11 Uhm. Need one more parenthesis. There. Round. There, 45. That was a weird number, but that was good that we saw that.
19:22 And if we put in another big day, we're going to run this days left at this pace way down. Let's run it again.
19:30 And let's, Say we did this on the 28th. Let's run our track again. Again, this track is going to be done automatically.
19:41 That trigger is going to be happening every day. It almost seems, it seems impossible until it's done. It's done. Nice.
19:48 Now 24 days. Like, how cool would it be, right? And how uplifting would it be to go from like 45 days a day before to now with 40, 24 days left?
19:58 Like, oh my god. Like, this is doable by the end of the month. Like, uhm, wow. We may want to even project.
20:10 Let's, uhm, do something else here. Let's project. At this pace, days left. At this pace, your final writing day is is.
20:34 And then do, uhm, today. Is it today plus whatever this number is here?
20:53 Oh, we need to format. That is actually a date. 45,562. But we need to format it. Uhm, I don't think I it's date.
21:05 So I think it's just gonna be this to date. Just need to, nope. So it's going to be text. We want to format this text.
21:20 The format we want is probably going to be YYYMMDD. There we go. So 27th of September. Nice. Let's merge that.
21:43 24 days left. At this pace your final writing, your final, your final writing date is September 27th. Awesome.
21:55 If you don't have motivation from doing this, I don't know what will get it to you. You're going to have every day here.
22:01 Let's see if we can get down another 10,000. 1,000 words today. Run that tracker. Again, that would just happen overnight.
22:10 16 days left at this pace. Your final writing day is 9, September 19th. Awesome. Like, this is a really cool little dashboard, right, to keep all of our count here.
22:19 Get a motivational quote. Put in only one place, same time every time. Fantastic. And we got little pieces of information that we may want to know.
22:31 How many days left at this pace? What is our final, when is that actual date? What is our goal? Where are we at, right?
22:40 What is our progress? What is missing, which may be common on other dashboards, is how much is left. And in this particular case, I don't think that number is that really important to even have.
22:56 Like, we can absolutely add this, right? This is our current sum is 30,100. What is what is the amount left?
23:04 We may want to do that later on. And so I wonder if we do add that, but add it as if it was, like, in the last 20% or 10%.
23:20 Like, we may add this to the, um, here. You have to write plus, and we're just going to take. Take this minus this.
23:40 Not plus. Comma. More words. So maybe we add it here, right? 69,900. And if we have, like, you know, a weird word count, right?
24:00 We're going to have probably weird word counts. Run it. There we go. 68,622 words. We can actually, uh, format this text.
24:13 I think it's going to be number, num, number, number, number. There we go. So that adds a little comma there.
24:25 So we have a lot of formulas here. Concatenate, around text today doing a little math formatting it, uh, and getting a nice little dashboard here, right?
24:44 I think this should be called the ultimate writer's word count tracker, right? Anything else is less than the ultimate. Fantastic.
24:57 Thanks for watching.