Automating Task Management with Google Apps Script

Efficient task management is crucial for both personal productivity and team collaboration. As tasks are completed, managing and archiving them effectively can help maintain a clear and focused workflow. In this blog post, we will explore how to automate the process of moving completed tasks from an active task list to a completed archive using Google Apps Script in Google Sheets.

Overview of the Script

The archiveCompletedTasks function is designed to automate the process of archiving completed tasks in a Google Sheet. This script checks tasks in a “Tasks” sheet and moves any completed tasks (where a checkbox is checked) to a “Completed” sheet. This not only helps in keeping the task list updated but also preserves a record of completed activities.

function archiveCompletedTasks() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tasksSheet = ss.getSheetByName("Tasks");
  const completedSheet = ss.getSheetByName("Completed");
  if (!tasksSheet || !completedSheet) {
    console.error("One of the sheets does not exist. Check the sheet names.");
    return; // Exit the function if any sheet is not found
  }
  // Get all tasks, starting from the second row to skip the header
  const tasks = tasksSheet.getRange('A2:B' + tasksSheet.getLastRow()).getValues();
  let rowsToDelete = [];
  // Iterate through the tasks and collect the indices of completed tasks
  for (let i = 0; i < tasks.length; i++) {
    if (tasks[i][1] === true) { // Check if the checkbox is checked
      completedSheet.appendRow(tasks[i]);
      rowsToDelete.push(i + 2); // Add 2 to adjust for header row and 1-based indexing
    }
  }
  // Delete rows from the Tasks sheet, starting from the last to avoid index shifting
  // Reversing the array to start deletion from the bottom of the sheet
  rowsToDelete.reverse().forEach((rowIndex) => {
    tasksSheet.deleteRow(rowIndex);
  });
}

Script Breakdown

• Get Active Spreadsheet: The script begins by retrieving the active spreadsheet using SpreadsheetApp.getActiveSpreadsheet().
• Identify Sheets: It identifies two specific sheets within the spreadsheet — “Tasks” for ongoing tasks and “Completed” for archived tasks.
• Fetch Tasks: The tasks are fetched from the “Tasks” sheet, starting from the second row (to skip the header) and includes two columns: Task Description and Completion Status (a checkbox).
• Process Tasks: Each task is checked to see if it’s completed (checkbox is true). If completed, the task is appended to the “Completed” sheet and then removed from the “Tasks” sheet.
• Adjust for Indexing: When deleting rows, the script adjusts for the header row and the shift in indexing as rows are removed.

Implementation Steps

1. Setup Your Sheets: Create a Google Sheet with two tabs: “Tasks” and “Completed”. The “Tasks” tab should have at least two columns: one for the task description and another for a checkbox indicating completion.
2. Open Script Editor: From your Google Sheet, go to Extensions > Apps Script to open the script editor.
3. Paste and Save the Script: Paste the provided script into the script editor and save.
4. Run or Trigger the Script: You can run the script directly from the editor or set up a time-driven trigger to run it automatically at regular intervals.

Example Data Structure

To help visualize how this script works, here’s an example of the data structure in the “Tasks” and “Completed” sheets:

Tasks Sheet:

Task Description	Completed
Submit monthly report	FALSE
Update project plan	TRUE
Organize team meeting	FALSE

Completed Sheet (after running the script):

Task Description	Completed
Update project plan	TRUE

Conclusion

Automating the movement of completed tasks using Google Apps Script is a simple yet powerful way to enhance task management. By implementing this script, you can ensure that your task sheets are always up-to-date and that records of completed tasks are preserved efficiently.

This approach not only saves time but also reduces the chances of manual errors, making it an invaluable addition to any task management system.