Fetch and update Grist table from external API source

Ask for Help
1

Hi all,
This is probably a dumb question, but I kindly ask your help to understand the way forward…

I need to fetch data from an external source to a Grist table. The external source is read only, so I just need to import the table and keep it updated, automatically once per day.
The data from the external source has an alphanumeric primary key. I would like to create a Grist table that I could use as a reference from other tables.

I am searching for the simplest and automatic possible solution (eg. cron script?), we are in rather tight intranet, so I cannot use external services (eg. N8N).

I can fetch the data from my intranet only with something like:
curl -X GET "https://wauc-test.dipvvf.it/api/Personale?codiciSede=AB" -H "accept: application/json"

or simply browsing to:
https://wauc-test.dipvvf.it/api/Personale?codiciSede=AB

The response is a JSON string like this (this data is redacted of course):

[
  {
    "codiceFiscale": "RSSMRI84L20C546K",
    "cognome": "ROSSI",
    "nome": "MARIO",
    "accountDipvvf": "rossi.mario",
    "emailVigilfuoco": "rossi.mario@vigilfuoco.it",
    "qualifica": {
      "nome": "VCSC",
      "gruppo": {
        "codice": "1",
        "descrizione": "VIGILI DEL FUOCO"
      },
      "codSettore": null,
      "codice": "505",
      "descrizione": "VIGILE DEL FUOCO COORDINATORE SCATTO CONVENZIONALE"
    },
    "sede": {
      "id": "GE.2002",
      "codice": "GE",
      "codDistaccamento": "2002",
      "descrizione": "Vattelapesca"
    },
    "specializzazioni": [
      {
        "codice": "AM3",
        "descrizione": "AUTISTA DI 3^ GRADO",
        "dataInizioValidita": "19/04/2021",
        "dataFineValidita": "19/04/2031"
      }
    ],
    "turno": "A",
    "saltoTurno": "2",
    "tipoPersonale": {
      "codice": "2",
      "descrizione": "OPERATIVI"
    }
  },
...other records...
]

In this case the codiceFiscale field is the primary key.

Thank you in advance,
Emanuele

2

Emanuele,
a simple way to get the data from the external source and import it in Grist could be to use the Grist API:

  • read the data with the curl command
  • use the returned json to build the Grist API payload
  • call the Grist API to add / update the records in the Grist table

Depending on how the source data changes you may need some logic to understand what to load in Grist (e.g. get the codici fiscali already in Grist and filter them out from the external source).

As for the API you can directly use the REST API (REST API reference - Grist Help Center) or one of the clients (GitHub - gristlabs/py_grist_api: Python client for interacting with Grist, GitHub - gristlabs/py_grist_api: Python client for interacting with Grist)

When using the REST API I find it productive to test the API using PostMan and then use the code snippet it produces.

You can package the above in a script and call it via cron once a day.

Hope this helps, if you need more details, let me know.

Fabio

3

Thank you Fabio.
I did not know PostMan, it seems great indeed.
I am probably going to use py_grist_api.

A couple of questions more:

  1. What python lib would you suggest to transform the original json to the Grist json payload?

  2. I would use the Add or update records of a table API that could filter the duplicated codici fiscali. Seems correct to you?

The case of using external data sources as read or read/write tables may seem quite common to me. What about suggesting a specific feature to Grist devs to simplify the auto import/export?

4

Emanuele,
JSON strings can easily converted to Python dictionaries with the native json package:

import json
my_dict = json.loads(my_json_string)

Once you have a dictionary, should be straightforward to transform it (or create a new one) with the structure you need.

The API you mention should be fine, if you plan to use the Python client you may want to check sync_table

As for the “importing feature”, I did not check if there are already some features (there might be): often the data transformation and the integration parts are more cumbersome that the import per se (for example I’m working on an integration to import data from a Google Form to a Grist table: importing data in Grist is as simple as an API call, adapting the data is a little more complex), I’m not sure how to formulate the feature request.

Fabio

,

5

Thank you again Fabio.

You are very probably right. Better to leave to scripting for now.

Emanuele