Automated Project Management in Google Sheets

Intermediate

About this Tutorial

Create automatic notifications when a project is assigned, a project needs review. And move a project from one tab to another tab.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Apps Script APIs used

GmailApp SpreadsheetApp

Apps Script methods used

.sendEmail() .copyTo() .deleteRow() .getActiveSheet() .getActiveSpreadsheet() .getColumn() .getMaxColumns() .getRange() .getRow() .getSheetByName() .getValue()

Video Transcript

<div>00:00 So what I want to do today is create an automated project management sheet. This is something that I personally made myself recently, so it's pretty much on the top of my mind. 00:10 To manage projects internally, if you have multiple people, you have like a project, you have a status, and you have some person. 00:20 name assigned, or assignee, or someone who's assigned to this project and they have some status. So we have, let's say, project 1, project 2, and project 3. 00:32 And we want to do a few things.

One, we want to create a dropdown menu that has some statuses here. 00:38 We'll click drop-down we'll add here. Uh, completed, or complete, uh needs review, and we'll say in progress. So we have three, we can say yellow for this one, needs review has a red, and complete is green. 00:55 Uh, once something's in progress, it'll change to yellow, and then when we need a review, Thank you.

Ah, it will change to red, and complete will be green. 01:03 But, if we have a number of projects here, let's change this to colon b, if we have a number of projects here, it's gonna get pretty messy here. 01:12 So, we're gonna call this in progress, ah, and we want to, or actually we'll call it projects, and we want to create a duplicate , of this, and call it complete. 01:23 But, we want to delete all of these items.

So, let's make a little header here, um, I do want to just use, let's make this a little bit prettier with quicksand, twelve, we have project status name, we want some names here of people who in our company maybe we assign each person, we have Carl, let's say 01:45 , and Ben, just two, we can give them colors as well, Carl is purple, and Ben is blue.

Okay, we can have that there, and what we want to do is a few things to this, to make this really awesome. 02:00 Automated, to make this ah a place where we can keep track of what projects are completed, what's in progress, who is it assigned to, and if they're completed or not. 02:14 But what we want to do is add automated emails. So we're going to go to Apps Script . We're going to automate these emails when statuses are changed. 02:24 We're also going to move anything that's complete over to the Complete tab.

So one thing about the Complete tab I want to do is delete everything except the first two rows. 02:35 That's just something I do to make it cleaner. I want to also make it cleaner. have exactly the same formatting as the other sheet. 02:47 You'll see why soon because basically what we're going to do is we're going to insert a line above here each time it's complete. 02:56 And we want the same formatting each time. So.

Let's go back to our Apps Script and start writing our Apps Script . 03:05 To do automations when we edit things we'll use onEdit and we'll use an event here as the input. We're going to do this to move. 03:15 So this onEdit will move when complete. We're going to need to do another function here. Create a function to send email when needs review. 03:32 We also want to create a function to send email when assigned.

So if we have individuals here here, and say I'm a project manager, and I say I have, I create a project, I say it's in progress, or nothing, and I just assign someone, say Carl, I want to automatically let Carl know, hey you've been assigned 03:53 this project, go here and check it. Uh, go here meaning this sheet. Okay.

So let's, let's, do that there, and we're going to have to do these separately because there's a little bit of an issue with onEdit as a simple function , or a simple trigger rather, it cannot send email, but we do have a way to 04:13 onEdit send emails, which I will show you later, uh, but the first thing we want to do is move anything that's completely needed to the complete tab. 04:22 So how do we do that?

First, we need to make sure we get the row, we need some variables basically, because we're going to say if row is greater than one, meaning it's not the header , and column, or c-o-l, we're going to use as a, actually we'll use column here. Column is greater is equal to two, making 04:43 sure that we're editing the second column, double ampersand again, and edit value, the value that we're editing, is equal to complete. 04:55 Did we say complete or completed? Complete. There we go.

And we want to make sure we're on the right project, uh on the right tab, which is projects. 05:07 So tab name equals projects. So we want to make sure all of those are true, and then we will, uh we'll come here and move the row. 05:18 Which in, uh. that we will copy the row to complete, and we will come back and delete the row.

So that's what moving really is, there's not a single function that says hey move this row. 05:37 What we do is create a function that copies the row to complete, we have to actually create actually also insert a row at the top of complete, uh copy it and then come back and delete the row from projects. 05:53 That's how we quote unquote move something. So what we got to get these variables variable row equals.

