How to use properly add or update API endpoint?

Developers
1

Hello, I need to add or update all values of a Grist table from a local csv using add or update API endpoint.

I am having difficulty on how to search all values of the table and update only the different values from the local csv. @Dmitry_Sagalovskiy ?
How to get the id of each record to udpate? Is that the correct payload structure?

json
{
  "records": [
    {
      "require": {"id": 1},
      "fields": {"field1": "value1", "field2": "value2"}
    },
    {
      "require": {"id": 2},
      "fields": {"field1": "value3", "field2": "value4"}
    }
  ]
}
2

Hi @logoupraxis! There is a PATCH method (to update known records) and PUT method (add-or-update).

The PATCH/modify method requires knowing the id of the records to modify. The PUT/add-or-update method allows identifying records using other columns.

One way to do the update is to fetch all records (using GET), compare them to the CSV file using your own logic in your script, decide which need to be updated, then use the PATCH/modify method to update just those records with just the fields that changed. The bulk of the work happens in your own script, but you have more control what to do.

For certain situations, this can be simplified using the PUT/add-or-update method. If you are updating from a CSV file, you must have some way to know which row of the CSV file corresponds to which row in Grist. Perhaps you have a column like “email” that identifies each record. In that case, you can use PUT like this:

{
  "records": [
    {
      "require": {"email": "bob@example.com"},
      "fields": {"field1": "value1", "field2": "value2"}
    },
    ...
  ]
}

The reason it’s called “add-or-update” is that Grist will look for a row with the given value of the email column. If it exists, it will update the supplied fields. If it doesn’t exist, Grist will create it.

(The payload that you suggested, using "id" in the "require" field, would make the PUT/add-or-update method equivalent to the PATCH/modify method.)

As far as whether only the different fields will be updated or all the supplied fields, I am not sure actually, but I don’t know how you would tell the difference if the result is the same, except for how fast it might run.

If efficiency matters, e.g. if you have a large CSV file and a large table, but only a few records have changed, the first method is faster: fetch data first, do the processing in your script, then update only the few records needed.