清理和标准化 CSV 上传以导入 Google Sheets 和 Drive

• •HTTP Webhook 端点（n8n 会自动生成）
• •Google Drive API 凭证
• •Google Sheets API 凭证

{
  "meta": {
    "instanceId": "2000c64071c20843606b95c63795bb0797c41036047055a6586498e855b96efc"
  },
  "nodes": [
    {
      "id": "24e5fd68-0441-4541-b543-fcaef4f8ec6c",
      "name": "设置说明",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -496,
        -112
      ],
      "parameters": {
        "width": 760,
        "height": 696,
        "content": "🧹 **需要设置：**"
      },
      "typeVersion": 1
    },
    {
      "id": "b452e138-2c91-4934-8be6-e3c190407db3",
      "name": "CSV 上传 Webhook",
      "type": "n8n-nodes-base.webhook",
      "position": [
        -160,
        160
      ],
      "webhookId": "csv-upload-webhook",
      "parameters": {
        "path": "csv-upload",
        "options": {
          "noResponseBody": false
        },
        "httpMethod": "POST"
      },
      "typeVersion": 1
    },
    {
      "id": "e6a91fe0-7d79-4e45-900b-5200b6af7cbf",
      "name": "提取 CSV 内容",
      "type": "n8n-nodes-base.code",
      "position": [
        48,
        160
      ],
      "parameters": {
        "jsCode": "// Extract and validate CSV upload\nconst requestData = $input.first();\nlet csvContent = '';\nlet filename = 'uploaded_file.csv';\n\nif (requestData.binary && requestData.binary.data) {\n  csvContent = requestData.binary.data.toString();\n  filename = requestData.binary.data.filename || filename;\n} else if (requestData.json.csv_content) {\n  csvContent = requestData.json.csv_content;\n  filename = requestData.json.filename || filename;\n} else if (requestData.json.file_base64) {\n  csvContent = Buffer.from(requestData.json.file_base64, 'base64').toString();\n  filename = requestData.json.filename || filename;\n} else {\n  throw new Error('No CSV content found in request');\n}\n\nif (!csvContent || csvContent.length < 10) {\n  throw new Error('Invalid or empty CSV file');\n}\n\nconst initialRows = csvContent.split('\\n').length - 1;\n\nreturn {\n  json: {\n    filename,\n    original_content: csvContent,\n    file_size_bytes: csvContent.length,\n    initial_row_count: initialRows,\n    upload_timestamp: new Date().toISOString(),\n    processing_started: true\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "57875349-1fa5-454b-aa02-1255a1c87fb4",
      "name": "解析 CSV 数据",
      "type": "n8n-nodes-base.code",
      "position": [
        240,
        160
      ],
      "parameters": {
        "jsCode": "// Parse and clean CSV data\nconst uploadInfo = $input.first().json;\nconst csvContent = uploadInfo.original_content;\n\nconst lines = csvContent.split('\\n').map(line => line.trim()).filter(line => line.length > 0);\nif (lines.length === 0) throw new Error('No valid rows found in CSV');\n\nconst headers = lines[0].split(',').map(header => header.replace(/\"/g, '').trim().toLowerCase().replace(/\\s+/g, '_'));\n\nconst rawRows = [];\nfor (let i = 1; i < lines.length; i++) {\n  const values = lines[i].split(',').map(val => val.replace(/\"/g, '').trim());\n  if (values.length === headers.length && values.some(val => val.length > 0)) {\n    const row = {};\n    headers.forEach((header, index) => row[header] = values[index] || '');\n    rawRows.push(row);\n  }\n}\n\nreturn {\n  json: {\n    ...uploadInfo,\n    headers,\n    raw_rows: rawRows,\n    parsed_row_count: rawRows.length\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "275eb9f3-40bc-4c2e-bd8e-7dd707f9dc21",
      "name": "清理和标准化数据",
      "type": "n8n-nodes-base.code",
      "position": [
        448,
        160
      ],
      "parameters": {
        "jsCode": "// Clean CSV rows\nconst data = $input.first().json;\nlet cleanedRows = [...data.raw_rows];\n\nconst cleanEmail = e => e ? (e.toLowerCase().trim().match(/^[^\\s@]+@[^\\s@]+\\.[^\\s@]+$/) ? e : '') : '';\nconst cleanPhone = p => p ? p.replace(/\\D/g, '') : '';\nconst cleanName = n => n ? n.trim().split(' ').map(w => w.charAt(0).toUpperCase() + w.slice(1).toLowerCase()).join(' ') : '';\nconst cleanText = t => t ? t.trim().replace(/\\s+/g, ' ') : '';\n\ncleanedRows = cleanedRows.map(row => {\n  const cleaned = {};\n  Object.keys(row).forEach(key => {\n    let value = row[key];\n    if (key.includes('email')) cleaned[key] = cleanEmail(value);\n    else if (key.includes('phone')) cleaned[key] = cleanPhone(value);\n    else if (key.includes('name')) cleaned[key] = cleanName(value);\n    else cleaned[key] = cleanText(value);\n  });\n  cleaned._data_quality_score = Math.round((Object.values(cleaned).filter(v => v).length / Object.keys(cleaned).length) * 100);\n  return cleaned;\n});\n\nconst highQualityRows = cleanedRows.filter(r => r._data_quality_score >= 30);\n\nreturn {\n  json: {\n    ...data,\n    cleaned_rows: highQualityRows,\n    cleaning_summary: {\n      original_count: data.parsed_row_count,\n      after_cleaning: highQualityRows.length,\n      average_quality_score: Math.round(highQualityRows.reduce((s, r) => s + r._data_quality_score, 0) / highQualityRows.length)\n    },\n    processing_completed: new Date().toISOString()\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "c5d3faa0-0c48-4782-b436-2c51c39ec39a",
      "name": "生成干净 CSV",
      "type": "n8n-nodes-base.code",
      "position": [
        640,
        160
      ],
      "parameters": {
        "jsCode": "// Generate cleaned CSV\nconst data = $input.first().json;\nconst headers = data.headers;\nconst cleanedRows = data.cleaned_rows;\n\nlet csvContent = headers.join(',') + '\\n';\ncsvContent += cleanedRows.map(r => headers.map(h => r[h] || '').join(',')).join('\\n');\n\nconst cleanedFilename = `cleaned_${Date.now()}_${data.filename}`;\n\nreturn {\n  json: { ...data, cleaned_csv_content: csvContent, cleaned_filename: cleanedFilename },\n  binary: {\n    data: Buffer.from(csvContent, 'utf8'),\n    fileName: cleanedFilename,\n    mimeType: 'text/csv'\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "56a06147-0e57-44aa-8c24-20e284486bbe",
      "name": "保存到 Google Drive",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        848,
        80
      ],
      "parameters": {
        "name": "={{ $json.cleaned_filename }}",
        "driveId": {
          "__rl": true,
          "mode": "list",
          "value": "My Drive"
        },
        "options": {},
        "folderId": {
          "__rl": true,
          "mode": "list",
          "value": "root",
          "cachedResultName": "/ (Root folder)"
        }
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "IPz4dCJVFC8uaoHw",
          "name": "Google Drive account 2"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "3b1b4bd8-3c65-4ed9-b87c-f6a5885d1712",
      "name": "清空现有表格",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        848,
        240
      ],
      "parameters": {
        "operation": "clear",
        "sheetName": "Sheet1",
        "documentId": "YOUR_GOOGLE_SHEET_ID"
      },
      "typeVersion": 4
    },
    {
      "id": "a856710d-317a-4d97-92f0-2946c91511ec",
      "name": "准备表格数据",
      "type": "n8n-nodes-base.code",
      "position": [
        1040,
        240
      ],
      "parameters": {
        "jsCode": "// Prepare data for Google Sheets\nconst data = $input.first().json;\nreturn data.cleaned_rows.map(r => ({ json: r }));"
      },
      "typeVersion": 2
    },
    {
      "id": "d1e73fbe-6d7c-47df-8e74-02d4b3ce7fa8",
      "name": "导入到 Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1248,
        240
      ],
      "parameters": {
        "options": {},
        "operation": "append",
        "sheetName": "Sheet1",
        "documentId": "YOUR_GOOGLE_SHEET_ID"
      },
      "typeVersion": 4
    }
  ],
  "pinData": {},
  "connections": {
    "Parse CSV Data": {
      "main": [
        [
          {
            "node": "Clean & Standardize Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "CSV Upload Webhook": {
      "main": [
        [
          {
            "node": "Extract CSV Content",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate Clean CSV": {
      "main": [
        [
          {
            "node": "Save to Google Drive",
            "type": "main",
            "index": 0
          },
          {
            "node": "Clear Existing Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare for Sheets": {
      "main": [
        [
          {
            "node": "Import to Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract CSV Content": {
      "main": [
        [
          {
            "node": "Parse CSV Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Clear Existing Sheet": {
      "main": [
        [
          {
            "node": "Prepare for Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Clean & Standardize Data": {
      "main": [
        [
          {
            "node": "Generate Clean CSV",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}