Very basic N8N-Grist workflows

Hi,
I’m new to Grist and really loving it, thanks for the awesome tool :slight_smile:
I played around with N8N and ended up writing tutorials for basic workflows, as a memo for myself and that might help others! Here they are:

Tuto: Get data from Grist
  • In n8n, add Grist node:
    Credential to connect with : “Create new credential”. The API key can be found at https://docs.getgrist.com/account
    Operation : “Get Many Rows”
    Document ID : can be found in Grist Settings > Document ID
    Table ID : table name (replace blank spaces by _) or copy/paste from Code View
  • Click Test step, see the output!

Next steps: add other nodes after, for instance “Convert to file” node to transform json into csv or xls, or Metabase node to create dashboards

Tuto: Add or update Grist data (upsert)

Using HTTP request node and Grist API (as for now n8n Grist node only offers distinct options to “create” or to “update”).

  • In Grist API console (Settings > API console), find records and PUT request
  • Click Try it out, choose the document of interest in the first dropdown and the table of interest in the second
  • In request body, in require object, put the column name and value to match ; in fields the column name and value to update. Ex:
{
  "records": [
    {
      "require": {
        "Species": "Rhinolophus mehelyi"
      },
      "fields": {
        "Red_list_category": "Endangered"
      }
    }
  ]
}
  • Click Execute, see the curl request, the request URL and the server response (should be 200). Verify in the table that the fields have been successfully created or updated.
  • In N8N, add HTTP request node
    Method : PUT
    URL : paste the request URL from the console API
    Authentication : Generic Credential Type
    Generic Auth Type : Header Auth
    Header Auth : Create New Credentials with Name = Authorization and Value = Bearer YOUR_GRIST_API_KEY
    Send Body : activated
    Body Content Type : JSON
    Specify Body : Using JSON
    JSON : paste the JSON from the console API
  • Click Test step and verify in Grist table that the fields have been successfully created or updated. Et voilà !

Next steps: Add other nodes before (call to another API, xls file…). Or for testing purposes, add a Code node which returns a json. The code node script:

return [
  {
    "species": "Rhinolophus mehelyi",
    "category": "Critically Endangered",
  },
  {
    "species": "Corsican bat",
    "category": "Least Concern",
  }
];

The request body in HTTP node, mapping input data:

{
  "records": [
    {
      "require": {
        "Species": "{{$json.species}}"
      },
      "fields": {
        "Red_list_category":"{{$json.category}}"
      }
    }
  ]
} 
Tuto: Respond to Grist webhook
  • In N8N, create a Webhook node and copy the Test URL
  • In Grist Settings> Webhooks, create a new webhook :
    Name : free
    Event types : add, update, or both
    Table : table name
    Filter for changes in these columns (optional) : specify column name
    URL : paste the test URL from N8N
    Enabled: ON

NB: Ready Column is optional but useful to avoid unwanted notifications (cf doc)

  • Click Listen for test event
  • In Grist, modify a value in the specified column
  • In N8N see the successful request and the json looking like:
[
  {
    "headers": {
     ...
    },
    "params": {},
    "query": {},
    "body": [
      {
        "id": 2,
        "manualSort": 2,
        "Common_name": "Murin des marais",
        "Species": "Myotis dasycneme ",
        "Red_list_category": "Endangered",
        "Photo": [
          "L",
          2
        ]
      }
    ],
    "webhookUrl": "https://zzz.app.n8n.cloud/webhook-test/ee647f7d4",
    "executionMode": "test"
  }
]

  • Add an email node (or another type of node) after the webhook node, to get notified when there’s an update
  • Config the node to send a message about the updated field.
    Ex of email body, in Text > Expression :
The {{$json.body[0].Species}} is now listed in the IUCN red list as "{{$json.body[0].Red_list_category}}".
  • Click Test step, check mail client to see the new email. Testing done and dusted!
  • In N8N, in Webhook node switch to Production URL and copy the url. Activate the general workflow (mandatory!!!) and save
  • In Grist Webhook URL field, replace the test url by the production url. Disable and re-enable the Enabled toggle in case of an error in the Status field.
  • In the table, modify a value in the specified column. Check inbox and see the triggered email.
    Screenshot 2024-10-30 at 18.49.19

