实时ClickUp时间跟踪到HubSpot项目同步

• •可能需要目标 API 的认证凭证

{
  "meta": {
    "instanceId": "a8c64a6cf7dcfa4f9b9bae070111f579dc10d8d5f08bd580db0503f385c3ed87",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "05fe5ea5-681b-4be2-bfcb-95b75280339b",
      "name": "ClickUp：获取任务详情",
      "type": "n8n-nodes-base.clickUp",
      "position": [
        2000,
        1400
      ],
      "parameters": {
        "id": "={{ $json.task_id }}",
        "operation": "get",
        "authentication": "oAuth2"
      },
      "credentials": {
        "clickUpOAuth2Api": {
          "id": "vyn2tbfGHLDeANiL",
          "name": "ClickUp account 2"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "c35c2491-51cf-45c2-bec3-45a214ac4ada",
      "name": "ClickUp：获取时间条目详情",
      "type": "n8n-nodes-base.clickUp",
      "position": [
        2000,
        1600
      ],
      "parameters": {
        "team": "9015730766",
        "resource": "timeEntry",
        "operation": "get",
        "timeEntry": "={{ $json.data.interval_id }}",
        "authentication": "oAuth2"
      },
      "credentials": {
        "clickUpOAuth2Api": {
          "id": "vyn2tbfGHLDeANiL",
          "name": "ClickUp account 2"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "0e67ca2b-8071-48d4-9a01-8b063e3fe98c",
      "name": "合并：任务和时间数据",
      "type": "n8n-nodes-base.merge",
      "position": [
        2320,
        1500
      ],
      "parameters": {
        "mode": "combineBySql"
      },
      "typeVersion": 3.1
    },
    {
      "id": "ee0a97cb-d935-4f33-849f-2b3d44a849fe",
      "name": "代码：提取冲刺和任务数据",
      "type": "n8n-nodes-base.code",
      "position": [
        2740,
        1500
      ],
      "parameters": {
        "language": "python",
        "pythonCode": "from datetime import datetime, timedelta\nfrom collections import defaultdict\nimport re\n\n# --- Helper Functions ---\n\ndef parse_date_safely(date_str):\n    \"\"\"\n    Safely parses a date string (expected in '%Y-%m-%d' or Unix millisecond timestamp)\n    into a datetime object. Returns None if invalid.\n    \"\"\"\n    if date_str is None or str(date_str).strip() == '' or str(date_str).strip() == '0':\n        return None\n    \n    date_str_clean = str(date_str).strip()\n\n    # Try parsing as Unix millisecond timestamp first (like date_created, date_updated, due_date, start_date)\n    if date_str_clean.isdigit() and len(date_str_clean) >= 10: # Minimum 10 digits for seconds, >=13 for milliseconds\n        try:\n            # ClickUp dates like due_date are often milliseconds\n            # If it's a 10-digit number, it might be seconds. Check length heuristic.\n            if len(date_str_clean) == 10: # Likely seconds\n                return datetime.fromtimestamp(int(date_str_clean))\n            elif len(date_str_clean) >= 13: # Likely milliseconds or more precise\n                return datetime.fromtimestamp(int(date_str_clean) / 1000)\n        except (ValueError, TypeError):\n            pass # Fallback to trying YYYY-MM-DD\n            \n    # Try parsing as YYYY-MM-DD\n    try:\n        return datetime.strptime(date_str_clean, '%Y-%m-%d')\n    except ValueError:\n        return None\n    \n    return None # If all attempts fail\n\n\ndef get_sprint_name(custom_fields):\n    \"\"\"\n    Extracts the sprint name from the 'custom_fields' list of a task.\n    Returns default strings if the field is not found or value doesn't match.\n    \"\"\"\n    for field in custom_fields:\n        if field.get('name') == 'Sprint' and field.get('type') == 'drop_down':\n            selected_value = field.get('value')\n            options = field.get('type_config', {}).get('options', [])\n            \n            # Note: selected_value for dropdowns in ClickUp API is typically the orderindex (integer)\n            for option in options:\n                if option.get('orderindex') == selected_value:\n                    return option.get('name', 'Unknown Sprint')\n            return \"Sprint Value Not Found\"\n    return \"No Sprint Field\"\n\ndef get_hubspot_deal_id(custom_fields):\n    \"\"\"\n    Extracts the HubSpot Deal ID from the 'custom_fields' list of a task.\n    Returns None if the field is not found.\n    \"\"\"\n    for field in custom_fields:\n        if field.get('name') == 'HubSpot Deal ID' and field.get('type') == 'short_text':\n            return field.get('value', None)\n    return None\n\ndef clean_sprint_name_for_key(sprint_name):\n    \"\"\"\n    Cleans a sprint name to be suitable for use as a dictionary key.\n    Converts to lowercase, replaces non-alphanumeric with underscores.\n    \"\"\"\n    sprint_name = sprint_name.lower()\n    sprint_name = re.sub(r'[^a-z0-9]+', '_', sprint_name)\n    sprint_name = sprint_name.strip('_')\n    return sprint_name\n\n# --- Global Constants/Mappings ---\nGRACE_PERIOD_DAYS = 7 # Define grace period for 'AT RISK' status\nSPRINT_KEY_MAPPING = {\n    \"Sprint 1 Initial Set up and Discovery\": \"sprint_1\",\n    \"Sprint 2 Data and advanced set up\": \"sprint_2\",\n    \"Sprint 3 Sales/Marketing/Service set up\": \"sprint_3\",\n    \"Sprint 4 Training and QA\": \"sprint_4\",\n    \"Additional Requests\": \"additional_requests\",\n    \"Goodstack requests\": \"goodstack_requests\",\n    \"No Sprint Field\": \"no_sprint_field\", # Consistent key for tasks without a sprint field\n    \"Sprint Value Not Found\": \"sprint_value_not_found\" # Consistent key for unmatched sprint values\n}\n\n# --- Main Processing Logic for a Single Task Input ---\n\n# Assuming 'items' contains exactly ONE task, as per the incremental solution requirement.\nif not items or not items[0].get('json'):\n    # Return an empty or default object if no task data is provided\n    return [{ \"json\": {\n        \"error\": \"No task data provided or input is empty for incremental update.\"\n    }}]\n\ntask = items[0]['json'] # Get the single task object\n\n# Initialize variables for the output\nproject_name_overall = \"N/A\"\nhubspot_deal_id = None\nclickup_list_id = None\nfinal_project_start_date = None\nfinal_project_end_date = None\ntotal_time_remaining = 0.0\noverall_project_performance_status = \"UNDEFINED\"\ntarget_actual_sprint_field_name = None # Initialized new field\nnumeric_sprint_id = None # Initialized new field\ntask_name_output = \"N/A\" # New variable for the task name in output\n\n# --- Extract direct task properties ---\ntry:\n    # time_spent and time_estimate are in milliseconds in the raw input\n    actual_hours_for_this_task = float(task.get('time_spent', '0')) / (1000 * 60 * 60)\n    scoped_hours_for_this_task = float(task.get('time_estimate', '0')) / (1000 * 60 * 60)\n    \n    # Task name extraction\n    task_name_output = task.get('name', 'N/A') # Extract the task name here\n\n    # Project name can be found from task['project']['name']\n    project_name_overall = task.get('project', {}).get('name', 'N/A').strip()\n    \n    # Get IDs from the single task\n    hubspot_deal_id = get_hubspot_deal_id(task.get('custom_fields', []))\n    if task.get('list'):\n        clickup_list_id = task['list'].get('id')\n\n    # Get start and end dates from this single task\n    task_start_date_obj = parse_date_safely(task.get('start_date'))\n    task_due_date_obj = parse_date_safely(task.get('due_date'))\n    \n    final_project_start_date = task_start_date_obj.strftime('%Y-%m-%d') if task_start_date_obj else None\n    final_project_end_date = task_due_date_obj.strftime('%Y-%m-%d') if task_due_date_obj else None\n\n    # Calculate time remaining for this single task\n    total_time_remaining = round(scoped_hours_for_this_task - actual_hours_for_this_task, 2)\n\n    # --- Determine status for this single task ---\n    current_date_obj = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)\n    date_closed_obj = parse_date_safely(task.get('date_closed'))\n    \n    if date_closed_obj:\n        if task_due_date_obj and date_closed_obj <= task_due_date_obj:\n            overall_project_performance_status = \"CLOSED ON TIME\"\n        else:\n            overall_project_performance_status = \"CLOSED LATE\"\n    elif current_date_obj and task_due_date_obj:\n        if current_date_obj <= task_due_date_obj:\n            overall_project_performance_status = \"OPEN - ON TRACK\"\n        else:\n            days_overdue = (current_date_obj - task_due_date_obj).days\n            if 1 <= days_overdue <= GRACE_PERIOD_DAYS:\n                overall_project_performance_status = \"OPEN - AT RISK\"\n            else:\n                overall_project_performance_status = \"OPEN - OFF TRACK\"\n    else: # Task has no due date and is not closed\n        overall_project_performance_status = \"STATUS UNKNOWN (Missing Due Date)\"\n\n    # --- Determine Target Actual Sprint Field Name (Generalized) ---\n    task_sprint_name = get_sprint_name(task.get('custom_fields', []))\n    cleaned_sprint_key = SPRINT_KEY_MAPPING.get(task_sprint_name, clean_sprint_name_for_key(task_sprint_name))\n    \n    # Construct the field name for ANY cleaned sprint key\n    target_actual_sprint_field_name = f\"actual_{cleaned_sprint_key}_hours\"\n\n    # --- Determine Numeric Sprint ID (1-4 or 5 for others) ---\n    if cleaned_sprint_key == \"sprint_1\":\n        numeric_sprint_id = \"1\"\n    elif cleaned_sprint_key == \"sprint_2\":\n        numeric_sprint_id = \"2\"\n    elif cleaned_sprint_key == \"sprint_3\":\n        numeric_sprint_id = \"3\"\n    elif cleaned_sprint_key == \"sprint_4\":\n        numeric_sprint_id = \"4\"\n    else:\n        numeric_sprint_id = \"5\" # For any other sprint/workstream category\n\nexcept Exception as e:\n    # If a critical error occurs during single task processing, log it and return partial data\n    print(f\"Critical Error processing single task '{task.get('name', 'Unknown')}' (ID: {task.get('id', 'N/A')}): {e}\")\n    # Populate with default/error values if something goes wrong\n    actual_hours_for_this_task = 0.0\n    scoped_hours_for_this_task = 0.0\n    total_time_remaining = 0.0\n    overall_project_performance_status = \"ERROR_PROCESSING\"\n    # Ensure task_name_output is also set to a default in case of error\n    task_name_output = task.get('name', 'ERROR_TASK_NAME')\n\n\n# --- Prepare Sprint-Specific Hours (only for the task's assigned sprint) ---\nactual_hours_by_sprint = defaultdict(float)\nscoped_hours_by_sprint = defaultdict(float)\n\n# Ensure cleaned_sprint_key is available even after an exception\nif 'cleaned_sprint_key' not in locals(): # Check if it was defined in the try block\n    # If an exception occurred before cleaned_sprint_key was set, define a default\n    task_sprint_name_for_fallback = get_sprint_name(task.get('custom_fields', [])) # Get sprint name for fallback\n    cleaned_sprint_key = SPRINT_KEY_MAPPING.get(task_sprint_name_for_fallback, clean_sprint_name_for_key(task_sprint_name_for_fallback))\n    target_actual_sprint_field_name = f\"actual_{cleaned_sprint_key}_hours\" # Ensure this is also set for output\n    numeric_sprint_id = \"5\" # Default to 5 if sprint cannot be determined\n\nactual_hours_by_sprint[cleaned_sprint_key] = actual_hours_for_this_task\nscoped_hours_by_sprint[cleaned_sprint_key] = scoped_hours_for_this_task\n\n\n# --- Construct the Final Output Dictionary ---\noutput_data = {\n    \"name\": task_name_output, # Added the task name here\n    \"project_name\": project_name_overall,\n    \"project_start_date\": final_project_start_date,\n    \"project_end_date\": final_project_end_date,\n    \"total_scoped_hours\": round(scoped_hours_for_this_task, 2), # This is now the TASK'S scoped hours\n    \"actual_hours_tracked\": round(actual_hours_for_this_task, 2), # This is now the TASK'S actual hours\n    \"total_time_remaining\": total_time_remaining,\n    \"project_status\": overall_project_performance_status, # This is now the TASK'S status\n    \"clickup_list_id\": clickup_list_id,\n    \"hs_object_id\": hubspot_deal_id,\n    \"hubspot_owner_id\": None, # Not present in provided raw task data\n    \"hubspot_team_id\": None, # Not present in provided raw task data\n    \"target_actual_sprint_field_name\": target_actual_sprint_field_name, # Field name for the current task's sprint\n    \"numeric_sprint_id\": numeric_sprint_id,\n}\n\n# Add sprint-specific hours. Only the relevant sprint will be non-zero.\n# Initialize all specified sprint keys to 0.0 first to ensure presence\n# This ensures a consistent set of keys in the output, even if their value is 0.\nall_sprint_keys_to_include = list(set(SPRINT_KEY_MAPPING.values()) | set(actual_hours_by_sprint.keys()))\n\nfor key_alias in sorted(all_sprint_keys_to_include): # Sort for consistent output order\n    output_data[f\"sprint_{key_alias}_scoped_hours\"] = round(scoped_hours_by_sprint[key_alias], 2)\n    output_data[f\"actual_{key_alias}_hours\"] = round(actual_hours_by_sprint[key_alias], 2)\n\n\n# --- Return in n8n-compatible format ---\nreturn [{ \"json\": output_data }]"
      },
      "typeVersion": 2
    },
    {
      "id": "9951232a-969c-46c6-b314-a505e086ccdc",
      "name": "时间跟踪更新触发器",
      "type": "n8n-nodes-base.clickUpTrigger",
      "position": [
        1700,
        1460
      ],
      "webhookId": "68824f3c-6b7a-4369-96bd-78e19bfeda4e",
      "parameters": {
        "team": "9015730766",
        "events": [
          "taskTimeTrackedUpdated"
        ],
        "filters": {
          "spaceId": "90153635535"
        },
        "authentication": "oAuth2"
      },
      "credentials": {
        "clickUpOAuth2Api": {
          "id": "vyn2tbfGHLDeANiL",
          "name": "ClickUp account 2"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "f37f07d8-0cc2-4855-912a-6bb63ed766a5",
      "name": "在项目文件夹上",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        2980,
        1500
      ],
      "parameters": {
        "url": "https://api.hubapi.com/crm/v3/objects/objectTypeId/search",
        "method": "POST",
        "options": {},
        "jsonBody": "={\n  \"properties\": [\n    \"project_name\",\n    \"hubspot_company_id\",\n    \"project_start_date\",\n    \"project_end_date\",\n    \"total_scoped_hours\",\n    \"actual_hours_tracked\",\n    \"sprint_1_scoped_hours\",\n    \"actual_sprint_1_hours\",\n    \"sprint_2_scoped_hours\",\n    \"actual_sprint_2_hours\",\n    \"sprint_3_scoped_hours\",\n    \"actual_sprint_3_hours\",\n    \"sprint_4_scoped_hours\",\n    \"actual_sprint_4_hours\",\n    \"hubspot_owner_id\",\n    \"hubspot_team_id\",\n    \"total_time_remaining\",\n    \"project_status\",\n    \"clickup_list_id\"\n  ],\n  \"filterGroups\": [\n    {\n      \"filters\": [\n        {\n          \"propertyName\": \"project_name\",\n          \"operator\": \"EQ\",\n          \"value\": \"{{ $json.project_name }}\"\n        }\n      ]\n    }\n  ],\n  \"sorts\": [\n    {\n      \"propertyName\": \"project_start_date\",\n      \"direction\": \"DESCENDING\"\n    }\n  ],\n  \"limit\": 10\n}",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth"
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "3BbcoRaYndonbb01",
          "name": "HS_Auth"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "8baccf3c-becd-48aa-99bf-8d6ec104903e",
      "name": "HubSpot：更新项目小时数（actual_additional_requests_hours）",
      "type": "n8n-nodes-base.httpRequest",
      "maxTries": 2,
      "position": [
        3560,
        1360
      ],
      "parameters": {
        "url": "=https://api.hubapi.com/crm/v3/objects/{objectTypeId}/{{ $json.results[0].id }}",
        "method": "PATCH",
        "options": {},
        "jsonBody": "={\n  \"properties\": {\n    \"actual_hours_tracked\": \"{{ (parseFloat($json.results[0].properties.actual_hours_tracked || 0) + parseFloat($('Code: Extract Sprint & Task Data').item.json.actualHoursTracked)).round()}}\",\n    \"total_time_remaining\": \"{{ (parseFloat($json.results[0].properties.total_time_remaining || 0) - parseFloat($('Code: Extract Sprint & Task Data').item.json.actualHoursTracked)).toFixed(1) }}\"\n  }\n}\n\n\n",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth"
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "3BbcoRaYndonbb01",
          "name": "HS_Auth"
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.2
    },
    {
      "id": "96e4f398-ad13-4a79-87d2-2e1174193db7",
      "name": "HubSpot：更新项目小时数 冲刺 1-4",
      "type": "n8n-nodes-base.httpRequest",
      "maxTries": 2,
      "position": [
        3560,
        1660
      ],
      "parameters": {
        "url": "=https://api.hubapi.com/crm/v3/objects/objectTypeId/{{ $json.results[0].id }}",
        "method": "PATCH",
        "options": {},
        "jsonBody": "={\n  \"properties\": {\n    \"{{ $('Code: Extract Sprint & Task Data').item.json.target_actual_sprint_field_name }}\": \"{{ $('Code: Extract Sprint & Task Data').item.json.actual_hours_tracked }}\",\n    \"actual_hours_tracked\": \"{{ $('OnProjectFolder').item.json.results[0].properties.actual_hours_tracked.toNumber()+$('Code: Extract Sprint & Task Data').item.json.actual_hours_tracked}}\"\n\"total_time_remaining\": \"{{ $json.results[0].properties.total_time_remaining - $('Code: Extract Sprint & Task Data').item.json.actual_hours_tracked}}\"\n}\n\n\n",
        "sendBody": true,
        "specifyBody": "json",
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth"
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "3BbcoRaYndonbb01",
          "name": "HS_Auth"
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.2
    },
    {
      "id": "986eb283-e122-4e8d-a090-2ad9f17da217",
      "name": "便签4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        820,
        820
      ],
      "parameters": {
        "width": 640,
        "height": 1500,
        "content": "### **实时 ClickUp 时间跟踪到 HubSpot 项目同步**"
      },
      "typeVersion": 1
    },
    {
      "id": "dc3c9c3a-fe2c-4abb-9565-6378f45aeb20",
      "name": "便签5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2540,
        1020
      ],
      "parameters": {
        "color": 7,
        "width": 660,
        "height": 320,
        "content": "### 说明框"
      },
      "typeVersion": 1
    },
    {
      "id": "7ed05d2f-311a-487c-b598-d8254085512a",
      "name": "按冲刺类型路由",
      "type": "n8n-nodes-base.if",
      "position": [
        3180,
        1500
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "loose"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "25b4da00-6ab5-415b-b102-41ad495a5f71",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $('Code: Extract Sprint & Task Data').item.json.numeric_sprint_id }}",
              "rightValue": 4
            }
          ]
        },
        "looseTypeValidation": true
      },
      "typeVersion": 2.2
    }
  ],
  "pinData": {},
  "connections": {
    "OnProjectFolder": {
      "main": [
        [
          {
            "node": "Route by Sprint Type",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Route by Sprint Type": {
      "main": [
        [
          {
            "node": "HubSpot: Update Project Hours (actual_additional_requests_hours)",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "HubSpot: Update Project Hours Sprint 1-4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge: Task & Time Data": {
      "main": [
        [
          {
            "node": "Code: Extract Sprint & Task Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ClickUp: Get Task Details": {
      "main": [
        [
          {
            "node": "Merge: Task & Time Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Time Tracked Update Trigger": {
      "main": [
        [
          {
            "node": "ClickUp: Get Task Details",
            "type": "main",
            "index": 0
          },
          {
            "node": "ClickUp: Get Time Entry Details",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ClickUp: Get Time Entry Details": {
      "main": [
        [
          {
            "node": "Merge: Task & Time Data",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Code: Extract Sprint & Task Data": {
      "main": [
        [
          {
            "node": "OnProjectFolder",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}