Système automatisé de relations externes hospitalières : envoi d'e-mails personnalisés via Google Sheets et Gmail

• •Compte Google et informations d'identification Gmail API
• •Informations d'identification Google Sheets API

{
  "meta": {
    "instanceId": "5d8ded8ed85f32cb4dbffdc338bf9f00b9b1ab189dbbed41f77a5c21e99276af",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "96056900-7870-4d4e-91a6-2f64177c7109",
      "name": "MINDANAO",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -144,
        240
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1968569642,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ/edit#gid=1968569642",
          "cachedResultName": "Mindanao"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ/edit?usp=drivesdk",
          "cachedResultName": "PH HOSPITALs"
        }
      },
      "credentials": {},
      "typeVersion": 4.7
    },
    {
      "id": "e78171fa-2463-478c-a9f4-03eebbda963d",
      "name": "VISAYAS FILES",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -144,
        48
      ],
      "parameters": {
        "options": {},
        "filtersUI": {
          "values": [
            {
              "lookupValue": "={{ $json.hospital }}",
              "lookupColumn": "Hospital Name"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 822141156,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ/edit#gid=822141156",
          "cachedResultName": "Visayas"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ/edit?usp=drivesdk",
          "cachedResultName": "PH HOSPITALs"
        }
      },
      "credentials": {},
      "typeVersion": 4.7
    },
    {
      "id": "d9705318-8318-46f9-bff3-2bdb2f0ca121",
      "name": "LUZON FILES",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -144,
        -144
      ],
      "parameters": {
        "options": {},
        "filtersUI": {
          "values": [
            {
              "lookupValue": "={{ $json.hospital }}",
              "lookupColumn": "Hospital Name"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 678847961,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ/edit#gid=678847961",
          "cachedResultName": "Luzon"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1dNEKLvwSBgUvMJd1ekxz-R5_RN3ewlJTCOIHW4-bhIQ/edit?usp=drivesdk",
          "cachedResultName": "PH HOSPITALs"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "b19960c1-5e4b-4a81-adc3-be75854ef747",
      "name": "Lorsqu'un message de chat est reçu",
      "type": "@n8n/n8n-nodes-langchain.chatTrigger",
      "position": [
        -1040,
        216
      ],
      "webhookId": "7c234d39-d75e-4791-94cc-b5aa633df171",
      "parameters": {
        "options": {}
      },
      "typeVersion": 1.3
    },
    {
      "id": "3821c377-95eb-42ca-8d97-e6c4d7379016",
      "name": "Hospital Parser",
      "type": "n8n-nodes-base.code",
      "position": [
        -816,
        216
      ],
      "parameters": {
        "jsCode": "const rawText = $input.first().json.chatInput;\nconst lines = rawText.split('\\n').filter(line => line.trim() !== '');\nconst region = lines[0] || 'Unknown';\nconst hospitals = lines.slice(1);\n\n// Return one item per hospital\nreturn hospitals.map(h => ({\n  json: {\n    region: region,\n    hospital: h\n  }\n}));\n"
      },
      "typeVersion": 2
    },
    {
      "id": "f13b30e3-2df2-4dc9-86b3-e0bd7012ecc9",
      "name": "Batch Sender",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -592,
        216
      ],
      "parameters": {
        "options": {
          "reset": false
        }
      },
      "typeVersion": 3
    },
    {
      "id": "d3c1de1b-d9e8-467a-ab7a-e8f2478b6c9a",
      "name": "Region Switcher",
      "type": "n8n-nodes-base.switch",
      "position": [
        -368,
        32
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": false,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "89b82ead-f3b3-49dc-bdd8-f4099fdb59cf",
                    "operator": {
                      "type": "string",
                      "operation": "contains"
                    },
                    "leftValue": "={{ $json.region }}",
                    "rightValue": "=LUZON"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": false,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "3930e266-a3c8-4c92-82f2-93d4cf6e840c",
                    "operator": {
                      "type": "string",
                      "operation": "contains"
                    },
                    "leftValue": "={{ $json.region }}",
                    "rightValue": "=VISAYAS"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": false,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "8048a6d6-54c1-4fe9-80e6-c56cd5392c53",
                    "operator": {
                      "type": "string",
                      "operation": "contains"
                    },
                    "leftValue": "={{ $json.region }}",
                    "rightValue": "=MINDANAO"
                  }
                ]
              }
            }
          ]
        },
        "options": {
          "ignoreCase": true,
          "allMatchingOutputs": false
        }
      },
      "typeVersion": 3.2
    },
    {
      "id": "2119f413-e8cb-40a6-af1f-fddfd0b56700",
      "name": "Send Gmail Message",
      "type": "n8n-nodes-base.gmail",
      "position": [
        80,
        136
      ],
      "webhookId": "9b7e7cd3-4959-4e16-be0a-f51fb53929be",
      "parameters": {
        "sendTo": "={{ $json['Main Email'] }}",
        "message": "=<p>Dear {{ $json[\"Hospital Name\"] }},</p>\n\n<p>My name is {{ $json[\"Your Name\"] }}, a {{ $json[\"Your Credentials\"] }}.</p>\n\n<p>During a recent hospital admission, I personally experienced the challenges of paper-based real-time billing—waiting for updated estimates, relying on manual printouts, and facing delays when clarity was needed most. This inspired me to build a working demo system tailored for hospitals like {{ $json[\"Sample Hospitals\"] }}.</p>\n\n<p><b>Here’s a quick overview of what I built:</b></p>\n\n<p>📹 Video 1: {{ $json[\"Video Link 1\"] }}</p>\n<p>📹 Video 2: {{ $json[\"Video Link 2\"] }}</p>\n<p>📹 Video 3: {{ $json[\"Video Link 3\"] }}</p>\n\n<p>✅ Patients can receive secure login credentials by email and check their real-time running bill anytime.<br>\n✅ The hospital can save costs by reducing paper use while improving transparency.<br>\n✅ Admin staff can manage patients and services from a centralized panel.</p>\n\n<p>Would you be available this week for a quick discussion?</p>\n\n<p>--<br>\nBest regards,<br>\n{{ $json[\"Your Name\"] }}<br>\n{{ $json[\"Your Contact\"] }}</p>\n",
        "options": {},
        "subject": "=Letter of Intent: {{ $json['Hospital Name'] }}"
      },
      "typeVersion": 2.1
    },
    {
      "id": "e93525d1-ab66-47f7-ab98-77594a58b622",
      "name": "Note adhésive",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1416,
        -368
      ],
      "parameters": {
        "width": 1728,
        "height": 768,
        "content": "### **For the MAIN YELLOW Sticky Note (Place at the top of your workflow):**\n**Hospital Outreach Automation**\nThis workflow automates personalized email outreach to hospitals. \nSend a chat message where the first line is the region (LUZON, VISAYAS, MINDANAO) and each following line is a hospital name. \nThe workflow will look up each hospital's email from a Google Sheet and send a personalized message via Gmail."
      },
      "typeVersion": 1
    },
    {
      "id": "6545ccf7-b4a2-4f59-829a-d637479776ff",
      "name": "Note adhésive1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1392,
        -176
      ],
      "parameters": {
        "color": 4,
        "width": 464,
        "height": 304,
        "content": "**SETUP REQUIRED:**\n1.  **Set up credentials** for Google Sheets and Gmail.\n2.  **Replace the Google Sheet ID** in the 'Set Configuration' node with your own. Use our template structure.\n3.  **Customize the email template** in the 'Send Gmail Message' node to match your message.\n\n**How to use:**\n1.  Activate the workflow.\n2.  Send a chat input in the following format:\n    `LUZON`\n    `St. Luke's Medical Center`\n    `Makati Medical Center`\n    `Philippine General Hospital`\n\n---"
      },
      "typeVersion": 1
    },
    {
      "id": "8652c245-c942-436a-b037-051efecba80a",
      "name": "Note adhésive2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -880,
        -176
      ],
      "parameters": {
        "color": 4,
        "width": 352,
        "height": 80,
        "content": "Youtube Tutorial: https://www.youtube.com/embed/5u9W-Iegq6k"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "96056900-7870-4d4e-91a6-2f64177c7109": {
      "main": [
        [
          {
            "node": "2119f413-e8cb-40a6-af1f-fddfd0b56700",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d9705318-8318-46f9-bff3-2bdb2f0ca121": {
      "main": [
        [
          {
            "node": "2119f413-e8cb-40a6-af1f-fddfd0b56700",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f13b30e3-2df2-4dc9-86b3-e0bd7012ecc9": {
      "main": [
        [],
        [
          {
            "node": "d3c1de1b-d9e8-467a-ab7a-e8f2478b6c9a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e78171fa-2463-478c-a9f4-03eebbda963d": {
      "main": [
        [
          {
            "node": "2119f413-e8cb-40a6-af1f-fddfd0b56700",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3821c377-95eb-42ca-8d97-e6c4d7379016": {
      "main": [
        [
          {
            "node": "f13b30e3-2df2-4dc9-86b3-e0bd7012ecc9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d3c1de1b-d9e8-467a-ab7a-e8f2478b6c9a": {
      "main": [
        [
          {
            "node": "d9705318-8318-46f9-bff3-2bdb2f0ca121",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "e78171fa-2463-478c-a9f4-03eebbda963d",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "96056900-7870-4d4e-91a6-2f64177c7109",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2119f413-e8cb-40a6-af1f-fddfd0b56700": {
      "main": [
        [
          {
            "node": "f13b30e3-2df2-4dc9-86b3-e0bd7012ecc9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b19960c1-5e4b-4a81-adc3-be75854ef747": {
      "main": [
        [
          {
            "node": "3821c377-95eb-42ca-8d97-e6c4d7379016",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}