N8N json file
{
  "name": "Grist simple examples",
  "nodes": [
    {
      "parameters": {
        "binaryPropertyName": "=",
        "options": {}
      },
      "id": "6c43e368-39d7-4a6d-9eb9-a9710d375e0a",
      "name": "Convert to File",
      "type": "n8n-nodes-base.convertToFile",
      "typeVersion": 1.1,
      "position": [
        -480,
        660
      ]
    },
    {
      "parameters": {
        "content": "## Add or Update data\n",
        "height": 249.14686976518175,
        "width": 1146.9145651495285,
        "color": 2
      },
      "id": "f7d9191e-d5bd-49de-95fe-44d157d7c2ba",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -1040,
        860
      ]
    },
    {
      "parameters": {
        "content": "## Get data\n",
        "height": 241.76566563124703,
        "width": 1146.9145651495285,
        "color": 5
      },
      "id": "502a8668-4b83-4eb2-991e-9f02958c9359",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -1040,
        600
      ]
    },
    {
      "parameters": {},
      "id": "0bcfefb8-5795-455d-9081-56763ce313ff",
      "name": "When clicking ‘Test workflow’",
      "type": "n8n-nodes-base.manualTrigger",
      "typeVersion": 1,
      "position": [
        -920,
        660
      ]
    },
    {
      "parameters": {
        "jsCode": "return [\n  {\n    \"species\": \"Rhinolophus mehelyi\",\n    \"category\": \"Critically Endangered\",\n  },\n  {\n    \"species\": \"Corsican Bat Myotis nustrale\",\n    \"category\": \"Least Concern\",\n  }\n];"
      },
      "id": "e712ce9f-62b9-4353-9d0d-7277065a56d8",
      "name": "new data",
      "type": "n8n-nodes-base.code",
      "typeVersion": 2,
      "position": [
        -660,
        920
      ]
    },
    {
      "parameters": {
        "docId": "4bcewbGwxdzhGM7KJioKMb",
        "tableId": "Communes",
        "additionalOptions": {}
      },
      "id": "1e7d9450-253f-411d-a771-1d39106f2efe",
      "name": "Grist get rows",
      "type": "n8n-nodes-base.grist",
      "typeVersion": 1,
      "position": [
        -660,
        660
      ],
      "credentials": {
        "gristApi": {
          "id": "hxImCvhZbyUHAIkS",
          "name": "Grist account 2"
        }
      }
    },
    {
      "parameters": {
        "method": "PUT",
        "url": "https://docs.getgrist.com/api/docs/o3CCCCCCCCCCCw/tables/French_bats_species/records",
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth",
        "sendBody": true,
        "specifyBody": "json",
        "jsonBody": "={\n  \"records\": [\n    {\n      \"require\": {\n        \"Species\": \"{{$json.species}}\"\n      },\n      \"fields\": {\n        \"Red_list_category\":\"{{$json.category}}\"\n      }\n    }\n  ]\n} ",
        "options": {}
      },
      "id": "8b7b7d1d-42b3-40af-8781-418db2fddfc0",
      "name": "Grist PUT request",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4.2,
      "position": [
        -480,
        920
      ],
      "credentials": {
        "httpHeaderAuth": {
          "id": "yXy7keE1NvlNK4wl",
          "name": "Header Auth account"
        }
      }
    },
    {
      "parameters": {
        "fromEmail": "ssss@e.email",
        "toEmail": "ssss@e.email",
        "subject": "IUCN bat status changed 🦇",
        "emailFormat": "text",
        "text": "=Hello, \nThe {{$json.body[0].Species}} is now listed in the IUCN red list as \"{{$json.body[0].Red_list_category}}\".\n\nKind regards",
        "options": {}
      },
      "id": "27ea1d94-7d9d-4c0c-a7be-9f0e8665e6c1",
      "name": "Send Email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 2.1,
      "position": [
        -480,
        1180
      ],
      "credentials": {
        "smtp": {
          "id": "qdRk2D2IJzyBxgFN",
          "name": "SMTP account"
        }
      }
    },
    {
      "parameters": {
        "content": "## Respond to Grist webhook\n",
        "height": 241.76566563124703,
        "width": 1146.9145651495285,
        "color": 6
      },
      "id": "30f38f80-1f7e-40e0-b9ae-9b17f7c9d97e",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "typeVersion": 1,
      "position": [
        -1040,
        1140
      ]
    },
    {
      "parameters": {
        "httpMethod": "POST",
        "path": "03e24572-a381-455e-a5b8-ae697647f7d4",
        "options": {}
      },
      "id": "d9e4bdd3-0892-4774-8626-e679f2ab76dd",
      "name": "Webhook: Grist data changed",
      "type": "n8n-nodes-base.webhook",
      "position": [
        -660,
        1180
      ],
      "webhookId": "03e24572-a381-455e-a5b8-ae697647f7d4",
      "typeVersion": 1.1
    }
  ],
  "pinData": {},
  "connections": {
    "When clicking ‘Test workflow’": {
      "main": [
        [
          {
            "node": "Grist get rows",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "new data": {
      "main": [
        [
          {
            "node": "Grist PUT request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Grist get rows": {
      "main": [
        [
          {
            "node": "Convert to File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Webhook: Grist data changed": {
      "main": [
        [
          {
            "node": "Send Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": true,
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "00622671-91e4-4e52-af7e-b9aaf34b9ae1",
  "meta": {
    "templateCredsSetupCompleted": true,
    "instanceId": "c72a9cba71c59f460b0e40bdba55cf485741c8f5079e60f3aed3fe7c1a35e0dd"
  },
  "id": "wjL26RUqLjhuWtay",
  "tags": []
}
12 Likes

I need for n8n to check a Grist table every 2 hours let’s say, and if a DATA in a column of a specific table is less than 15 days away, send an email for a user.