Automate task tracking for Google Sheets

Google sheets is a great project task tracking tool with its ability for data recording, calculation, visulization and real time collaboration.

However, when it comes to the project execution, Google Sheets lacks some important features that you get from normal project management tools like notification, archiving and change logging. Without these features, 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.

Install Sheet Automation extension

If you haven’t installed Sheet Automation, it is an extension in Google Workspace Marketplace and click “Individual Install” to install the extension.

Once it is installed, go to sheet menu Extensions - Sheet Automation and open it. A sidebar should appear on the right side shortly.

Project Template

Let’s start with a simple project template.

There are 3 sheets:

  • Tasks: this is where we track all project tasks.
  • Archive: this sheet is used to record the expired tasks. The sheet structure is identical to Tasks.
  • Logging: a plain sheet to record change history

And We have a basic workflow:

  1. Task is created with the default status To do.
  2. Task is assigned to people in the assignee column.
  3. Assignee update various columns when they work on the task.
  4. Task is archived when it is completed or marked as Won’t do.

Notification, notification, notification

A task is created without an assignee. When it is appropriate, the project owner or team member will update the assignee column. That’s not just a matter of filling a cell. We need make sure people are aware of the assignment.

With Sheet Automation, creating such notifications is in a breeze.

The rule is pretty much self-explanatory. The trigger will monitor E column where assignee is recorded. And the condition is used to ensure when the column is not blank (e.g., there is an assignee).

In the action part, we will extract the email address from the assignee column. That’s why we use a variable in the recipient field. Variable is supported to pull relevant information out of the sheet in subject or message as well.

This is it. Every time we update the assignee column, an email will be sent out instantly.

Likewise we could send more notifications to ensure the team is aware of change when the priority is changed, or the status is upated.

Due date reminder

Another good 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.

The rule runs on a daily basis using a timer based trigger. It will be triggered every day at the specified time.

It’s important to specify the option “Trigger on every matched row” so that the rule checks every task.

The condition means that “when today is 3 days before the due date”. If it matches, the email action will be fired.

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

Archiving tasks

When task is marked as completed or Won’t do, it will be convenient to archive them automatically.

Note there are two conditions to check if the status is Done or Won’t do in the rule. As long as one of them is satisfied, the task will be moved to Archive sheet.

Logging changes

When team are working on the project, tasks may be constantly updated. While it is not necessary to notify the team for every single change, it would be helpful to track changes as logging.

The rule monitors a range of the sheet. Whenever there is a change within the range, the whole row will be copied to the logging sheet. And additionally, a timestamp column will be added to the log row. This is useful to record the time when the change is made.

Conclusion

We just walked through a few things that can turn the sheet into an automated system for project tracking. You can check out recipes to see what you can do with Sheet Automation.

And there are many more features on the way.

Comments

Add a Comment