Sync Google Sheets Contacts to SeaTable with Update-Insert Logic

{
  "meta": {
    "instanceId": "e2b72466a589dd1250fc94a8e861457e040bf25b07f6b069958c036d3f2bfe77",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "9c61b9c6-8bfc-4a93-9a00-8c58211d19c0",
      "name": "When clicking ‘Execute workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -960,
        640
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "1211014d-547c-4bef-b164-3f89599e2356",
      "name": "Loop Over Items",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        -288,
        640
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "8cf9862f-edf3-459d-a953-3c9245cf97c0",
      "name": "Replace Me",
      "type": "n8n-nodes-base.noOp",
      "position": [
        -64,
        368
      ],
      "parameters": {},
      "executeOnce": true,
      "typeVersion": 1
    },
    {
      "id": "f52c74d9-8523-45e5-8456-88c8e34d0f65",
      "name": "If",
      "type": "n8n-nodes-base.if",
      "position": [
        160,
        560
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "581f9c5f-c61c-418f-bcad-c9b9e5e23f13",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.isEmpty() }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "7ed9293d-36ab-4010-9c17-e887d6fb4c76",
      "name": "seatablelookup",
      "type": "n8n-nodes-base.seaTable",
      "position": [
        -64,
        560
      ],
      "parameters": {
        "options": {
          "simple": false
        },
        "operation": "search",
        "tableName": "Table1",
        "searchTerm": "={{ $json.email }}",
        "searchColumn": "email"
      },
      "credentials": {
        "seaTableApi": {
          "id": "YBFwe7cXLQNGuChg",
          "name": "SeaTable account"
        }
      },
      "executeOnce": true,
      "typeVersion": 2,
      "alwaysOutputData": true
    },
    {
      "id": "3423e503-7818-40a9-a244-ff34e47803a2",
      "name": "Create a row",
      "type": "n8n-nodes-base.seaTable",
      "position": [
        384,
        640
      ],
      "parameters": {
        "columnsUi": {
          "columnValues": [
            {
              "columnName": "email",
              "columnValue": "={{ $('contacts').item.json.email }}"
            },
            {
              "columnName": "firstname",
              "columnValue": "={{ $('contacts').item.json.firstname }}"
            },
            {
              "columnName": "lastname",
              "columnValue": "={{ $('contacts').item.json.lastname }}"
            },
            {
              "columnName": "company",
              "columnValue": "={{ $('contacts').item.json.company }}"
            }
          ]
        },
        "tableName": "Table1"
      },
      "credentials": {
        "seaTableApi": {
          "id": "YBFwe7cXLQNGuChg",
          "name": "SeaTable account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "c0db351e-4c38-4f30-a532-71102834c3f4",
      "name": "Update a row",
      "type": "n8n-nodes-base.seaTable",
      "position": [
        384,
        832
      ],
      "parameters": {
        "rowId": "={{ $json._id }}",
        "columnsUi": {
          "columnValues": [
            {
              "columnName": "email",
              "columnValue": "={{ $('contacts').item.json.email }}"
            },
            {
              "columnName": "firstname",
              "columnValue": "={{ $('contacts').item.json.firstname }}"
            },
            {
              "columnName": "lastname",
              "columnValue": "={{ $('contacts').item.json.lastname }}"
            },
            {
              "columnName": "company",
              "columnValue": "={{ $('contacts').item.json.company }}"
            }
          ]
        },
        "operation": "update",
        "tableName": "Table1"
      },
      "credentials": {
        "seaTableApi": {
          "id": "YBFwe7cXLQNGuChg",
          "name": "SeaTable account"
        }
      },
      "typeVersion": 2
    },
    {
      "id": "6a21c5d9-a021-4c07-8f23-bbff08cdb5c1",
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -736,
        1056
      ],
      "parameters": {
        "url": "=https://{{ $json.server }}/api-gateway/api/v2/dtables/{{ $json.base_uuid }}/tables",
        "method": "POST",
        "options": {},
        "jsonBody": "{\n  \"table_name\": \"contact\",\n  \"columns\": [\n    {\n      \"column_name\": \"email\",\n      \"column_type\": \"text\"\n    },\n        {\n      \"column_name\": \"firstname\",\n      \"column_type\": \"text\"\n    },\n        {\n      \"column_name\": \"lastname\",\n      \"column_type\": \"text\"\n    },\n        {\n      \"column_name\": \"company\",\n      \"column_type\": \"text\"\n    }\n  ]\n}",
        "sendBody": true,
        "sendHeaders": true,
        "specifyBody": "json",
        "headerParameters": {
          "parameters": [
            {
              "name": "accept",
              "value": "application/json"
            },
            {
              "name": "authorization",
              "value": "=Bearer {{ $json.access_token }}"
            }
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "4d17f737-3b7e-46e4-9024-7648cc7ee4c7",
      "name": "contacts",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -512,
        640
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1877973285,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1VPUd7xbigvW4dIjyvsSRn3Y3jpITjyZneCIEU0HK_Hw/edit#gid=1877973285",
          "cachedResultName": "contacts"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "={{ $json.googlesheetid }}"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "0xG6VARJ6hnHx2T1",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.4
    },
    {
      "id": "16a0a6b7-f105-4c8b-9ec0-c1472fee0630",
      "name": "settings",
      "type": "n8n-nodes-base.set",
      "position": [
        -736,
        640
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "4b74909d-6a40-422f-9d5a-1d72f5577f3f",
              "name": "googlesheetid",
              "type": "string",
              "value": "<put your Google Sheet ID here>"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "8f0cbb8a-e570-4e8c-90c9-029326d69a5f",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -784,
        512
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 96,
        "content": "Set the Google Sheet ID you want to use"
      },
      "typeVersion": 1
    },
    {
      "id": "a59171ae-873a-4c1d-9be6-7bd4a15be1a6",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -560,
        512
      ],
      "parameters": {
        "color": 7,
        "width": 192,
        "height": 96,
        "content": "\n[Google Sheet Template](https://docs.google.com/spreadsheets/d/1mFKp3wmbV9qp2tpGGsN72zdiC32y8H1nhjdgP885y-U/edit?usp=sharing)\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2b104909-8608-4165-966b-7edf96b717e3",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        592,
        144
      ],
      "parameters": {
        "color": 7,
        "width": 608,
        "height": 848,
        "content": "# Keep your Google Sheets contacts in sync with SeaTable\nUpdate or Insert records in SeaTable\n\n## How it works\n\n- The Google Sheet is your list of contacts to be inserted or updated in SeaTable.\n- For each contact, we first check if the record exists in SeaTable\n- We then update or insert the contact in SeaTable\n\n## How to use\n1. Download the [Google Sheet Template](https://docs.google.com/spreadsheets/d/1mFKp3wmbV9qp2tpGGsN72zdiC32y8H1nhjdgP885y-U/edit?usp=sharing). Identify the ID of the document in your own environment.\n   - The ID is the `string` between d/ and /edit\n\n2. Setup the Google Sheet ID in the `settings` node\n\n3. Update \"Table1\" on SeaTable with fields email, firstname, lastname, company\n\n4. Configure your Google & SeaTable credentials\n\n5. Update your Google Sheet with your own contacts\n\n\n## Requirements\n- **Google credentials** to access your documents\n- **SeaTable** Cloud up and running.\n- **n8n version** this workflow was tested on 1.105.2 (Ubuntu)\n\n## Need Help?\n\nFeel free to comment this [post](https://www.linkedin.com/posts/n8n-about_n8n-seatable-airtable-activity-7363129613465571332-Scun/).\nContact me on [LinkedIn](https://www.linkedin.com/in/stephaneheckel/) or ask in the [Forum](https://community.n8n.io/)!\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "65ef3bb5-2721-4ca6-9860-45644d2fd8e0",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1040,
        864
      ],
      "parameters": {
        "color": 7,
        "width": 576,
        "content": "# Create Table via API \n\n### (use the Bearer below)\nhttps://api.seatable.com/reference/createtable\n\n### Get Base Token produce Bearer (access-token)\nhttps://api.seatable.com/reference/getbasetokenwithapitoken\n\n### Get your Base UUID\nhttps://account.seatable.com/bases\n\n### Create your API Token\nhttps://api.seatable.com/reference/createapitoken\n\n### Get your API token\nhttps://account.seatable.com/api\n\nhttps://cloud.seatable.io/workspace/85603/dtable/datanosco/?tid=0000&vid=0000"
      },
      "typeVersion": 1
    },
    {
      "id": "c74be35d-a5b6-48f9-89b0-3b13b198f813",
      "name": "Edit Fields",
      "type": "n8n-nodes-base.set",
      "position": [
        -960,
        1056
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "1812dcee-b5af-46ab-aab4-4065e1026eb9",
              "name": "server",
              "type": "string",
              "value": "cloud.seatable.io"
            },
            {
              "id": "ee799561-c443-4fa2-aa4b-2123871bc982",
              "name": "base_uuid",
              "type": "string",
              "value": "<your base_uuid>"
            },
            {
              "id": "d6e5604f-1cbb-485c-9017-d44343369594",
              "name": "access_token",
              "type": "string",
              "value": "<your access token>"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "7abce5a9-f386-4006-974f-76d2405bd97f",
      "name": "HTTP Request1",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -928,
        288
      ],
      "parameters": {
        "url": "https://cloud.seatable.io/server-info",
        "options": {},
        "sendHeaders": true,
        "headerParameters": {
          "parameters": [
            {
              "name": "accept",
              "value": "application/json"
            }
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "e437af5a-6ba3-4dac-802c-37296987b67c",
      "name": "Edit Fields2",
      "type": "n8n-nodes-base.set",
      "position": [
        -704,
        288
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3.4
    },
    {
      "id": "a2f3b7ca-c6df-4f4d-b701-6ca8b9029db2",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1056,
        176
      ],
      "parameters": {
        "color": 7,
        "width": 576,
        "height": 288,
        "content": "# Get Server Info\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "If": {
      "main": [
        [
          {
            "node": "Create a row",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "Update a row",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "contacts": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "settings": {
      "main": [
        [
          {
            "node": "contacts",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Replace Me": {
      "main": [
        []
      ]
    },
    "Edit Fields": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create a row": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request": {
      "main": [
        []
      ]
    },
    "Update a row": {
      "main": [
        [
          {
            "node": "Loop Over Items",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request1": {
      "main": [
        [
          {
            "node": "Edit Fields2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "seatablelookup": {
      "main": [
        [
          {
            "node": "If",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Loop Over Items": {
      "main": [
        [
          {
            "node": "Replace Me",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "seatablelookup",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When clicking ‘Execute workflow’": {
      "main": [
        [
          {
            "node": "settings",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}