📝 How-To: Dynamically Filter a Google Sheets Dropdown Based on Status and Type (Using Apps Script)

This guide walks you through dynamically populating a dropdown list in Google Sheets, filtering values based on:

• Task status (e.g. not "Completed")
• Task type (e.g. only "Task" not "Sub-Task")

We use Google Apps Script to automatically populate a hidden column with valid dropdown values and keep it up to date via an onEdit trigger.

---

✅ Requirements

• A sheet named TODO with the following columns:
  • A: Task Name
  • B: Task Type (Task or Sub-Task)
  • J: Status (Completed, etc.)
• A target sheet (e.g., CMS) where the dropdown will live
• A helper column (e.g., TODO!Z2:Z) to store filtered values

---

🪄 Step-by-Step Instructions

1. Add the Script

Go to Extensions → Apps Script, then paste the script below.

2. Understand What It Does

• It runs every time the TODO sheet is edited
• It filters only "Task" rows where the status is not "Completed"
• It outputs those results to TODO!Z2:Z
• You can use that column as your dropdown source

---

💻 Google Apps Script Code

javascriptCopyEditfunction onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() === "TODO") {
    populateParentTaskList();
  }
}

function populateParentTaskList() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const todoSheet = ss.getSheetByName("TODO");

  const tasks = todoSheet.getRange("A2:A1000").getValues().flat();      // Task Names
  const types = todoSheet.getRange("B2:B1000").getValues().flat();      // Task or Sub-Task
  const statuses = todoSheet.getRange("J2:J1000").getValues().flat();   // Status

  // Filter only: Type == "Task" AND Status != "Completed"
  const activeParentTasks = tasks.filter((task, i) =>
    task && types[i] === "Task" && statuses[i] !== "Completed"
  );

  // Output to column Z
  todoSheet.getRange("Z2:Z").clearContent();
  todoSheet.getRange(2, 26, activeParentTasks.length, 1)
           .setValues(activeParentTasks.map(task => [task]));
}

---

🧪 Final Setup

In your dropdown cell(s):

1. Go to Data → Data Validation
2. Select: List from a range
3. Enter: TODO!Z2:Z
4. (Optional) Check “Show dropdown list in cell”
5. Click Done

(Optional)

• Hide column Z if you don’t want it visible to users
• Apply the validation to a range like CMS!D2:D1000 for full-column dropdowns

---

🎯 Result

You’re now dynamically generating a dropdown that updates itself anytime the task list changes — no more manual filtering, and no more “Completed” sub-tasks cluttering your options.