5 Ways To Use Google Sheets for Advanced Project Management

Intermediate

About this Tutorial

Here are 5 advanced ways to manage projects inside of Google Sheets Manage Task Flow with IF() and a Checkbox Manage Data Inputs with IF() and ISBLANK() Create Quick Dashboard headers with: Transpose / Unique Create Pickers with Index / Match Email reports with Apps Script

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Apps Script APIs used

MailApp SpreadsheetApp

Apps Script methods used

.sendEmail() .getActiveSpreadsheet() .getRange() .getSheetByName() .getValue()

Featured Formulas

COUNTIF() FILTER() IF() INDEX() ISBLANK() MATCH() TRANSPOSE() UNIQUE()

Video Transcript

<div>00:01 Hello, welcome. So in this video we're gonna go over five ways to use Google Sheets for advanced project management. The most interesting thing is that you're probably bored with your normal project management. 00:13 You've seen dropdown lists, you've seen headers , you've seen how to deal with, you know, tasks and multiple tasks.

But we want to get a level deeper. 00:23 So I'm gonna share with you five things that I think are going to be way more advanced than you've probably seen before. 00:31 First off is, let's say we're reviewing our task list and we want to use the if and a checkbox , because we could just do this, right? 00:41 We can just insert check boxes, let's go insert checkbox right there, <laugh>. There we go. And right, and you see like, okay, I do this task done, I do this task, great, I do this task. 00:55 And you can see the visual representation of the checkbox .

It is checked, it is marked off. But when we want to maybe flow a little bit better or we don't want to get distracted by other things, what we could do is create a list like this. 01:10 So we only wanna see the thing we're working on, or the next thing once we hit that checkbox , we could do equals if, and not a logical expression is going to actually be the one before it. 01:22 So we'll start on the second one. We'll go if, if b2. So we're in C3 right now.

If B2 is checked, meaning true, or if it's false, it's gonna be unchecked. 01:37 So if it's true, we want this, whatever this task is, if we, if it's false, meaning it is not checked, we want nothing, we, we don't wanna see the next task unless we're done with the one beforehand. 01:50 And we're gonna auto-fill here. And if you can check this out, our first one we're gonna put in right away. 01:56 So if we uncheck these, let's move, copy it all the way down. Right Now our task is this to audit this one. 02:06 See, we have no more information here.

We can't see what the next task is. We can't be distracted by anything else. 02:12 And the moment we're done with this to audit, there we go. We see audit. Okay, now we audit the next customer. 02:19 We are done with that. We audit the next customer and we won't know what we have to do next until we need to do it. 02:26 Now you might say, this is silly, the tasks are right there. You can move these to another tab. And they're gone, right? 02:34 And now it still works. It works because this formula , once we've written it, it it moves.

If we cut and we paste, if we cut and copy, or you can set it up on another page, you can say, we'll create a new page of the test I need to do today and I wanna flow through this. 02:51 I just wanna work on the thing that I'm working on right now. Come back to it, hit done, and work on the next thing. 02:56 That is it. We could create this in another tab.

So we don't necessarily need to cut and move this to another place. 03:03 We could actually just create this new task list that we want to manage our project today, our task right now, we can move, we can create that on another sheet. 03:15 Cool. I hope this could help you with like focusing on more kinds of tasks and projects. So a similar thing, say it is not so simple as a checkbox . 03:25 Let's say we have to put some data in here. We have to write some number .

Maybe we have to write the number of sales. 03:31 Maybe we're crabbing the number of sales from somewhere else, and we're putting in here. So in this case, it's not a if if true or false, a checkbox is checked or not. 03:41 We go equals if, and we say is blank, B L A n K. Now we put the value as b2 again, the one right above it, b2. 03:52 Now this gets a little weird because if it is blank, it is true. So what if it is empty? It's true if it's not empty, if there's something there, it's false. 04:01 So this way we say if it's true, if it is actually blank, we want nothing.

If it's false, meaning there is something there, we want this task. 04:10 So we'll do D three. So now if I write 45 in here, see there my task has shown up the next one. 04:18 And if I copy paste this all the way down, see and now I just type in the sales 33 here, 21 here. 04:27 We now have that task. Again, again, this helps us with flowing. It helps us with data inputs if we want to focus on the data input at hand and not get distracted by anything else. 04:38 And also, it honestly helps us put the data in the right place.

