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": []
}
13 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.

I would do :

  • A “schedule” node that launches the workflow every 2h
  • A “Grist” node that gets your column
  • A “code” node to do the check
  • An “if” node to say : if true
  • An “email” node to send the email if true
1 Like

spent a day trying to automate updating a Grist table with data from an Excel table, and failed miserably.

when using the test code with fixed data, it works well and updates or creates new records in Grist.

But when replacing the fixed data by expression (fields), it always returns error of wrong payload or something like that

this is the JSON I am using… it even shows, on the JSON Editor Result, the correct data!!

{
  "records": [
    {
      "require": {
        "CODIGO_OBRA": "{{$json.CODIGO_OBRA}}"
      },
      "fields": {
        "STATUS": "{{$json.STATUS}}",
        "NOME_CONTRATANTE": "{{$json.NOME_CONTRATANTE}}",
        "REFERENCIA_OBRA": "{{$json.REFERENCIA_OBRA}}",
        "GESTOR": "{{$json.GESTOR}}"
      }
    }
  ]
}

Hi, in your 3rd screenshot it looks like you’re sending a body quite empty, and the error is telling you “body.records” are missing.

Did you get rid of the code node, and is the code you’re showing in the screen2 is in the http put node?

Indeed for the ex i used “code” node with fixed data, just to have something to inject in the http put node. The piece of code with expressions/variable must be in the http node. This is the request payload.

If you use an excel, you replace the “code” node with a excel node. The output of the excel should already be variables, that you inject in the http node. The pièce of code with expressions must still be in the http put node.

Can this help?

I will try.

But I don´t see how the body is empty… I mean… look the input… 713 itens

72 pages with 10 itens each

I didn´t use a code node… I used an Edit Fields node

here is the flow… first node it connects to our system and downloads the Excel file with latest data.

Then it reads the Excel file
Extracts the fields
Edit the fields to already remove “dirt”

Can you show your http node (and maybe your code node) please?

it worked. With a very different JSON code at the HTTP Request

{{ [$json].map(item => ({
require: { CODIGO_OBRA: item.CODIGO_OBRA },
fields: {
REFERENCIA_OBRA: item.REFERENCIA_OBRA,
NOME_CONTRATANTE: item.NOME_CONTRATANTE,
GESTOR: item.GESTOR,
STATUS: item.STATUS
}
})) }}