Event dot range dot get row pretty simple variable column is going to be equal is event dot range dot get column variable edit value meaning the value that we're editing to will be event. 06:20 dot value that one is pretty simple just value no just value it's it's auto completing there we go variable sheet name is equal to spread sheet app dot get active spread sheet actually not spread sheet sorry All right. 06:40 sheet get name so this sheet name actually it's gonna be tab name here tab name is going to be projects and it's this name here we can also log these if we wish um but let's cut first insert a row at the top of complete which we need variable complete equals spreadsheet app dot get active spreadsheet 07:08 dot get sheet by name complete so we don't have to type that over and over again we can just say complete dot insert row after actually we want before three and nope sorry before two the issue here is if we insert a row underneath the first row it will have the formatting of the first row but if we insert 07:36 a row above the second row it will have the formatting of the second row.

So that's really important to know we want to insert there okay we did that now copy the row from projects to complete so we have the row that we're editing we know that we're editing it to complete so all we need to do is again 07:59 get the variable projects so this will be the same except a different name which will be actually misspelled complete there. 08:10 Projects. Now we need to copy the row to complete so we'll do projects dot umm. No we don't need to do that. 08:22 Actually we need to variable range to copy. We can just do it this way. uh complete dot get range .

We're gonna go to the row. 08:37 We want one column and we want only one row sorry the first column one row and then the number of columns is gonna be max columns. 08:47 We're gonna need that variable as well variable max columns equals and this will be pretty simple complete dot. Sorry not complete projects. 08:59 Projects dot get. Max columns. We also need max columns complete. So we'll call this projects equals complete dot get. it. 09:20 Max columns. We'll need that as well. Okay, so we are going to actually project. Sorry. Projects dot get range .

Get the row, get all of the columns, get values . 09:33 No, we don't need the values . We'll just do copy too. Where are we copying it to? Is we're copying it to complete? 09:39 complete. 3 3 3 3 3 3 Umm. Get range . We are going to go to the second row because we've inserted a row. 09:49 So we know the second row is blank. We do 1 1 and max columns complete. This should be max column projects. 10:00 Here. There we go. And that should come uhh if we say project is complete. It is now entered here. But now we have to go back and delete it from projects. 10:17 So how do we do that?

We just go to projects. Delete. Row. And which row do we delete? We delete the row that we got up here as well. 10:26 So that is saved. And we're only doing that if it's complete. So we can check this by doing something like needs review. 10:34 And making sure that that does not copy over here. It is not doing it. But now let's do complete. And it should- Delete this row. 10:42 There we go. It's deleted the row. Fantastic. So we have our automation done on complete. But the next thing is we want to send an email when someone uh changes to needs review. 10:54 Okay.

Let's do that down here. I'm going to create a new function called send review email. We have an event here as well. 11:08 What we need to do is we're going to use the on edit here as a simple trigger . But we're going to use the on edit as a installable trigger for this one because permission. 11:20 For sending email does not allow us umm to do it through a simple trigger . We need to do a installable trigger which I will show you again in a second. 11:31 But we will use exactly the same stuff here. So we're going to use all this as well.

We're having the same event umm but we want to make sure it is needs review. 11:48 And if that is so, we will send an email. Who are we going to send an email to? Well let's first do Gmail app. 11:55 Gmail app.send email. And this is going to be a simple email. It's going to have some- one who it's to, a subject and a body. 12:05 So we need to create those variables. Variable two equals umm I think it's session dot get active user. But we can also get owner . 12:16 I think it's going to be spreadsheet app dot get owner . Get active spreadsheet get owner .

This should, let's look at this uh ah we need to get owner dot get email. 12:32 That's what we need. So whoever is the owner of this spreadsheet should be the one who is umm getting these emails. 12:40 That's what we're doing here. So I don't have to put my own email in there. Someone can use this ah without putting their own email in. 12:46 So the subject variable subject should probably be needs review and we want to get the project name. Project name. How do we get the project name? 12:59 Well we know what row we're on.

So we just need to do variable project name equals spreadsheet app dot git. 13:07 Umm. Let's get these this as well. We want projects dot git range . The range is going to be the row. 13:21 One uh one column, number of rows one, number of columns one. So this is just the very first thing in A column and we're going to get value. 13:32 And that is going to be need review, needs review project name. Variable body is equal to. You Hey, just need this project reviewed. 13:45 Umm. What we want to do here is also put project name. And then, it's going to be interesting.

Plus, we're going to create a slash N is a new line. 14:00 line. . I'm going to do that twice. And then I'm going to put um the URL of the spreadsheet . So we don't have to copy paste this URL. 14:11 It will automatically get the URL for us. So variable sheet URL equals spreadsheet app get active spreadsheet . Get URL.

That's pretty simple. 14:23 And we're just going to include that in the email so that we can click on here and make sure if there's any other information here we just want that project name that needs to review. 14:32 Maybe we have some extra columns here of information like uh where the ultimate link is, whatever the project is. Is it happy? 14:40 I mean umm notes on it, updates, last date, whatever. Wanna do that. So, we're gonna send that email. Let's test that email now. 14:53 Let's see we have an invel left and say 36. Oh, this is, This should be a plus sign.

