We're going to do simple task management automations today. We're going to take these task names, due dates, priority, and status. We're going to make it a little bit easier to do data entry and look at this to see the differences of statuses and priorities. Right now, everything's black and white. We want to add a little color to it. We're going to create dropdown menus for priority and status. We're also going to highlight due dates today and tomorrow. We're going to do two different conditional formats. We're going to create a chart where we see all the different statuses and the percentage of each. And we're going to finish off with a email reminder that we're going to set up a trigger every day. It's going to email us. If there's any do tasks that are not completed yet, wait for that one. That's going to be a pretty awesome one. Let's start by creating some drop down menus.

So we'll take. All the priority, right click, drop down. And already it has our options here, but let's just color them high, low and medium, actually we'll switch out low and medium there. So high, medium, and low might want to even change this to a nicer red click done, and we can also select the statuses and now do the same thing. Right? Click. Drop down menu, and we see everything is here already. We'll use a little different Shades, maybe orange for progress and not started. We want to make sure hey We want to highlight those in black perhaps or Turn Bland Sheets Into Glam Sheets! How To Color Code Rows Based on Drop-down Value

dark gray is okay Or even this dark blue might be nicer something that stands out amongst the pastels here So we have completed and in progress if we want to add another item we can But for now, those are our options and this makes it a lot easier to change just by clicking instead of typing or adding to it. So we've created a dropdown for priority and a dropdown for status. Now let's highlight all of the due dates today first and then we'll do tomorrow. So in this due date column, we'll select it and go up to format conditional formatting and we'll say date is today. We will mark this as maybe white text and red background, perhaps. Click done. And let's add one more rule, that is for tomorrow.

Date is tomorrow. This one we'll make a little bit lighter, maybe yellow. And click done. So now, if we check this sheet, we can see, Oh, the red ones are exactly the ones that are absolutely due today. Tomorrow, if we get through those, we can work on those that are tomorrow. So let's create a list. We can just add a new sheet. Call this summary or data . We're going to use equals unique . Just in case we don't have all the priorities. And we can right next to it, count. If our range . Is going to be this C, and our criteria is going to be here. So we can copy paste this down. We can also create another one for statuses. Unique . Choose this range .

And here we'll do the same count if our range though will be the status. And our criteria will be this status here. Let's auto fill that. And right next to it, we can add percentages. So equals this divided by the sum. Let's do dollar sign E two colon E. And we need this to, to be locked as well. There we go. And we can change these formats to percentages. If we copy paste this over here. I think we need to do B2 to B. And there we go, we now have a nice little table that shows us priority and which percentages of each one. Also statuses here, give us a little space.

We can use this in charts if we want. Can select these cells , insert a chart and it'll first try to guess what kind of chart , let's say pie chart . Want to make it a little nicer chart style , background color , none chart border, none. Let's maximize it. 3d. It's fun. Yeah. We can put the label inside and we can get rid of the legend. Give us a nice big pie chart to show our status there. Let's create an email reminder. Email reminders will be sent out on the day things are due, and we'll want to make sure that we're not reminding ourselves of any completed statuses. So let's go to extensions Apps Script. Once you open it, it'll say my function. We'll call this send reminder. We need to have a variable that is the spreadsheet itself, the entire workbook.

That's SpreadsheetApp . getActiveSpreadsheet. And let's get all the tasks equals ss. getSheetByName tasks. getRange a colon d. And we're going to get the values of that. And we're going to work with that. We're going to do a for loop here. For i equals zero. i is less than tasks. length. That's it. That's how many tasks are in our data range . And we're going to iterate I plus plus make this a little easier to read. Then once we go into that for loop, how are we going to get through it? And what do we need? We need due date, those utilities dot format , date, new date, and it's going to be tasks I in square brackets.

One. Why this is one here is because on our sheet, we have the a column, B column, C column, and D column. So this is column one, two, three, four, but in arrays , the very first one is actually zero. So if we want the task name, we'll do in square brackets, zero. If we want the due date, one priority to status will be three, zero one, two, And so that's why we use tasks I, which is the row. And the one for the column. So we have that formatted. We need to format this in gmt plus zero, let's say, or whatever time zone you are in. And we want it formatted in yyy hyphen mm hyphen dd. Whatever your format is, you'll want to format it here, because

we're going to compare it to today. And this will be the exact same, except inside of new date, we'll put nothing. So we'll actually delete that task. So this is today, this new date with no variable inside, and we're now going to compare these. If due date is equal, we need two equal signs today. And you need two ampersands to say, and tasks I for the row and three for the status. Is exclamation point equals, meaning not equal to completed. And if that's the case,. What we'll need is an email and we're just going to get our own email session dot get active user dot get email.

That's just us, whoever is the owner of the sheet, creator of the sheet, and creator of this automation. This might be different than the owner of the sheet if you create this as an automation in someone else's sheet, but you authorize that automation as yourself. So what are we going to do if these are the same, the due date and today? We're going to send an email, gmailapp. getSendEmail. And we're going to send an email with three things. We need who it's to, email. We need the subject, which will be task. We'll put on a new line, actually. And we're going to need the body. Comma, task, reminder. And we're going to add the name of the task in the subject. Tasks, i, curly bracket, zero. That's the first column of whatever row we're in.

And then the body of the email will say, Reminder, task is due. We will add a new line, plus tasks i curly, uh, square bracket zero. Let's save this. And once we run it, we're going to have to authorize. The very first time we run it, but I will show you how to run it now to test it, and then I'll show you how to add this as a trigger so that it triggers every single day, and if there's any due dates that are the same as today, it will send an email. If not, it won't do anything. It's executed, and here's my email in my inbox. It says reminder task is due, submit expense report. Let's double check that that is correct. Submit expense report is today, and it is not completed.

That's great. We also have finalized presentation slides and organized inventory. Perfect. Those are the emails that we got. See this update website content is completed, order back office supplies, completed, conduct employee interviews completed. So it didn't, and here's organized inventory and it sent us that reminder. Great. We have now. Successfully sent one reminder. How do we set this up so that it sends it to us every day if, if these due dates are the same as today? Go over to the left side, click triggers. Here on the bottom right we'll say add trigger . Choose which function to run. This will be a drop down menu of all your functions . If you have multiple ones, you'll select Send Reminder. If you have only one, it'll be already here.

Instead of EventSource from Spreadsheet , we'll change this to TimeDriven. And we'll say DayTimer. And we're going to send this. Let's say at the beginning of the day, just before we get into the office, 6am to 7am, click save, and now this will trigger every single day. But the joy of this kind of automation is that we're using the if inside of the function , so it will only send it to us if those are true. Our automation is here. If you want to delete this, you can always come back to triggers. In your app script and click three dots and click delete trigger , or we can edit this. If you want to change the time, just click on the pencil icon there and you can edit it. Hope this was fun for you. Hope this was interesting.

We've sent that email reminder. We've created a drop down for priority status to make data entry easier. We've highlighted due dates today and tomorrow to keep us on our toes. We have a percent in each status to see how our tasks are being managed. And We're doing an email reminder, so we don't actually have to check this sheet every single day, or if we stop checking it, we'll get an email reminder of which tasks are due. Thanks for watching, and if you're looking to get more out of your Google Sheets, automations, just make better Google Sheets and better decisions for your life, subscribe here to BetterSheets on YouTube.

Better Sheets

Simple Task Management Automations