求助：Google Sheets营销任务分成员筛选脚本开发

Hey there! I totally get how tricky this can feel when you're starting out with Sheets automation—let's break this down into simple, actionable steps that don't require advanced coding skills. Here's exactly how to set up your task tracking system:

Start by setting up a clear master tab (name it something like Master List) with all your team's tasks. I recommend these columns to keep things organized:

• Task ID (unique identifier for each task)
• Task Description (details of what needs to be done)
• Assignee (the exact name of the team member—make sure this matches their tab name later!)
• Due Date
• Status (e.g., Not Started, In Progress, Done)

Fill this out with all your existing tasks, and double-check that the Assignee column uses consistent spelling/capitalization for each team member.

Make 4 new tabs in your Sheet, naming each one exactly after a team member (e.g., Sarah, Mike, Luna, Raj). This exact match is key for the auto-filtering to work later.

This is the easiest way to get each member's tab to automatically show only their tasks, and it updates whenever you add/change tasks in the master list.

In the A1 cell of each member's tab, paste this formula (adjust the range and column letter to match your master list):

=QUERY('Master List'!A:E, "SELECT * WHERE C = '"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))&"'", 1)

Let me break this down so you understand what's happening:

• 'Master List'!A:E: This refers to all columns A to E in your master tab—change this if your tasks span more columns.
• WHERE C = '...': The C is the column letter where your Assignee names live (if Assignee is in column D, use D instead).
• The RIGHT(CELL(...)) part automatically grabs the name of the current tab, so you don't have to manually type each member's name into the formula. Super handy if you ever rename tabs!
• The final 1 tells Sheets to include the header row from your master list.

• If the formula pulls in the header correctly, you can format it to stand out (bold, fill color) so members can easily scan their tasks.
• Add conditional formatting (e.g., highlight overdue tasks in red) to make the tab more useful—just select the Due Date column, go to Format > Conditional formatting, and set a rule like "Date is before today".

• Always keep the Assignee column and tab names identical (no extra spaces, same capitalization—Sheets is case-sensitive here!).
• If you don't want team members editing the master list, adjust sharing settings to give them "View only" access to the master tab, but "Edit" access to their own tabs (since the formula is read-only, they can't mess up the master data).

内容的提问来源于stack exchange，提问作者Adam Berthiaume