Automated Project Management in Google Sheets

Andrew Kamphey -

When you have multiple people working on multiple tasks, and you want everyone to be on the page, what do you do?

You can use a Google Sheet.

But managing projects can be an intimidating task in Google Sheets.

It's a lot of data entry, right?

Especially when you have multiple people working on different tasks with different statuses across different projects. Data entry for multiple projects, with different people, tasks, and statuses – it gets messy fast!

I'm no stranger to these issues. I need to do project management in Google Sheets as well as non-Google Sheets Wizards. Manually tracking everything in a regular spreadsheet can quickly become a full-time job in itself. Not only is it time-consuming, but it's also error-prone. Imagine forgetting to update a task status, or having conflicting information across different sheets for different projects.

That's why, I want to share a possible solution I've recently created: an automated project management sheet in Google Sheets. This has made my life much easier, and I hope it can do the same for you. This has been a game-changer for me, and I truly believe it can make your project life a whole lot smoother. Let's dive in and see how it can work its magic for you too!

Why Automate Sheets

1. Track multiple projects and their statuses in one organized Google Sheet.

2. Create dropdown menus for easy status updates (e.g., In Progress, Needs Review, Complete).

3. Assign projects to team members without manual email notifications.

4. Automatically move completed projects to a dedicated "Completed" tab.

5. Send email notifications when a project status changes to "Needs Review."

6. Notify team members via email when they are assigned to a new project.

7. Color-code project statuses and assignees for better visual management.

8. Reduce manual data entry errors by automating status updates and notifications.

9. Keep the main project workspace clutter-free by archiving completed tasks.

10. Use Apps Script to automate repetitive tasks, enhancing productivity.

Set Up Your Automated Project Management Sheet

The first thing we wanna do is to create the basic structure of our project management sheet. The goal is to have a clear and organized way to track multiple projects, the statuses, and the people assigned to them. Examples are the following:

  1. Project: This is the name or description of the project.
  2. Status: The current status of the project (e.g., In Progress, Needs Review, Complete).
  3. Assignee/Name: The person responsible for the project.

Create Dropdown Menus to be user-friendlier

To make our sheet user-friendly, we will create a dropdown menu for the status column. We'll ditch the tedious task of typing out the status for each project every single time. Instead, we'll create a handy dropdown menu for the status column. This way, we can easily select the status of each project from the options. Instead of typing out the status every time.

  1. Click on the cell where you want the dropdown to appear.
  2. Go to Data > Data validation.
  3. In the Criteria section, select List of items.
  4. Enter the statuses: In Progress, Needs Review, Complete.
  5. Specify the cell range where you want to apply your dropdown menu.

To make it visually appealing, we can add some color coding:

  • In Progress: Yellow
  • Needs Review: Red
  • Complete: Green
  1. Click Done.

Assigning Projects to Team Members

In this part, we are going to assign our projects to team members. Usually you have to email each person, and give them a link to the sheet. We'll automate this so you don't have to write that email yourself.

In our example, we'll use two names: Carl and Ben. You can, of course, add more names as needed. Here's how to add the assignees:

  1. In the Name column, type the names of your team members.
  2. Optionally, you can color-code the names for easy identification:
    • Carl: Purple
    • Ben: Blue

Automate Status Changes

When someone changes a status, for some particular statuses, we want something to happen. We want the status change to automatically send an email or add more context. For example we want to move the row from our projects tab to the complete tab. So we don't have to cut and paste the row ourselves and possibly make data entry errors.

The first thing we need to do is to transfer any project labeled as "completed" to a dedicated "Completed" tab. Here’s a straightforward plan to get you started:

  1. Open Apps Script. Go to Extensions > Apps Script.
  • Automate with App Script: We'll create a script that will monitor your project tracker for status changes. Whenever a project status is updated to “completed,” our script will automatically take action.
  • Move Rows Automatically: The script will copy the row of the completed project to a "Completed" tab and then remove it from the main "Projects" tab. This keeps your primary workspace clutter-free and up-to-date.
  1. Coding the Movement function. We will delve into the nitty-gritty of coding this automation.
  • Identify the Row: The script will track changes in the status column. When it detects a change to "completed," it will locate the exact row that needs to be moved.
  • Copy the Row: This identified row will be copied to our "Completed" tab. It’s important that the "Completed" tab has the same layout as the "Projects" tab to ensure a seamless transition.
  • Delete the Original Row: After copying, the script will delete the original row from the "Projects" tab. This step prevents any confusion or duplication and keeps your project list clean. 
//this onEdit will move when complete
function onEdit(event) {
  var row = event.range.getRow()
  var column = event.range.getColumn()
  var editValue = event.value 
  var tabName = SpreadsheetApp.getActiveSheet().getName()

  if (row > 1 && column == 2 && editValue == "Complete" && tabName == "PROJECTS")
  {
    // move the row
    var complete = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("COMPLETE")
    var projects = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PROJECTS")
    var maxColumnsProjects = projects.getMaxColumns()
    var maxColumnsComplete = complete.getMaxColumns()
      // insert a row at the top of COMPLETE
      complete.insertRowBefore(2)
      // copy the row to COMPLETE
projects.getRange(row,1,1,maxColumnsProjects).copyTo(complete.getRange(2,1,1,maxColumnsComplete))
      // come back and delete the row from PROJECTS
      projects.deleteRow(row)
  }
}

// create a function to send email when "needs review"

// create a function to send email when "Assigned"

Automating Email Notifications