Because if I put the data here like randomly, I'm like, oh, I realized that I, oh my god, I have this blank right here. 04:48 That's not where I meant to put it. Meant to put it here. Oh, okay, okay, got it. It, it's a much easier way to see if I got that correct in the correct place. 04:55 Sometimes we can get blinded by a lot of texts, a lot of black and white, a lot of texts all over the screen. 05:01 It can get a little you know, daunting.

Now the next one that I want to talk about is transpose and unique using Transpose and Unique to create a really quick dashboard . 05:12 We have all these tasks and they're all arranged. See we have a dropdown list and we've already created those.

Like we have those in mind. 05:17 We know, okay, it's one of these three things, but sometimes tasks will come up or projects will come up where you just start writing sort of categories and you don't know how many categories there are here and you don't really know how many are in each category. 05:29 You're like, ah, there's a bunch of reviews. There's a bunch of audits I have to do. Yeah, there's some consultations, which ones are which. 05:35 Well, we can create a new sheet right here and we can, we can do equals unique .

And the range we're gonna pick is this B column, but we're gonna change this to b2. 05:44 We don't want that header in there. And now what Unique does is just gets us one of each that exists. 05:50 So there's only three things there. If we add in, let's say change this to social media review, maybe, maybe someone accidentally wrote that instead of review, it'll show up here. 06:06 So we ha see that. Now what is interesting is we can wrap this with the transpose formula , which literally just DR changes it from a column to a row. 06:17 And now we have headers .

So we can do really cool things at this. We can say either, you know, count if, maybe we say count if sorry, count if in this column and the criterion is B one here, we can say how many there are. 06:37 We need to actually put the dollar sign in front of that B and now we can copy paste this. Now we know how much, how much of these things are. 06:45 We can also do filter , filter that maybe the customer names or the company descriptions, the taglines or the company IDs. 06:53 And we go here, filter it, and we say the condition is, what was that?

Column B is equal to audit. 07:04 So now let's put the dollar signs in front of the Cs and the BS so we can copy it over. 07:09 So those don't change, but the B one's gonna change. And now we have a really cool sort of con bond things we can see, we can view, okay, here's SCH Schumer. 07:18 And we can see, okay, who's in what place, how many are there?

Oh yeah, there are 45 in this column, right in this category. 07:26 We can make a really cool header and cool summary here on another page very quickly when working with lots of projects or customers or clients. 07:33 And we want to say like, let's say we want to invoice someone and we're gonna have to go to another program to do that. 07:40 But maybe we can create like an invoice here, invoice someone for you know, sales of this amount. We can make a very quick, this is very dirty quick. 07:52 Invoice who? Right? Invoice. Let's call this invoice.

And then we want to say this is customer. Who, who are we doing? 08:00 We, we have maybe the sales here or we have the task that we did. We have the company ID here. 08:06 We have to copy all of this stuff over here to this invoice page, right? That's gonna take a long time. 08:12 Unless we can create a little picker here. We can say we want to change all of the information, right? The company description, tagline and id, we want all of this stuff here, but we don't want to copy paste it in. 08:33 We only want to change the customer. So we're gonna do this.

We're gonna create a dropdown here from a range . 08:39 The range is going to be the comp customer list here, C to C on tasks C, colon C done here. 08:53 Now let's look at this. We have a dropdown of all of the customers. Let's pick one. And now using the dropdown plus index match index , what are we indexing? 09:06 We want to get the description. We're gonna match the company name here, search type. I always put zero. And now we have Bist area, Truffer, persistent realtime approach. 09:23 Let's see B exactly. We got exactly their company description and now we can move along.

Just take that copy and paste it and change the index to E for their tagline. 09:36 Incentivized killer systems. Oh my god, these were literally random. Was it E oh, we now need f change just the column of the index . 09:46 And now just by changing the name of the company here or the customer, we now change all of this data . 09:54 So we can do this for invoices, we can check their statuses. So maybe we don't want to scroll down and have to search for Harvey Quigley here. 10:02 Oh, it's, see, they're done. We can check their status here just with this index match .

And this is gonna be a thing, column A done. 10:13 So now let's pick another customer lab body group started. So now we can see their, their status. We can invoice them, we can create a little summary for that particular customer. 10:24 All that information in that row is now we can move it around all places, all around here. Now what if we want to get a report, and we don't want to check this sheet every single day, but we want to check the number . 10:36 Like the business is, we want to do sales, we wanna get our customers in here. So let's do this.

