Automate sheet based project management

Given the above features, there is no doubt Google sheet is great at data recording, project planning, time tracking, calculation, visulization. Combined with real-time data syncing, this makes it the ideal spreadsheet software for project coordination and team-based tasks.

Many people use Google sheet to track project tasks.

However, just because Google Sheets can handle tons of data for free doesn’t mean that it can solve all your problems. When it comes to the project execution, it lacks some important features.

One example is you want to assign tasks to people. That’s not just a matter of updating a cell. You need make sure people are aware of the assignment. When the due date is close, it makes sense to notify the assignee. The sheets need to work like an automated system. Otherwise it’s just a bunch of data waiting people to watch and update passively.

That’s where Sheet Automation comes to play. By using Sheet Automation, you will take your project management to another level.

Template

Let’s start with a simple project tracking template with three sheets.

• Overview:
• Tracking:
• Archive:
• Logging:

There are some columns including: Priority, Status, Assignee, Summary, Description, Due date. You can add as many as possible.

The status inlcudes Todo, Working, Completed.

The workflow is like:

1. Task is created with the default status To do
2. Task is assigned to people by updating assignee column to the assignee’s email
3. Assignee update the status column to working when they starts the task.
4. Assignee completes the task and updates the status column to completed.

Notification, notification, notification

Notification is probably the most important feature for any project management tool. Google sheet has its own notification feature, however it’s too dumb for any serious use.

When a task is assigned, the priority is changed, or the status is upated, an email should be sent out to ensure the assignee or the team is aware of situation.

Assignment notification example: The assignee is recorded in column E. So we will just define a trigger to monitor the column update.

Explain condition: the condition is used to make sure when the assignee column is not blank (e.g., there is an assignee).

Explain email action: use cc to notify the project owner or other team members.

Any column can be included in the email subject or body.

Due date reminding

One use of notificaiton is to remind the assignee that the due date is close or the task is overdue.

Let’s see how to send out an email 3 days before the due date.

As there is no change in sheet except time passes, we have to use another type of trigger Sheet is scheduled.

Eplain the trigger: it will be triggered every day at the specified time to check every task.

Explain the condition: the condition is evaluated to check the date again today. Tf the current day is exactly 3 days before the due date, an email action is fired.

Similarly automation could be defined to trigger on the due date or 7 days before the due date.

Task moving

When task is completed or marked as Won’t do, is will be convenient to move them to archive sheet automatically. Other than that, we could move the complete task to another sheet.

It’s helpful to do hourse keeping so that the tracking sheet clean and easy to manage.

• Archiving the expired tasks
• Auto sorting based on due date
• Highlight the tasks closet to due date

Task update, logging,

Despite completing task, there are other kind of changes to tasks, like priority, description. It may not be necessary for meail notification for every single change. Probably you don’t want to receive many emails whenever the task is changed. But it’s not harmful to receive some notifications when important fields change. For example, when the priority is changed to HIGH or the status is changed to BLOCKED.

You can even log the changed task by copying it to a separate sheet to serve as history.

Flexible: Every project is unique and Google Sheets enables you to build a tailor-made system. You can adapt it as needs change, without any new software.

Accessible: You and your coworkers can access all of your project data from any corner of the world. All you need is an internet connection.

Compatible with everything: Every other piece of software in the world integrates with Google Sheets. As a result, you can easily pull together information from multiple departments.

Easy to use: There’s no need to onboard your team as they’ll already be familiar with spreadsheets. Consequently, maintenance is easy and there’s no need for IT support.

Businesses of all sizes use a variety of systems to organize their work more efficiently, from a simple to-do list to complex project management software.

If you are a small business, Google sheet may be your best option.

If you’re already using Google Sheets, you should be familiar with the features for project management, such as:

• Basic templates for a project timeline, plans, trackers, etc
• Gantt chart functionality to track your project start date, task duration, and end date
• Charts like a bar chart, stacked bar chart, line chart, etc., for visualization
• Conditional formatting, data validation, and pivot tables for advanced data analysis
• Sharing and real-time collaboration support

Comments

Add a Comment