There we go. 15:02 Nice handy error message. We wanna automate project management. So the first time we run this, we will need to authorize it. 15:20 So, We will get an error here, but we just wanna authorize all of this. Let's allow. So the error will always come up that the range is undefined here. 15:32 If we're running this on edit from here. But we're not gonna be running the on edit from inside of our IDE or in our app script . 15:39 We're gonna be running the, running it in our projects. So let's say project test.

We need to say needs review. 15:49 And this should trigger the email. And so we can always see here in executions if there is some failure. Like this one was a failure, but it was the one we, ran. 16:01 We can look at our inbox and see if we get an email. Let's look again. Needs review. Let's make sure everything is hunky-dory. 16:16 Oh, the wh- One thing that I actually forgot to do was trigger this send review email. And I was gonna show you that. 16:25 So now I will show you that right now. So let's go over to triggers.

We need actually create the trigger . 16:30 In the instance of a simple trigger we do not need to create it, but now we need an installable trigger because we have those email permissions. 16:38 So we go to the bottom right add trigger . Bigger. We're going to choose our function here. Send review email. We do want the event source to be from spreadsheet , but we want to change it to on edit. 16:49 So this sounds the same as using the simple trigger on edit, but it allows us to send emails because we cannot send an email. 16:58 We cannot give grant permission.

In the right permissions to the simple trigger . So now that we have that we can delete this needs review. 17:08 Uh let's say we've assigned it to Carl. Needs review. And now we can go back to our executions and see if there's any error . 17:20 Or if it is triggered. So now we have a function send review email. It has a trigger . It has been triggered. 17:27 We should get an email. There we go. Needs review project test. And we have a link back to the sheet. 17:35 Perfect. So next thing we need to do on our list.

Is we want to create a function that sends email when assigned. 17:44 So this is great for someone who's working on these projects to let the person in charge know hey we need this reviewed. 17:53 We already added the complete. So someone can come in here complete and this will move over here to the completed tab. 17:59 through the system for Automate this But if I want to assign a project. Carl's project. I'm it's not in progress yet. 18:09 It doesn't need a review yet. It needs to get started.

And so I want to assign Carl here to the project and you'll have him get an email. 18:18 So let's look at what we need to do for that. We want to copy this send review email function and we'll edit this. 18:27 So instead of send email we'll send a signed email. We want to make sure that the column edited is the third column. 18:36 So we're and the edit value is going to be different each time so I'm gonna just delete that for right now. 18:50 And inside of here I'll say another if if edit value is equal to Carl we'll do something.

And then same but if, who's the other name, uh Ben. 19:09 So depending on who it is assigned to I'm gonna do something. Umm and that's the only options I want to give it. 19:18 I don't want anything else to happen. So, we are going to take this whole email and we will send an email but we're just gonna edit the email a bit. 19:31 Let's format this well. There we go. Okay. In this one we want, hey Carl you've been assigned. Assigned. Signed project. 19:46 We have the project name as well. We want the two to be someone else.

So, I'm going to put my own email here. 19:57 But this could be, you could type literally anyone's Carl's email right here. We need the URL, ah hey just you got assigned this project and check it out at this URL. 20:16 Same down here.

I'm going to use my own ehm- ehm- ehm ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm ehm- ehm-- ehm- ehm-- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- ehm- e And assign email, we need to go back to our triggers and add a new trigger . 20:48 Add a trigger , which function to run, we want to send a signed email from spreadsheet and on edit. The good thing is that these uh, these edits, or these functions will only run really, if the if statements are all true, if all of these are right. 21:08 So let's look. Let's assign Ben a project.

Ben's project. We don't need a status yet because it hasn't been started. 21:19 We hit, Ben. And now let's see if we get an email. We'll refresh. There it is. Already assigned project to Ben. 21:27 Ben's project. If we do this for Carl. Carl's second project. And assign it to Carl. Let's see if we get that email. 21:40 There we go. We got Carl's second project. So assign project to Carl. And now we have a link. So we can add interesting stuff here, right? 21:47 If we had more values , if we had more information, we could add it.

But the good thing is that we have this link to the sheet that this is being sent from. 21:56 So if Carl needs to update this, he can go and update it. To in progress. And if you if he needs a review, he can click needs review. 22:06 And we will get that reviewed. Email. There it is. Needs review. Carl's second project. So that's pretty cool. We've now created a completely automated project management suite with just a few. 22:20 Bits of code here. This is not that many less than a hundred lines of code.

And you can get the code right here. 22:27 If you're a member of better sheets, grab it right away. If you're not a member of better sheets and you're watching this somewhere else, uh become a bit, become a better sheets member and enjoy this automated project management code.</div>