Automated Project Management Notifications in Google Sheets
Learn how to automate project management notifications in Google Sheets using Apps Script. This tutorial covers creating dropdowns for project statuses, sending automated emails, and managing project assignments efficiently.
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. To manage projects internally, if you have multiple people, you have a project, you have a status, and you have some person's 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. And we want to do a few things. One, we want to create a dropdown menu that has some statuses here. We'll click dropdown . We'll add here completed, or complete 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. Once something is in progress, it'll change to yellow, and then when we need a review it will change to red, and complete will be green. 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. We're gonna call this in progress and we want to, or actually we'll call it projects, and we want to create a duplicate of this, And call it complete, but we want to delete all of these items. So let's make a little header here. I do want to just use, just make this a little bit prettier with quicksand 12. We have project status name. We want some names here of people who are in our company. Maybe we assign each person. We have Carl, let's say, 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 automated, to make this 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, what we want to do is add automated emails. So we're going to go to app script . We're going to automate these emails. When statuses are changed, 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. That's just something I do to make it cleaner. I want to also make it have exactly the same formatting as.
The other sheet, 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 and we want the same formatting each time. So let's go back to our app script and start writing our app script to do automations. Oh, when we edit things, we'll use on edit and we'll use an event here as the input. We're going to do this to move. So this on edit will move when complete we're going to need to do another function here, create a function to send email when needs review. We also want to create a function to send email when. Assigned. If we have individuals here, and say I'm a project manager, and I say, I have, I create a project, I say it's in progress, nothing, and I just assign someone, say Carl, I want to automatically let Carl know, hey, you've been assigned this project, go here and check it. Go here, meaning this sheet. So 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 on edit as a simple function or a simple trigger . Rather, it cannot send an email, but we do have a way to on edit, send emails, which I will show you later. But the first thing we want to do is move. We're going to move anything that's completed to the complete tab. 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 Automated Project Management in Google Sheets Automated Project Management in Google Sheets Automated Project Management in Google Sheets Add New Task when Task Completed
as a, actually, we'll use column here. Column is equal to two, making sure that we're editing the second column, double ampersand again, and edit value, the value that we're editing, Is equal to complete, do we say complete or completed, complete, there we go and we want to make sure we're on the right project on the right tab, which is projects. So tab name equals projects. So we want to make sure all of those are true and then we will we'll come here and move the row. Which in 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. What we do is create a function that copies the row to complete.
We have to actually also. Insert a row at the top of complete. Copy it and then come back and delete the row from projects. That's how we quote unquote move something. So what, we gotta get these variables. Variable row equals event. range . getRow. Pretty simple. Variable column. Is gonna be equal is event do range , do get column variable. Edit value, meaning the value that we're editing to will be event do value. That one is pretty simple. Just value. No, just value. It's auto completing. There we go. Variable sheet name . is equal to spreadsheet app dot get active spreadsheet . Actually, not spreadsheet , sorry, sheet get name. Automated Project Management in Google Sheets Automated Project Management in Google Sheets Automated Project Management in Google Sheets How to Protect Spreadsheet From Yourself
This sheet name , actually it's going to 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. 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 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 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, get the variable projects. So this will be the same except a different name, which will be actually misspelled, complete their projects. Now we need to copy the road to complete. So we'll do projects dot. Um, no, we don't need to do that. Actually. We need to variable range to copy. We can just do it this way. Uh, complete dot get range . We're going to go to the row. We want one column and we want. Only one row, I'm sorry, the first column, one row, and then the number Automated Project Management in Google Sheets Automated Project Management in Google Sheets Automated Project Management in Google Sheets Delete A Row When Writing a Certain Word
of columns is going to be maxColumns. We're going to need that variable as well, variable maxColumns equals, and this will be pretty simple, complete dot Sorry, not complete, projects. Projects. getMaxColumns. We also need MaxColumns. complete, so we'll call this projects equals MaxColumns. We'll need that as well. Okay, so we are going to actually project, sorry, projects. getRange , get the row, get all of the columns, get values , nope, we don't need the values , we'll just do copy to . Where are we copying it to? Is we're copying it to complete. Dot get range . We are going to go to the second row because we've inserted a row. So we know the second row is blank. We do one, one and max columns complete.
This should be max column projects here. There we go. And that should come if we say project is complete, it is now entered here, but now we have to go back and delete it from projects. So how do we do that? We just go to projects dot delete row . And which row do we delete? We delete the row that we got up here as well. 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 and making sure that does not copy over here. It's not doing it, but now let's do complete and it should delete this row. 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 changes to needs review. 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. What we need to do Is we're going to use the unedit here as a simple trigger , but we're going to use the unedit as a installable trigger for this one, because permissions for sending email does not allow us to do it through a simple trigger . We need to do a installable trigger , which I will show you again in a second, 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. But we want to make sure it is needs review. And if that is so we will send an email. Who are we going to send an email to? Let's first do Gmail app dot send email. And this is going to be a simple email. It's going to have someone who it's to a subject and a body. So we need to create those variables.
Variable two equals I think it's session dot. Get active user, but we can also get owner . I think it's gonna be spreadsheet app dot get owner , get active spreadsheet , get owner . This should, let's look at this. Ah, we need to get owner , get email. That's what we need. So whoever is the owner of this spreadsheet . Should be the one who is getting these emails. That's what we're doing here. So I don't have to put my own email in there. Someone can use this without putting their own email in. 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? We know what row we're on. We just need to do variable project name equals spreadsheet app dot, Get let's get these, this as well.
We want projects dot get range . The range is going to be the row, one 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. And that is going to be need, need review, needs review, project name. Variable body is equal to, hey, just need this project reviewed. 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, I'm going to do that twice, and then I'm going to put the URL of the spreadsheet . So we don't have to copy paste this URL, it will automatically get the URL for us. So variable sheet URL equals spreadsheet app get active spreadsheet .
If we're running this on edit from here, but we're not going to be running the on edit from inside of our IDE or in our Apps Script , we're going to be running it in our projects. So let's say project test. We need to say needs review, 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. 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. Oh, the one thing that I actually forgot to do was trigger this send review email and I was going to show you that. So now I will show you that right now. So let's go over to triggers. We need actually create the trigger 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. If you go to the bottom like head at Twitter. 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. 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. We cannot grant permission, the right permissions to the simple trigger . So now that we have that, we can delete this needs review. 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 errors or if it is triggered. So now we have a function , send review email, it has a trigger , it has been triggered, we should get an email.
There we go, needs review project test, and we have a link back to the sheet. So the next thing we need to do on our list is we want to create a function that sends email when assigned. So this is great for someone who's working on these projects to let the person in charge know, Hey, we need this reviewed. We already added the. So someone can come in here, complete, and this will move over here to the completed tab. But if I want to assign a project, Carl's project, I'm, it's not in progress yet. It doesn't need a review yet. It needs to get started. And so I want to assign Carl here to the project and have him get an email. 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.
So instead of send email, we'll send assigned email. We want to make sure that the column edited is the third column. So we're going back here, the C column, not the B column. And the edit value is going to be different each time. So I'm going to just delete that for right now. And inside of here, I'll say another if edit value is equal to Carl. We'll do something and then same, but if who's the other name Ben, so depending on who it is assigned to, I'm going to do something. And that's the only options I want to give it. 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 going to edit the email. Let's format this. There we go. Okay. In this one we want, Hey Carl, you've been assigned. Automated Project Management in Google Sheets Automated Project Management in Google Sheets Email When Cell Changes
We want to send a signed email from spreadsheet and on edit. The good thing is that these these edits or these functions will only run really If the, if statements are all true, if all of these are right. 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. We hit Ben and now let's see if we get an email. We'll refresh there. It is already assigned project to Ben's project. If we do this for Carl's second project. and assign it to Carl. Let's see if we get that email. 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? 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. 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 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 bits of code here. This is not that many, less than a hundred lines of code. And you can get the code right here. 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. Become a BetterSheets member and enjoy this automated project management.