Scripted Success Master

✅ Automate Task Completion in Google Sheets Using Apps Script

Welcome to Scripted Success Master! In this step-by-step guide, you’ll learn how to:

• Create a task management system in Google Sheets
• Automatically move completed tasks to a separate sheet
• Track completion date and calculate TAT (Turnaround Time)
• Use Google Apps Script to power it all

🧱 Step 1: Create the Google Sheet Structure

Sheet 1: Task List

Rename the first sheet to Task List and create the following columns:

ℹ️ Use Data Validation (Dropdown) in the Status column for values like: Not yet Started, WIP, Completed

Sheet 2: Completed Tasks

Create a new sheet and rename it to Completed Tasks. Add these headers:

Column	Header	Note
A	Sr No	From Task List
B	Task Description	From Task List
C	Start Date	From Task List
D	Priority	From Task List
E	Status	Should be "Completed"
F	Completion Date	Auto-filled by script
G	TAT	Days between Start & Completion

🧠 Step 2: Add the Script

Watch the full tutorial on YouTube

Open your Google Sheet, then go to Extensions → Apps Script and paste the following code:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const editedRow = e.range.getRow();
  const editedColumn = e.range.getColumn();

  const taskSheetName = 'Task List';
  const completedSheetName = 'Completed Tasks';
  const statusColumn = 5; // Column E

  if (sheet.getName() !== taskSheetName || editedColumn !== statusColumn) return;

  const status = e.range.getValue();
  if (status !== 'Completed') return;

  const taskRow = sheet.getRange(editedRow, 1, 1, sheet.getLastColumn()).getValues()[0];
  const startDate = new Date(taskRow[2]);
  const completionDate = new Date();
  const TAT = Math.ceil((completionDate - startDate) / (1000 * 60 * 60 * 24));

  const completedRow = [...taskRow, completionDate, TAT];

  let completedSheet = e.source.getSheetByName(completedSheetName);
  if (!completedSheet) {
    completedSheet = e.source.insertSheet(completedSheetName);
    completedSheet.appendRow(['Sr No', 'Task Description', 'Start Date', 'Priority', 'Status', 'Completion Date', 'TAT']);
  }

  completedSheet.appendRow(completedRow);
  sheet.deleteRow(editedRow);
}
  

🔍 How It Works

• The script listens for edits in the Task List sheet
• If the Status is changed to Completed, it copies the row to Completed Tasks
• It automatically inserts today’s date and calculates the TAT
• Then, it deletes the task from the main list

🎯 Tip: Freeze the first row in both sheets for better readability.

✨ Customize Further

• Send an email when a task is completed
• Auto-number Sr No column
• Add filters for each column

📦 Final Thoughts

This script transforms a basic spreadsheet into an automated workflow system. Perfect for individuals and small teams, it saves time and prevents errors. Stay tuned for more practical automation on Scripted Success Master!