Task List in Google Sheets with Kanban & Automated Status Tracking
Updated: Apr 9
Efficient task management is essential for maintaining productivity. This tutorial will explore a powerful solution to streamline task management using Google Sheets. By leveraging the capabilities of Google Apps Script, we can automate the tracking of task statuses and enable the seamless movement of tasks between different sheets based on their status in a given tab.
In addition to automated status tracking across sheets, it’s possible to visualise all tasks in a Kanban-style task board that updates automatically as tasks move between sheets; plus, each task can have its own Google Doc associated with it to really get creative with our ever-growing to-do lists.
Kanban Board
Visualising tasks on a Kanban board can motivate you to get things done. You can see all tasks at a glance and check their status. If you’ve never tried a Kanban board, you’re missing out on improved productivity when it comes to task management. Using a task list in Google Sheets with Kanban board & automated status tracking is the only task management tool you'll need.
To-Do Task List in Google Sheets with Kanban & Auto Status Tracking
Keeping your task list as simple as possible will make the process of managing tasks straightforward. If you have too many columns to complete, it will become a task in itself to manage.
Linking a Google Document to a task title like this will allow you to write down what’s important and log specific links and images or add checklists to break tasks down into smaller chunks in a space more suited to this type of media. Spreadsheets are great for organising and listing tasks, but a Google Document is the perfect companion when you want to elaborate on a task with lots of extra information and details.
The good news is I have two templates ready to go: a basic task management spreadsheet with automation to move tasks to the relevant sheet as detailed in this article and a Pro Task Management spreadsheet with all the bells and whistles, including the Kanban board and Google Docs to name a few. You can find them both here.
The Pro Task Management Template is also available via Etsy.com
There is a third option, which is similar to the Pro Task Management template, but this new addition also includes a fully dynamic calendar to keep track of your tasks over time; you can find this here.
The To-Do Task Tracker with Calendar Template is also available via Etsy.com
Check out this Project Management template for anyone looking for the full package, including everything from above, plus a Timeline Gantt Chart and many more features.
The Problem: Manual Task Tracking
Manually tracking and updating task statuses in a spreadsheet can be time-consuming and prone to errors. For example, imagine a spreadsheet with columns for Task, Description, and Status. The Status column provides dropdown options. Manually updating the status and moving tasks between sheets can become tedious. Alternatively, displaying all tasks in one gigantic list can be overwhelming and counterproductive. This is not how to manage tasks; that much is certain.
The Solution: Automated Status Tracking with Google Apps Script
Google Apps Script provides a powerful way to automate tasks in Google Sheets. By utilising Apps Script, we can create a custom function that moves tasks between sheets based on their status. Don’t worry if you’re unfamiliar with Google Apps Script; I’ll provide you with the full script and the steps to implement it in this tutorial, or you can skip this and download the free basic Task Management Spreadsheet here or the Pro Task Management Spreadsheet here.
Before we dive into the steps to create this, it’s best to understand how the Spreadsheet works and task management in general, so let’s explore that now.
Task List Management in Google Sheets with Automated Status Tracking
In this example, the Spreadsheet has six main tabs where tasks will live, and the script will move the task to one of those tabs depending on the selected status. The tabs I have chosen are a proven method for task management:
Backlog
Waiting
To-Do
In Progress
Completed
Archived
To put these tabs into perspective, here's a description of each task status that is commonly used in task management:
Backlog: The backlog status typically represents tasks identified and recorded but not prioritised or assigned to a specific timeframe. These tasks often await further analysis, evaluation, or planning before moving to the "To-Do" status. This is where you can capture and log your ideas or tasks to ensure they are not forgotten.
Waiting: The waiting status is assigned to tasks dependent on external factors or resources beyond the control of the person or team responsible. These tasks are put on hold until the necessary conditions or inputs are met.
To-Do: The To-Do status indicates tasks planned and scheduled to be worked on. These tasks are typically assigned to individuals or teams and represent the immediate or upcoming work that needs to be accomplished.
In Progress: The In Progress status indicates tasks being worked on or actively developing. It signifies that the task has moved beyond the planning stage and is actively being executed.
Completed: The Completed status is assigned to tasks that have been finished or fulfilled. When a task reaches this status, it signifies that all the necessary work associated with it has been completed successfully.
Archived: The Archived status is used to categorise tasks that are no longer active or relevant but are retained for historical purposes or reference. Archived tasks are typically older or completed tasks that are no longer actively tracked or prioritised.
These task statuses are commonly used in task management systems or tools to clarify the progress and state of individual tasks, helping teams or individuals stay organised and effectively manage their workloads.
That said, let’s look at how task list management in Google Sheets with Automated Status Tracking works in practice.
Planning Tasks
Planning tasks from the backlog and moving them into the To-Do status when you intend to work on them is a good idea. Personally, I plan out a week of tasks from the Backlog, stuff that I want to work on for the next 5 days and move them into the To-Do status. Depending on the complexity of the tasks, I add around 10-15 tasks per week. It’s best not to be too ambitious; you should be realistic about what is achievable, considering the various distractions and unforeseen circumstances that usually arrive.
You should never have more than three tasks in the ‘In Progress’ status since you can only work on a few things simultaneously; one or two is preferable. If this status is overloaded, you will struggle to do anything productively.
You can move new tasks from the To-Do list into the In Progress list as tasks are completed. If something crops up while a task is being worked on, moving the task into a ‘Waiting’ status is good practice. Usually, tasks are added to this status from the To-Do or In Progress statuses. You might plan to start a task or get started on one but subsequently realise you need something else or someone else to move this task along.
Priority is a key indicator to help decide what you should consider when planning to move tasks from the Backlog status to the To-Do status, so with that in mind, when tasks are created, you should give this some thought. It’s not wise to prioritise everything with a high priority; you need to be ruthless and default to a middle ground if in doubt.
In this Task Board, I set priorities from 1 to 5, with 1 being the most important and 5 being the least. When you create a task, if you know this should be a high priority, then go ahead and set this to 1 or 2, and equally, if you know it’s a low priority, set this to 4 or a 5; otherwise, if you’re not sure, set this to a 3. Most tasks will fall into this category, but you can change this later if necessary.
Adding labels can be really effective if you want to draw attention to something specific to that task. I have set up a few basic labels in this sheet, but you can change them to whatever works for you.
I have a settings tab in this sheet to set things like the status names for the dropdowns and the labels. You can change the labels here if you need to. It’s best not to have too many labels; you won’t use them. I find these three labels tend to work well in most cases:
Discuss
Follow Up
Urgent
Discuss is used to highlight that I need to have a discussion with somebody to move the task along. Once the discussion has taken place, the label can be removed.
Follow Up is used to highlight that I am waiting for some information from someone else to move the task along. Adding this will remind you to check in and get an update.
Urgent is only used in a very small number of cases where the priority alone does not stress the importance of the task. Labelling the task as urgent draws attention to ensuring this takes precedence over other tasks, particularly if you have a few with a priority of 1.
Finally, adding a due date is an option, although not essential in every case. If you have a deadline to work to, adding a due date can help to ensure this is met. This is great for tasks that are sat in the backlog as you can quickly identify tasks with a due date, but equally, when a task is active in another status tab, you can see which tasks to prioritise.
I’ve added conditional formatting to highlight overdue due dates, which can be another useful indicator of importance. Tasks that have a due date in the past will show in red, tasks due today will show in green and tasks due in the future will show in black.
Comments, Images, Links and Checklists
Speaking from experience, tasks often require more context than a title or description alone can provide; therefore, adding comments, images, links or even sub-tasks or checklists can be a valuable addition to ensuring you have everything you need to get the job done.
In this free template, I’m providing a basic task management spreadsheet allowing you to move tasks between sheets automatically.
I also have a separate comprehensive Pro Task Management Spreadsheet available here that will also allow you to visualise tasks on a Kan-Ban style board and automatically link a Google Document to a task to allow you to add comments, images, links and checklists. This will give each task its own canvas to document everything you need.
The Pro Task management sheet creates documents automatically when a task is added and links the document to each title with zero effort. All task-related documents are saved conveniently in a dedicated folder in your Google Drive.
Tasks are created using a neat sidebar and added to whichever sheet you decide based on the status and also displayed on the Kanban board to visualise all tasks at a glance.
If you choose to go with the free option, then I would still recommend creating a Google Document that you can use to log all these extras since it’s great to write down what’s important and log specific links and images or add checklists to break tasks down into smaller chunks.
You could manually add a hyperlink to the document from the task Title to get quick access to it. When the task is moved to another sheet with the free option, this linked document will move with it.
Free Basic Task Management Sheet
You can get the free task management spreadsheet here, which has everything set up and ready to go.
If you’d like to have a go at building this yourself from scratch, here is the Apps Script for the Free Task Management Spreadsheet. You’ll notice you can adjust the sheetNames variable, but if you do, you’ll also need to adjust the targetSheetName variable for the switch/case statement.
/**
* Move function to move the task to a status sheet based on the status selected on a specific task
*/
function onEdit(e) {
var editedCell = e.range;
var editedSheet = editedCell.getSheet();
var sheetNames = ['Backlog', 'Waiting', 'To Do', 'In Progress', 'Completed', 'Archived']; // Adjust Sheet names or add more
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!editedCell || !editedSheet || sheetNames.indexOf(editedSheet.getName()) === -1) {
return;
}
var rowIndex = editedCell.getRow();
var status = editedCell.getValue();
var targetSheetName; // Add or adjust Status name in each switch/case and sheet name for each targetSheetName
switch (status) {
case 'Backlog':
targetSheetName = 'Backlog';
break;
case 'Waiting':
targetSheetName = 'Waiting';
break;
case 'To Do':
targetSheetName = 'To Do';
break;
case 'In Progress':
targetSheetName = 'In Progress';
break;
case 'Completed':
targetSheetName = 'Completed';
break;
case 'Archived':
targetSheetName = 'Archived';
break;
default:
return;
}
var targetSheet = spreadsheet.getSheetByName(targetSheetName);
var sourceSheet = editedSheet;
if (sourceSheet.getName() !== targetSheetName) {
var sourceRow = sourceSheet.getRange(rowIndex, 1, 1, sourceSheet.getLastColumn());
var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
sourceRow.copyTo(targetRange);
sourceSheet.deleteRow(rowIndex);
sourceSheet.insertRowAfter(rowIndex);
sortSheet(targetSheet);
sortSheet(sourceSheet);
spreadsheet.toast("Success: Task moved to " + targetSheetName + " Sheet");
}
}
/**
* Helper function to sort the targetSheet and SourceSheet after the row has been moved from the onEdit(e) function
* Sorting is based on column C from highest to lowest (1 = High; 5 = Low)
*/
function sortSheet(sheet) {
var sortRange = sheet.getRange(5, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
sortRange.sort([{ column: 3, descending: true }]);
}
If you're creating this from scratch yourself, you should create the six status tabs mentioned earlier, along with a Settings tab. You’ll need to ensure the Tab (Sheet) names are the same as detailed in this Apps Script file.
Changing these variables will also require changing the tab names in the sheet and the Status and labels within the settings tab (image below) for the dropdown menus since this Apps Script will look for these sheetNames and targetSheetName variables.
If you’re unfamiliar with Google Apps Script, I advise you to leave these as they are. After all, these are tried and tested status names. The Labels aren’t so detrimental; you can change these or add more if required.
The dropdown menus for the Status and Labels are collected from this Settings tab for each of the six status sheets. Check out this tutorial for how to Create a Dropdown list with Smart Chips in Google Sheets
Each status sheet also displays a SPARKLINE bar chart to show the progress of tasks. Check out this tutorial for how to create Sparkline Bar Charts in Google Sheets
The Implementation: Moving Tasks Between Sheets
Let's dive into how the code works to automate the tracking and movement of tasks based on their status.
The function `onEdit(e)` is an event trigger function that runs automatically when any cell in the spreadsheet is edited.
`var editedCell = e.range;` retrieves the range of the edited cell.
`var editedSheet = editedCell.getSheet();` gets the sheet where the edited cell is located.
`var sheetNames = ['Backlog', 'Waiting', 'To Do', 'In Progress', 'Completed', 'Archived'];` defines an array of sheet names representing different task statuses.
`var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();` retrieves the active spreadsheet.
The next block of code checks if the edited cell exists, if the edited sheet exists, and if the edited sheet name is one of the predefined sheet names. If any of these conditions is not met, the function returns and does nothing.
`var rowIndex = editedCell.getRow();` gets the row index of the edited cell.
`var status = editedCell.getValue();` retrieves the value of the edited cell, which represents the selected status.
The `switch` statement matches the selected status with predefined cases and assigns the corresponding sheet name to the `targetSheetName` variable.
`var targetSheet = spreadsheet.getSheetByName(targetSheetName);` retrieves the sheet object based on the `targetSheetName`.
`var sourceSheet = editedSheet;` assigns the `editedSheet` to the `sourceSheet` variable.
The next block of code checks if the `sourceSheet` is different from the `targetSheet`. If they are different, it proceeds to move the task to the target sheet.
`var sourceRow = sourceSheet.getRange(rowIndex, 1, 1, sourceSheet.getLastColumn());` retrieves the range of the source row to be copied.
`var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);` specifies the target range where the source row will be copied.
`sourceRow.copyTo(targetRange);` copies the source row to the target range.
`sourceSheet.deleteRow(rowIndex);` deletes the source row from the source sheet.
`sourceSheet.insertRowAfter(rowIndex);` inserts a new row after the deleted row in the source sheet.
`sortSheet(targetSheet);` calls the `sortSheet` function to sort the target sheet.
`sortSheet(sourceSheet);` calls the `sortSheet` function to sort the source sheet.
`spreadsheet.toast("Success: Task moved to " + targetSheetName + " Sheet");` displays a toast message indicating the success of moving the task to the target sheet.
The `sortSheet(sheet)` function is a helper function that sorts a given sheet based on column C in descending order.
That's how the code works in a nutshell. It captures the edited cell, determines the target sheet based on the status value, and moves the task row between sheets.
By customising the script to match your sheet names and status options, you can easily adapt it to your specific task management setup. In addition, the automation provided by Google Apps Script eliminates the need for manual updates, ensuring that your task tracking remains accurate and efficient.
By defining the sheet names and their corresponding statuses in the script, it’s possible to customise the solution to fit your specific task management needs. This flexibility allows for seamless integration into existing workflows.
Benefits and Implications: Streamlined Task Management
Automating task status tracking in Google Sheets offers several benefits. First, it saves time and reduces the chance of human error associated with manual data movement. The streamlined workflow ensures tasks are always in the appropriate sheet, reflecting their current status. Finally, the automated solution can be shared with team members, enabling real-time collaborative task management.
Conclusion
Efficient task management is crucial for maintaining productivity. By utilising Google Apps Script in Google Sheets, we can automate the tracking and movement of tasks based on their status. This powerful solution streamlines task management, saves time, and improves accuracy, allowing teams or individuals to stay organised and focused on their goals.
Comments