We're gonna go onto our summary page. 10:42 We're gonna call this summary with a capital S. And right here in a one we're gonna have a sum of two to two. 10:52 Actually, I'm gonna just sum up all of the two Co, the the second row just in case we add any more statuses here. 11:00 But I just wanna see how many are in our pipeline and I wanna see that every single day. So all I want is this a one number emailed to me. 11:07 Let's go to extensions. This is super simple. Go to app script and deal with this. Let's write this script.

Write now right here. 11:15 So we're gonna call this dysfunction send report. And we just want that in our, in an email, in a, in a body of an email. 11:23 We want this number a one on summary. So let's do this. Let's first do mail app cause that's what we're gonna do. 11:31 We're gonna mail, we're gonna do dot send email. What is gonna be the email? The email is gonna have three things. 11:36 It's gonna have an email to send it who we're sending to. We're gonna have a subject, subject and we're gonna have a body or a rather. 11:44 We just want the data .

We just want that number in our email. So what you gonna write data , variable data is going, actually let's start at email variable email is equal to just whatever your email is. 11:56 My email is andrew be sheet.com. There we go. Our subject, it's gonna be, this is gonna be interesting. We can say today's report and then we can add a plus new date, not data date for our data variable data . 12:17 This is gonna be the longest one, hardest one. Follow along spreadsheet app. And we can start typing and then we can select it from here. 12:23 This is capital S, capital a spreadsheet app.

We can hit a the dot or a period, get active spreadsheet . We're gonna add parenthesis here. 12:32 That's one thing that they don't do is they don't add parenthesis there At the end we're gonna add another dot. 12:37 We're gonna say get sheet by name right here. Get sheet by name. Add parenthesis. Now we need to put summary here cause that's a sheet that we're on next. 12:49 What cell is it in? It's an A one. That's the range we're gonna do get range . And in quotes, we're gonna do a one. 12:56 Now we got the cell, we got the, the sheet, we got the spreadsheet file.

The thing is we need the value inside of that, the actual number . 13:03 So we gonna do equals or not equals, sorry, dot get value, add those parentheses. But now this data is the data that's inside of the cell. 13:15 A one un sheet summary and that's all we need. You hit command s we make sure we have that code all done. 13:22 And, and once it saves, it should be correct. If there were any errors there will tell us here with this particular syntax . 13:29 But let's see, we have to do now. It's not saving, is it? Oh yeah, it's saved.

Okay, <laugh>, I just didn't hit save. 13:39 You can also hit this save project up here, that little button. First thing I'm gonna do is I'm gonna hit run without doing anything. 13:46 I have to hit run here because I think I need to authorize. Yeah, authorization is required. Definitely wanna do that. 13:53 Do do do do do do do do allow send email. It's gonna send an email as us. It doesn't send that email yet. 14:02 We're going to run actually a little test. Let's see if it works.

I'm gonna hit run again and if we had an error , it show up right here, but it doesn't. 14:14 And I can see right here, I have sent the email. Just checked right there. It is done. It worked. Awesome. 14:23 All right, let's go. Actually make this happen every single day. We're gonna go over on the left side, this, this little timer go to triggers. 14:29 Remove my face a little add triggers on the bottom right here. I'm gonna send report. We're gonna choose that function . 14:35 If that's the only function you have written there, that's the only function that'll to show up here.

Keep the head from spreadsheet . 14:40 No, we wanna change the event source to time driven. We want a Daytimer. And then we want to select the hour in the day that We want, want to send it. 14:52 So I'll select at the end of the day, like actually I'll probably select in the morning like three to 4:00 AM I wanna get that report before I wake up and check my email. 15:00 Right? Hit save. It might ask for more authorization . It does. So we'll go through that and once it saves, it'll be written right here. 15:12 You can always delete this.

You can always click on the three buttons on the right side. I should move my face. 15:16 Again, delete trigger . You can always delete it. You can always see any error reports. If it comes in, you might see errors in executions as well. 15:25 Feel free to email me with some questions if you have them. Happy to answer them. Hopefully this was really helpful. 15:32 And now we can send an email directly from a sheet to manage that project. To manage that stats.

We can create invoices, we can create little summaries with just just picker. 15:43 And with index match we can do all kinds of things with if and it's blank. And create headers here with transpose and and unique hope. 15:51 This was very helpful to you. To now do advanced project management inside of Google Sheets.</div>