MORE Simple Task Management Automations in Google Sheets

0:00 I'm going to share with you more simple task management automations. In a previous video, we created dropdown menus. Highlighted due dates with conditional formatting , and created an automatic email that reminds us of anything that's due today. Today, however, we're going to create some more formulas with some automations like days until due. We're going to auto insert timestamps of when there are completed tasks and when there are tasks that are started or moved into in progress. We're gonna go create a formula to calculate that time between starting and completion. If you're a freak of, and we're going to also auto sort by due date, because anytime we're adding more tasks, we may want to sort this completely by the due date

0:49 every day, even if there's changes. So I'll show you how to do that. So let's start with these until do let's create a new column to the right. It's delete all that stuff. Days till due equals date diff start date today. End date is going to be date due and unit is going to be D or days, see this is going to be zero, but let's add days to this and if date D date diff D is less than one, then we won't do anything. So let's autofill this and now it will show nothing if the date is due today. But if it's. In the future, it'll show two days, one day, three days, four days, see, we have a number of days till due. Let's now work on this status column.

1:36 We want to do two things. We want to date things that are completed and date things that are still completed. Guarding or in progress. So let's add two more columns, one start and one pleaded. Let's move this to in progress and we'll test it on this one. So let's go to our Apps Script , that's over at Extensions, Apps Script . We may already have this function , send reminder. Let's create a new function . function on edit. This is a special inbuilt function that we just have to type here. We don't have to do anything else to it. We get some very interesting information every time that we change the text of a cell. We know the row it's on, e. range . get row. We know what column it's in, e. range . Spreadsheet Automation 101 Lesson 2: onEdit() Trigger

2:21 get column. We know what sheet we're on. getActiveSheet. getName. And we can say if sheet is equal to tasks here, two ampersands, and row is greater than one, meaning we're not in the header row , and column is equal to, we have two equal signs there, We're editing the status, so D or 4 is that number . We also can get the value that we're editing. Variable value equals E dot. And if that value is completed, well, we want to go to the sheet. Actually, the active sheet dot get sheet by name. Sorry, active spreadsheet . Show Sheet Tabs Based on Edit

3:07 Get sheet by name, tasks get range , and our range is gonna be the row. And the column we wanna edit is going to be this G column or the seventh column. So seven set value. And here we're gonna do new date, but we actually want something else. We want just the date. Let's get a variable. Date equals utilities. Format , date, new. Date that's today time zone . We'll just do it GMT plus zero for now You can change that to anything you want and the format we're gonna keep yyy hyphen month month hyphen day day D D We're gonna set this value of date and let's save this and now go

3:53 over and create or change to completed But we need to make sure value is equal to completed here. Okay, save that You We'll now change this to completed. Our sheet name is wrong. We need all caps tasks. Now when we switch to completed, we'll have the date completed. So anytime we switch one of these to completed, it'll have completed there in the same row. Let's do that for in progress now. So we'll stay in the same formula or function here. And we'll use the same if, except this value will be in progress. And we're going to change the 7 to a 6. Save that. Because we want to be on the tasks and put in start.

4:38 So let's put in progress. And now our start is showing. So the moment we select in progress, we're starting our task. And when we hit completed, we're completing our task. let's create a formula to calculate the time to completion. These will be just zero, but it'll be interesting. Let's change these start times to a couple days ago. And we can use date diff here. Date diff, the start date will be the start date , end date will be completed. Unit D. We'll add and space days. And so this shows us how many days between start and completed, we have all of these extra days here. So let's add, if is blank F2. And if that's actually blank, we won't do anything in that parentheses.

5:26 Now copy paste and see. Only if we have a start date , do we have time to completion. So let's add that start date . Zero days. We can add maybe three days ago. There we go. Three days, two days, two days. Let's do one final automation where we auto sort by due date. Basically we want to come to the sheet, freeze this first row and click and sort sheet maybe A to Z . This is the date, the smallest days going to the latest days. And we want to do that even if our due dates are changing. Let's say I have this. We can add around here if error comma enter. Let's fix that. Let's go to function and we'll create a new function, sort sheet.

6:15 We'll take spreadsheet app dot get active spreadsheet dot get sheet by name tasks dot sort . And we just put the column that we need sorted, which is number two or the B column. And we can choose true or false. It says here true is for ascending and false is for descending. So if we want the latest date first, last date we'll put false, but for us we'll put true. We can test this out by choosing up here sort sheet . Make sure it's saved. Click run. See if there's any errors, if not, go back to our sheet and see it is now sorted. Basically, we want to sort this overnight or maybe even every hour as we're editing due dates.

7:00 Anytime we edit one of these, the next time we come in, maybe the next day, we want to see, Hey, this is sorted differently, sorted correctly. And how do we create that sort sheet on an automatic timer? Go over to the left side, click triggers. We already had our trigger for send reminder. Now we're going to add a trigger for sort sheet . We have multiple functions here. Make sure you select sort sheet. Event source is going to be time driven. Our select type, let's say day timer, and we'll do it at the end of the day. So it's fresh for the next day, 8 to 9 p. m. Click save. If you haven't yet authorized, it'll ask you to authorize then. And now we have another automation here called sort sheet. And let's go back to our editor and make sure that it's sorting properly. Click run, executed fine, and we have the lowest dates at the top.

7:49 Again, just go over to sort date and turn this to false if you want the latest dates at the top. There you go, that was more simple automations. We've created a days until due formula . We created auto Entry for completed tasks for in progress when we started them and figured out what is the difference between the start and the end time and auto sort every day. So we keep our tasks in order of their date. Thanks for watching. And if you are enjoying this, make sure you subscribe here on YouTube to better sheets.