To keep everyone in the loop, we'll implement email notifications for different project actions. Here's how we'll do it:

  1. Needs Review Notifications: When a project status changes to "needs review," it's important that the project owner gets notified immediately.
  • App Script Function: We’ll write a script that detects the status change and triggers the email notification.
var column = event.range.getColumn()
  var editValue = event.value 
  var tabName = SpreadsheetApp.getActiveSheet().getName()

  if (row > 1 && column == 2 && editValue == "Needs Review" && tabName == "PROJECTS")
  {
    var to = SpreadsheetApp.getActiveSpreadsheet().getOwner().getEmail()
    var projects = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PROJECTS")
    var projectName = projects.getRange(row,1,1,1).getValue()
    var subject = "Needs Review: " + projectName
    var sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl()
    var body = "Hey just need this project reviewed: " + projectName + "\n\n" + sheetUrl
    GmailApp.sendEmail(to,subject,body)

  }
}
  • Notification Trigger: Whenever a project status is updated to "needs review," an email will be sent to the project owner. This will help your team ensure a timely feedback and review.
  • Installable Trigger Setup: Due to permission restrictions with simple triggers, we’ll use an installable trigger to handle the email notifications efficiently. This step is vital to ensure the script runs smoothly without any permission issues.
  • App Script Function: We’ll write a script that detects the status change and triggers the email notification.
  1. Assignment Notification: Keeping team members informed about their assignments is essential for smooth project management.
  • Assignee Notification: Whenever a project is assigned to Carl or Ben, they’ll receive an email notification. This will help them stay updated on their responsibilities.
  • Change Detection Function: We’ll create a function that monitors changes in the assignee column. When Carl or Ben are assigned to a project, the script will send them an email notification immediately.

Make sure to revisit our triggers and add a new one.

// create a funciton to send email when "Assigned"

function sendAssignedEmail(event){
  var row = event.range.getRow()
  var column = event.range.getColumn()
  var editValue = event.value 
  var tabName = SpreadsheetApp.getActiveSheet().getName()

  if (row > 1 && column == 3 && tabName == "PROJECTS")
  {

    if (editValue == "Carl")
    {
      var to = SpreadsheetApp.getActiveSpreadsheet().getOwner().getEmail()
      var projects = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PROJECTS")
      var projectName = projects.getRange(row,1,1,1).getValue()
      var subject = "Assigned Project to Carl: " + projectName
      var sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl()
      var body = "Hey just you got assigned: " + projectName + "\n\n" + sheetUrl
      GmailApp.sendEmail(to,subject,body)

    }
    if (editValue == "Ben")
    {
      var to =  SpreadsheetApp.getActiveSpreadsheet().getOwner().getEmail()
      var projects = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PROJECTS")
      var projectName = projects.getRange(row,1,1,1).getValue()
      var subject = "Assigned Project to Ben: " + projectName
      var sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl()
      var body = "Hey just need this project reviewed: " + projectName + "\n\n" + sheetUrl
      GmailApp.sendEmail(to,subject,body)

      
    }
    

  }
}

By automating these notifications, we streamline communication and ensure everyone stays informed about important project updates. This setup not only saves time but also enhances collaboration within the team.

Quick Quiz

To test your knowledge from the article.

What is the primary benefit of using an automated project management sheet in Google Sheets as described in the article?

A) It eliminates the need for email communication.  

B) It reduces data entry errors and saves time.  

C) It allows for unlimited project tracking.  

D) It requires no technical knowledge to set up.  

"Getting Things Done: The Art of Stress-Free Productivity" by David Allen
Techniques for organizing tasks and projects effectively.

"Project Management for the Unofficial Project Manager" by Kory Kogon, Suzette Blakemore, and James Wood
A practical guide for those who manage projects without formal training.

"The Phoenix Project: A Novel About IT, DevOps, and Helping Your Business Win" by Gene Kim, Kevin Behr, and George Spafford
A fictional narrative that illustrates the principles of DevOps and project management.

Key Terms Defined

Sometimes there are words that are jargon or some kind of insider information that you have to decode. Here's those possible words:

Google Sheets: A web-based spreadsheet application that allows users to create, edit, and collaborate on spreadsheets online.

Project Management: The process of planning, executing, and overseeing a project to achieve specific goals and meet specific success criteria.

Data Entry: The act of entering information into a computer system or database, often manually, which can be time-consuming and prone to errors.

Status: A term used to describe the current state or progress of a project or task (e.g., In Progress, Needs Review, Complete).

Dropdown Menu: A user interface element that allows users to select an option from a list that appears when the user clicks on it.

Color Coding: A method of using colors to categorize or differentiate items for easier identification and organization.

Assignee: The person or team responsible for completing a specific task or project.

Automation: The use of technology to perform tasks with minimal human intervention, often to improve efficiency and reduce errors.

Apps Script: A scripting language developed by Google that allows users to automate tasks and extend the functionality of Google Apps like Sheets, Docs, and Gmail.

Email Notifications: Automated emails sent to inform individuals about specific events or updates related to a project or task.

Installable Trigger: A type of trigger in Google Apps Script that runs a specific function automatically in response to certain events, like editing a spreadsheet, and requires permission to function.

Clutter-Free: A state of being organized and free from unnecessary items or information, making it easier to focus on important tasks.

Nitty-Gritty: The most essential and practical details of a subject or task, often involving complex or intricate aspects.

Seamless Transition: A smooth and uninterrupted change from one state or process to another, ensuring that everything continues to function properly.

Change Detection Function: A piece of code that monitors specific cells or data for changes and triggers actions based on those changes.