Campagne automatisée de marketing par e-froid avec modèles aléatoires et suivi via Google Sheets

• •Informations d'identification Google Sheets API

{
  "meta": {
    "instanceId": "db31bea69a53eada00777682bd2f0392fe4ec4364a135252e7113d3d2d2c1eb4",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "1606155a-a552-43b6-9f21-c2dd1d450b5a",
      "name": "Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -336,
        240
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 436315764,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0/edit#gid=436315764",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0/edit?usp=drivesdk",
          "cachedResultName": "Leadsss"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "sPZTi75OujS3qDy2",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "3f8db0ae-4627-496b-bebb-bb3984c65994",
      "name": "If",
      "type": "n8n-nodes-base.if",
      "position": [
        -112,
        240
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "fdc40194-662b-402b-b731-6405ce91259e",
              "operator": {
                "type": "string",
                "operation": "notEquals"
              },
              "leftValue": "={{ $json[\"Send Status\"] }}",
              "rightValue": "SENT"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "e1f635be-60c4-4512-bcb3-497f2323fa26",
      "name": "Loop Over Items",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        112,
        224
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "d6540ff3-3e6a-46bd-96a0-bede2040ac0c",
      "name": "Google Sheets1",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        336,
        48
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1056380010,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0/edit#gid=1056380010",
          "cachedResultName": "Sheet2"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0/edit?usp=drivesdk",
          "cachedResultName": "Leadsss"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "sPZTi75OujS3qDy2",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "34c31c92-aa2f-41f0-9147-059dbb1e70e5",
      "name": "Code",
      "type": "n8n-nodes-base.code",
      "position": [
        560,
        48
      ],
      "parameters": {
        "jsCode": "// Get templates from previous node\nconst templates = items.map(item => item.json);\n\n// Check if templates exist\nif (!templates || templates.length === 0) {\n  throw new Error('No templates found');\n}\n\n// Pick a random template\nconst index = Math.floor(Math.random() * templates.length);\nconst template = templates[index];\n\n// Convert body to HTML\nconst bodyHtml = template.Body.replace(/\\n/g, '<br>');\n\n// Return in n8n-compatible format\nreturn [\n  {\n    json: {\n      subject: template.Subject,\n      body: bodyHtml\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "a30f39bf-bac7-4d22-87be-6dad2d1c84d8",
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "position": [
        784,
        128
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.1
    },
    {
      "id": "7b4e39b8-66f2-4417-80db-d18111a62cde",
      "name": "Edit Fields",
      "type": "n8n-nodes-base.set",
      "position": [
        992,
        128
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "f6a6ba02-422b-4e7c-a73e-03901cc2db0c",
              "name": "subject",
              "type": "string",
              "value": "={{ $json.subject.replace(\"[Name]\", $json.Name || \"there\") }}\n"
            },
            {
              "id": "1b0ed009-ead8-491a-bb47-1fb79e12ab1c",
              "name": "body",
              "type": "string",
              "value": "={{ $json.body.replace(\"[Name]\", $json.Name || \"there\") }}"
            }
          ]
        },
        "includeOtherFields": "={{ true }}"
      },
      "typeVersion": 3.4
    },
    {
      "id": "04d53785-08f3-45cd-a4ed-ff947c700347",
      "name": "Merge1",
      "type": "n8n-nodes-base.merge",
      "position": [
        1440,
        64
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "combineBy": "combineByPosition"
      },
      "typeVersion": 3.1
    },
    {
      "id": "56c14e56-5840-4303-9c26-444a4a1a8915",
      "name": "Wait",
      "type": "n8n-nodes-base.wait",
      "position": [
        1440,
        320
      ],
      "webhookId": "59d0e464-9eef-4147-9d07-e283193ed822",
      "parameters": {},
      "typeVersion": 1.1
    },
    {
      "id": "2071518d-7fa2-4113-bb49-4f5b24d1a1f8",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -368,
        128
      ],
      "parameters": {
        "width": 380,
        "height": 320,
        "content": "   ## Get Leads Data From Google Sheet"
      },
      "typeVersion": 1
    },
    {
      "id": "97ecc536-451c-4c81-a045-5eb84d4561c3",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        272,
        -80
      ],
      "parameters": {
        "width": 420,
        "height": 300,
        "content": "## Get Email Templates From Google Sheet\n"
      },
      "typeVersion": 1
    },
    {
      "id": "4c935042-6c50-4e0d-987c-7ceadf4bf44d",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1584,
        -16
      ],
      "parameters": {
        "width": 300,
        "height": 280,
        "content": "## Log The Status"
      },
      "typeVersion": 1
    },
    {
      "id": "a55b3460-d3f9-48f4-aec2-634ad83144fa",
      "name": "When clicking ‘Test workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -544,
        240
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "0164e396-5f37-4257-851c-c5909769ae7e",
      "name": "Google Sheets6",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1728,
        64
      ],
      "parameters": {
        "columns": {
          "value": {
            "Time": "={{ new Date().toLocaleTimeString(\"en-GB\", { timeZone: \"Africa/casablanca\", hour12: false }) }}",
            "Email": "={{ $json.Email }}",
            "Send Status": "={{ $json.labelIds[0] }}"
          },
          "schema": [
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Send Status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Send Status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Email"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 436315764,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0/edit#gid=436315764",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1EP82H05QzVy4JPHVvz06pO09p-V7M8_6-6O1jRAigN0/edit?usp=drivesdk",
          "cachedResultName": "Leadsss"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "sPZTi75OujS3qDy2",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "19fe6e1b-828f-4ef8-b172-26b17467d1ce",
      "name": "Send email",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        1184,
        208
      ],
      "webhookId": "5639edcb-93af-4a72-afd8-df5eb8a7e8db",
      "parameters": {
        "html": "={{ $json.body }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "={{ $json.subject }}",
        "toEmail": "={{ $json.Email }}",
        "fromEmail": "anir@agramprojects.com"
      },
      "credentials": {
        "smtp": {
          "id": "XWbszNJxA8doYVEu",
          "name": "SMTP account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "d00d7894-2f25-4ec1-92d4-424838da86c7",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -416,
        -320
      ],
      "parameters": {
        "color": 5,
        "width": 480,
        "height": 240,
        "content": "## ⚙️ SETUP CHECKLIST\n1. Create Leads sheet: Name | Email | Send Status | Time\n2. Create Templates sheet: Subject | Body (use [Name])\n3. Connect Google Sheets OAuth\n4. Connect SMTP credentials\n5. Update all Google Sheets node IDs\n6. Set \"From Email\" in Send email node\n7. Set Wait time (30-120 seconds recommended)\n8. Test with 2-3 leads first!\n"
      },
      "typeVersion": 1
    },
    {
      "id": "e056f235-7ca6-4afb-bbd7-eeed07124bd9",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -208,
        640
      ],
      "parameters": {
        "color": 3,
        "width": 544,
        "height": 288,
        "content": "## Common First-Time Issues\n\n❌ No emails sending → Check SMTP credentials and \"From Email\" address\n❌ Duplicate sends → Verify IF node is filtering Send Status correctly\n❌ [Name] not replaced → Check lead sheet has \"Name\" column spelled exactly\n❌ Sheet not updating → Confirm \"Google Sheets6\" node points to correct sheet\n❌ Emails going to spam → Add delays (Wait node), use verified domain, warm up email account\n❌ Loop running forever → Check \"Loop Over Items\" batch size (default: 1 is correct)"
      },
      "typeVersion": 1
    },
    {
      "id": "ef4cb653-45cf-454e-9b57-0122da54cdf0",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        752,
        640
      ],
      "parameters": {
        "color": 4,
        "width": 512,
        "height": 224,
        "content": "## What to Test Before Running Campaign\n✅ Send 2-3 test emails to yourself\n✅ Verify personalization works ([Name] replaced correctly)\n✅ Check emails don't land in spam folder\n✅ Confirm Sheet updates with \"SENT\" status after send\n✅ Test that already-sent leads are skipped on second run\n✅ Verify email formatting (line breaks, HTML if used)\n✅ Test with empty Name field (should use \"there\" fallback)"
      },
      "typeVersion": 1
    },
    {
      "id": "845ec9dd-da56-4d32-a2aa-846974e11654",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1552,
        -320
      ],
      "parameters": {
        "color": 7,
        "width": 848,
        "height": 1376,
        "content": "# Step-by-Step Setup\n\n## 1. Create Leads Google Sheet\n\n- Create a new Google Sheet named \"Leads\"\n- Add these column headers in row 1:\nName | Email | Send Status | Time\n- Fill in your leads (leave Send Status and Time empty)\n- Copy the sheet ID from the URL\n\n## 2. Create Templates Google Sheet\n\n- Create another sheet named \"Templates\" (can be in same file, different tab)\n- Add these column headers:\nSubject | Body\n- Write 2-5 email templates using [Name] placeholder\n\nExample Subject: Quick question about [Name]'s design workflow\n\nExample Body: Hi [Name],\\n\\nI noticed your work in graphic design and wanted to reach out...\n\n- Copy the sheet ID\n\n## 3. Connect n8n Credentials\n\n- Add Google Sheets OAuth credentials (sign in with Google)\n- Add SMTP credentials for your email account\n- Gmail: enable \"App Password\" in Google settings\n- Custom domain: get SMTP details from hosting provider\n\n## 4. Update Workflow Nodes\n\n- In \"Google Sheets\" node (Leads):\n- Select your Leads sheet\n- Select the \"Sheet1\" tab (or your leads tab name)\n- In \"Google Sheets1\" node (Templates):\n- Select your Templates sheet\n- Select the templates tab\n- In \"Send email\" node: Set \"From Email\" to your sending address\n- Verify SMTP credentials are selected\n- In \"Google Sheets6\" node (Log Status): Already points to Leads sheet—just verify it's selected\n\n## 5. Configure Wait Time\n\n- In \"Wait\" node: default is instant—change to 30-60 seconds\n- This prevents spam flags and respects rate limits\n- For SendGrid/Mailgun: 10-30 seconds is safe\n\n## 6. Test It\n\n- Add 2 test leads with YOUR email addresses\n- Click \"Test workflow\" button\n- Check: emails received, personalization correct, Sheet updated to \"SENT\"\n- Verify no duplicate sends if you run again"
      },
      "typeVersion": 1
    },
    {
      "id": "e9e9a45d-56e7-429a-a4ac-65dea168f180",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2064,
        -192
      ],
      "parameters": {
        "color": 2,
        "width": 768,
        "height": 656,
        "content": "# 🚫 GMAIL SMTP DOES NOT WORK!\n\nGmail rejects SMTP authentication for automated sending.\nEven with App Passwords, it will fail.\n\n## ✅ WORKING OPTIONS:\n• SendGrid (100 emails/day free)\n• Mailgun (free tier)\n• Brevo (300 emails/day free)\n• Amazon SES (cheap, pay-as-you-go)\n• Custom domain SMTP (cPanel/Plesk hosting)\n• Resend.com\n• Postmark\n\n## ❌ WON'T WORK:\n• Gmail SMTP ❌\n• Yahoo SMTP ❌\n• Outlook/Hotmail SMTP ❌\n\n## 🎯 RECOMMENDED:\nStart with SendGrid free tier\nor use your web hosting SMTP\n\n## ⚙️ You MUST use a transactional email service!\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "3f8db0ae-4627-496b-bebb-bb3984c65994": {
      "main": [
        [
          {
            "node": "e1f635be-60c4-4512-bcb3-497f2323fa26",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "34c31c92-aa2f-41f0-9147-059dbb1e70e5": {
      "main": [
        [
          {
            "node": "a30f39bf-bac7-4d22-87be-6dad2d1c84d8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "56c14e56-5840-4303-9c26-444a4a1a8915": {
      "main": [
        [
          {
            "node": "e1f635be-60c4-4512-bcb3-497f2323fa26",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a30f39bf-bac7-4d22-87be-6dad2d1c84d8": {
      "main": [
        [
          {
            "node": "7b4e39b8-66f2-4417-80db-d18111a62cde",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "04d53785-08f3-45cd-a4ed-ff947c700347": {
      "main": [
        [
          {
            "node": "0164e396-5f37-4257-851c-c5909769ae7e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "19fe6e1b-828f-4ef8-b172-26b17467d1ce": {
      "main": [
        [
          {
            "node": "56c14e56-5840-4303-9c26-444a4a1a8915",
            "type": "main",
            "index": 0
          },
          {
            "node": "04d53785-08f3-45cd-a4ed-ff947c700347",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "7b4e39b8-66f2-4417-80db-d18111a62cde": {
      "main": [
        [
          {
            "node": "04d53785-08f3-45cd-a4ed-ff947c700347",
            "type": "main",
            "index": 0
          },
          {
            "node": "19fe6e1b-828f-4ef8-b172-26b17467d1ce",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1606155a-a552-43b6-9f21-c2dd1d450b5a": {
      "main": [
        [
          {
            "node": "3f8db0ae-4627-496b-bebb-bb3984c65994",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d6540ff3-3e6a-46bd-96a0-bede2040ac0c": {
      "main": [
        [
          {
            "node": "34c31c92-aa2f-41f0-9147-059dbb1e70e5",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e1f635be-60c4-4512-bcb3-497f2323fa26": {
      "main": [
        [],
        [
          {
            "node": "d6540ff3-3e6a-46bd-96a0-bede2040ac0c",
            "type": "main",
            "index": 0
          },
          {
            "node": "a30f39bf-bac7-4d22-87be-6dad2d1c84d8",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "a55b3460-d3f9-48f4-aec2-634ad83144fa": {
      "main": [
        [
          {
            "node": "1606155a-a552-43b6-9f21-c2dd1d450b5a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}