Synchronisation des lignes MySQL vers Google Sheets

Name: Synchronisation des lignes MySQL vers Google Sheets
Rating: 4.5 (10 reviews)
Author: Ahmed Saadawi

Intermédiaire

Ceci est unEngineeringworkflow d'automatisation du domainecontenant 9 nœuds.Utilise principalement des nœuds comme If, MySql, GoogleSheets, ScheduleTrigger. Synchronisation automatique de MySQL vers Google Sheets avec prévention des doublons

Prérequis

•Informations de connexion à la base de données MySQL
•Informations d'identification Google Sheets API

Nœuds utilisés (9)

Catégorie

Ingénierie

Aperçu du workflow

Visualisation des connexions entre les nœuds, avec support du zoom et du déplacement

Sélectionner des lignes d'une table

Ajouter une ligne dans la feuille

Mettre à jour des lignes dans une table

Déclencheur planifié toutes les n minutes

Aucune opération, ne rien faire

Vérifier si un nouvel enregistrement est retourné

React Flow

Exporter le workflow

Copiez la configuration JSON suivante dans n8n pour importer et utiliser ce workflow

{
  "id": "TmP9EhlzHlU4r1wS",
  "meta": {
    "instanceId": "ea2b41532677d34614d95d13d7d2ccb694657be482144baf71e7140df3b2a853",
    "templateCredsSetupCompleted": true
  },
  "name": "Sync MySQL Rows to Google Sheet",
  "tags": [],
  "nodes": [
    {
      "id": "a78b4dc6-b4c6-4ed8-8b9f-dad0a6897307",
      "name": "Sélectionner des lignes d'une table",
      "type": "n8n-nodes-base.mySql",
      "position": [
        -288,
        -112
      ],
      "parameters": {
        "table": {
          "value": "fifa25_customers"
        },
        "where": {
          "values": [
            {
              "value": "=0",
              "column": "sync"
            }
          ]
        },
        "options": {},
        "operation": "select"
      },
      "credentials": {
        "mySql": {
          "id": "HiSdj9EQPLInFlnQ",
          "name": "MySQL-SaadeddinPastry"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "b1a1e46f-4d7f-4068-a697-e35735af7718",
      "name": "Ajouter une ligne dans la feuille",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        352,
        -304
      ],
      "parameters": {
        "columns": {
          "value": {
            "id": "={{ $json.id }}",
            "name": "={{ $json.name }}",
            "email": "={{ $json.email }}",
            "phone": "={{ $json.phone }}",
            "gender": "={{ $json.gender }}",
            "region": "={{ $json.region }}",
            "datatime": "={{ $json.datatime }}",
            "birthdate": "={{ $json.birthdate }}"
          },
          "schema": [
            {
              "id": "id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "id",
              "defaultMatch": true,
              "canBeUsedToMatch": true
            },
            {
              "id": "name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "phone",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "phone",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "birthdate",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "birthdate",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "email",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "region",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "region",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "gender",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "gender",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "datatime",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "datatime",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "id"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "value": "gid=0"
        },
        "documentId": {
          "value": "1b86B_7Hcusp7ehDNjJZtCa8Rlmljf2av7Hs-gaSluoc"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "KJ3WD4ZJZo6YMqm3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "e7f77e58-ddfc-4086-8016-6ebdd9d01d44",
      "name": "Mettre à jour des lignes dans une table",
      "type": "n8n-nodes-base.mySql",
      "position": [
        352,
        -128
      ],
      "parameters": {
        "table": {
          "value": "fifa25_customers"
        },
        "options": {},
        "dataMode": "defineBelow",
        "operation": "update",
        "valuesToSend": {
          "values": [
            {
              "value": "1",
              "column": "sync"
            }
          ]
        },
        "valueToMatchOn": "={{ $json.id }}",
        "columnToMatchOn": "id"
      },
      "credentials": {
        "mySql": {
          "id": "HiSdj9EQPLInFlnQ",
          "name": "MySQL-SaadeddinPastry"
        }
      },
      "typeVersion": 2.4
    },
    {
      "id": "0aba12f9-2627-4be1-bd53-1505d332f352",
      "name": "Note adhésive",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -336,
        48
      ],
      "parameters": {
        "color": 3,
        "width": 208,
        "height": 80,
        "content": "Get 50 records from mysql table from not synced data"
      },
      "typeVersion": 1
    },
    {
      "id": "2ad57e58-7f42-4ef3-8d3a-228fc8ab422b",
      "name": "Déclencheur planifié toutes les n minutes",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -528,
        -112
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "minutes",
              "minutesInterval": 15
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "b52eebd6-1177-414e-8062-1204ac643dc9",
      "name": "Note adhésive1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        304,
        16
      ],
      "parameters": {
        "color": 3,
        "width": 208,
        "height": 80,
        "content": "Update all the got data with column sync = 1 to prevent duplication in next run"
      },
      "typeVersion": 1
    },
    {
      "id": "13a3b1e5-32b2-436f-9961-d924c9d052ef",
      "name": "Aucune opération, ne rien faire",
      "type": "n8n-nodes-base.noOp",
      "position": [
        352,
        160
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "f82d841b-fbda-4eed-80e5-15fa83a6cc00",
      "name": "Vérifier si un nouvel enregistrement est retourné",
      "type": "n8n-nodes-base.if",
      "position": [
        -80,
        -112
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "fdc56e38-29be-42a1-baa8-3c4e2380b2c1",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $jmespath($input.all(), '[].json').length }}",
              "rightValue": 0
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "d35b54c0-1bde-43d8-ad95-e79c9a5b3957",
      "name": "Note adhésive2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        176,
        -352
      ],
      "parameters": {
        "width": 400,
        "height": 656,
        "content": "## Output"
      },
      "typeVersion": 1
    }
  ],
  "active": true,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "75f1c88c-35cb-4762-ba78-e19a2ca167bd",
  "connections": {
    "a78b4dc6-b4c6-4ed8-8b9f-dad0a6897307": {
      "main": [
        [
          {
            "node": "f82d841b-fbda-4eed-80e5-15fa83a6cc00",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f82d841b-fbda-4eed-80e5-15fa83a6cc00": {
      "main": [
        [
          {
            "node": "b1a1e46f-4d7f-4068-a697-e35735af7718",
            "type": "main",
            "index": 0
          },
          {
            "node": "e7f77e58-ddfc-4086-8016-6ebdd9d01d44",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "13a3b1e5-32b2-436f-9961-d924c9d052ef",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2ad57e58-7f42-4ef3-8d3a-228fc8ab422b": {
      "main": [
        [
          {
            "node": "a78b4dc6-b4c6-4ed8-8b9f-dad0a6897307",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Foire aux questions

Comment utiliser ce workflow ?

Copiez le code de configuration JSON ci-dessus, créez un nouveau workflow dans votre instance n8n et sélectionnez "Importer depuis le JSON", collez la configuration et modifiez les paramètres d'authentification selon vos besoins.

Dans quelles scénarios ce workflow est-il adapté ?

Intermédiaire - Ingénierie

Est-ce payant ?

Ce workflow est entièrement gratuit et peut être utilisé directement. Veuillez noter que les services tiers utilisés dans le workflow (comme l'API OpenAI) peuvent nécessiter un paiement de votre part.