Make an Assignment Tracker with Forms, Sheets & Icons (Full Transcript)

[00:00:00] Speaker 1: If you have trouble keeping track of school assignments, we're going to make an assignment tracker using Google Sheets. We're also going to create a Google Form to make adding those assignments a breeze. My name is Jess Stratton, let's go. To get started, we're going to do this in what feels a little backwards, but we have to create the form first. So I'm in Drive, and I got there by going to drive.google.com. I'll click New, and then I'll choose Google Forms. I'll start by giving it a name. It's my assignment tracker. And now we're going to create all the questions, that is, all the things that we want to track. So the first one is the assignment. On the right here, I can choose what it is. This is going to be a short answer. We'll just put a little description about what it is. To create a new one, all the way on the right here, I'll click Add Question. The next thing I want in my tracker is the status of that. And you can change these according to what you want. This is just for an example. It's going to be whatever works the best for you. The more information you put in, the more you can report on it later. So this is going to be a dropdown. Option one, Not Started, In Progress. I'll call this one Need Help. Maybe you get stuck and need some office hours. And finally, Completed. Now we're going to make a checkbox to mark it as done. It's up to you. Some people like to set it as Completed. Some people like the satisfaction of clicking that checkbox. So I'm going to do both methods for you. Next one, Class. Also a dropdown. This is where you list all the classes that you have. Next up we have Due Date. And look at that. Google Forms already knows it's a date and has set it as such. If it doesn't for you, you can just change it in this grid. Next up is the Priority. And it wants to do a linear scale. I'm going to do a dropdown in this case. I'm just going to do High and Low. Now that rating is kind of neat to see, so I'm going to do a difficulty rating just because I think the rating tool is really fun. You can also use this for how many hours it's going to take you to complete the assignment. It's up to you. You can set a number here and you can set an emoji to use. And finally, we'll have a section for Notes here because you may want to include some more detail about what you're doing. Now we'll go over to Responses. I'm going to give this a name so that we can find it in the form. I'm going to give this a name so that we can find it in Google Drive easily. Clicking once inside, it's going to give you the name of that form. I'll click Link to Sheets. We're going to have it create a new spreadsheet. Assignment Tracker Responses is the default and that's totally fine. I'll click Create. And it's been made. Let's fill this out so that we can get some information in here. After this, I'm going to show you a really neat way to be able to input data very quickly. We're going to create an icon right on the home screen of your mobile device. Now to do this, we need to publish this. Here's where you set who can use this form. I'm going to go ahead and click Manage. I'm on a business account. So right now it's defaulting to everybody in my organization. If you want a free one, you might want to set this so that only you can access this. Right now it's only people in my organization. In your case, it will only be you. And that's what you want. I'll click Done. Publish. And now we can start filling this out. I'm going to click Copy right now just so we can fill this out a few times and get some information in there. So here's my form. Let's start putting in some dummy data. Midterm, I would consider that high priority. It's in progress. Put the date of the midterm. And it's difficult. To submit. I'll submit another response. We'll get another one in there. Now we can close out of this tab and open up our Assignment Tracker responses sheet. We've got some data in here. First thing I might want to do is adjust these columns so that you can see this better. This is the default response. This is the way it's going to look when you fill out this form by default. And we are going to change this. Now normally, I would not recommend changing this data directly on the sheet. It's not a best practice. The reason for this is if you alter this text in any way, you are altering the integrity of the data of the people that filled it out. And we don't want to do that. So what you can do is create a new sheet and use a formula like form responses or a query or a filter to get these responses onto a new sheet so that you can manipulate them that way. But for the sake of time and for the sake of the fact that you are the only one using this, we are going to go ahead and manipulate this right on the form data as it comes in. So I did want you to be aware of that. How are we doing? Okay, it's time to start formatting this. Let's have some fun. The first thing I want to do is get rid of this timestamp column because it's just unnecessary. This is the time and date that the form was filled out. So I'm going to hover my mouse over column A, click on this arrow and choose hide column. The nice thing about Google form responses is that Google will automatically put them in this nice formatted table in sheets. So that means that any formatting that I do to these columns will automatically get carried over to any new sheet. Any new data that comes in. One thing I might want to do to this column is boldface it. I'm going to take this text, bold and expand it a little bit because the assignment is kind of important, right? So let's keep going. I want some dropdowns in these fields. To do that, hover your mouse over this table header, click on the arrow and choose edit column type. I'm going to select dropdown. We're going to make this match our form that we filled out. So we've got in progress. You can choose these colors. Not started, I'll make it blue. I'm going to add one more because there were more that we put on our form, but we didn't include them in our sample data. So I need to do it manually. That's okay. Need help and completed. I can rearrange these if I want, for example, not started. We'll put at the top. I'll click done and I'm going to do the same thing to class. We'll do it to priority. And for difficulty, we'll edit the column type. Hover your mouse over smart chips this time and we're going to make it a rating. Now let's add a column at the end. Hover your mouse over the right corner of notes and the plus sign will appear. This is going to insert a column to the right. I'm going to call this one done. So in the column title, I'll change that to done. Now remember when I said I like the satisfaction of checking off a checkbox, we're going to include a checkbox here. So I'll click the down triangle, edit column type and choose checkbox. It's going to put one in every row. I'm now going to add some conditional formatting to the rest of my cells so that when we check this off, it's going to strike through everything else in this list. I'll click format, conditional formatting and the first thing we need to do is apply it to a range. Now I'm going to apply this to B2 through column I. I want to format these cells if the box is checked off. So we need to do that by choosing a custom formula. I can put in my formula now. We always start our formulas with an equal sign. We're testing the value of this cell, which is I2. However, I also want this rule to apply to anything in this table. So I'm going to use a dollar sign here. So I2 equals true, meaning there is indeed a check in this checkbox and if this is true, how are we going to format these cells? We're going to use the strike through text and I also like to make it grayed out. I'm going to not change the background color of the cell and I'll click done. We can test this out right away. Watch what happens when I check it off. And now if I check this one and this one, I can close out of this. So the nice thing about this is if you have this check mark method, you probably don't need this completed here, but it's up to you. That's what I meant when I wanted to give you both options. I'm going to do one more column. I'll click the plus sign. I'm going to change this text and call it due soon. On this due date, I could apply some conditional formatting and if it's due within this week, I could maybe highlight this or make it bold faced and red, but I have found that sometimes Google Sheets when importing data from a form and you try to manipulate it, it has problems. It will break the tape. That's why I'm going to add this extra column here and we're going to use emojis. This is also a nice lesson for you and how to access things on another sheet. Click the plus sign all the way at the bottom left-hand side of the screen. I have my sheet one down here. I'm going to double click inside and rename it to icons. This way, if we want to use some more icons for some other values, we can also use the same sheet and it's not going to mess up our form. I'm going to put in an icon here and I do that by clicking insert emoji. I can put whatever I want for an upcoming due date. I'm going to choose the fire icon. So I'll search for fire. I'll use this one, but of course you can use whatever you want. That is now the value on the icon sheet in cell A1. We're going to come back to our form responses sheet and we're going to reference this now. We are going to put this fire emoji right here in this due soon column if this due date is within the next seven days. We always start our formulas with an equal sign. This is going to be a test. So we're going to start it with an if. If the value in E2, which is the due date here, minus today's date is less than or equal to seven days from now, then we want to insert that fire emoji. Now that fire emoji is on a different sheet and we reference it with a sheet name and an exclamation mark. So right away, it's going on that icon sheet and we want the value in cell A1, but I'm going to make this an absolute value. Remember those dollar signs? I'm going to do that again here. So we're going to have it insert that fire, but otherwise if it's not within seven days, just leave it blank. And now I need to close my formula, so let's hit the enter key and see what happens. There it is. We're going to have it auto fill, so I'll put the check mark. And as you can see here, this is the only date that's within the next seven days. And here's our fire icon. So that's working. This is really cool. We have a fantastic assignment tracker, but as a bonus, I'm going to show you on my iPhone why it was a great idea to make it a form. The only system is one that you actually use. So let's make it really easy for you right now. I'm going to tap open drive. Here's my assignment trackers. We're going to open the form, that is the purple icon. Something fun that I didn't mention before is to tap that palette icon right at the top. Here's where you can change the way the header looks. And I'm going to tap choose image. Here's where you can scroll through and find something that you want. I'll find something schoolish and tap insert, tap apply. Now we're going to tap the chain link icon right at the top of the screen. This is going to give us a link that we can copy into our browser. I'll tap copy, and then I'll paste it right into the iPhone browser. Now we're looking at the actual form. Here's where we can type in our assignment, the class, the status. You have a nice calendar insert for the due date and any notes. What I'm going to do right now is put this on the home screen so that I can access it very quickly. Tap the bookmark icon right down at the bottom of the screen, that is the square with an arrow pointing up. Scroll and then tap add to home screen. From here, I'm going to scroll down, make sure open as a web app is toggled on. This is going to give it more of an integrated app-like feel for your iPhone. I'll tap add, and here it is. Now I can tap right on it, and it's going to pop right up so I can start adding all my assignments. My name is Jess Stratton, and I hope you enjoyed this tutorial. Be sure to subscribe to our channel and our newsletter so you don't miss any more great tips.