Limitations of the current API

• Creating and modifying records requires one request per record

Use cases

• Pasting data into cells requires modifying existing records and also (according to the spec) creating new records too (when the pasted data extends beyond the viewable records).
• We may eventually want to implement other features like setting multiple cell values to null, or doing something akin to the "fill" feature that spreadsheets offer. This would require modifying records in bulk.

Similar work

• Add bulk delete endpoint #2641 added a new endpoint to delete records in bulk

• Add existing_import API #1442 added a a new endpoint to create records in bulk

  In Matrix @silentninja said:

  I don't think you can use the existing_import API for pasting the data into cells because the existing_import API creates a temporary Table from a datafile and then copies the data from the temporary Table to the Table the data has to be imported into, meanwhile the case of pasting data into cells is a much simpler task. So creating a new temporary Table and copying data from that temporary table might be an overkill for the copy-paste feature

A "Paste" example

1. User copies these eight cells:

   

2. User pastes the copied data into the sheet at the following starting position:

   

3. The front end uses the API to:

   • Update records 249 and 250, modifying First Name and Last Name.
   • Create two new records by supplying values for First Name and Last Name.

4. The API responds with the full records for all the records modified and created, allowing the front end to display the new data.

Thoughts on API design

• I want to leave this ticket somewhat open-ended to start, in case the backend team would like to propose an API design.

• It would be nice to perform these changes within a single transaction. From a UX perspective, if something goes wrong, no changes should be made. That would mean creating one API to handle both updating records and creating records.

• How does the API behave when we tell it to update a record which no longer exists?

  Pretend we're following the "Paste" example above but in between steps 1. and 2. another user deletes record 249.

  • One possibility would be to create a new record with id 249.
  • Another possibility would be to fail. I think I prefer this behavior.

• For the copy-paste use case at least, we can rely on our our data being rectangular. That is, we don't need to update one field in record 249 and a different field in record 250 — we need to update all the same fields in all records. I think it's fine to bake this assumption into the API, especially since I predict it may be necessary for a performant implementation. For example, we'd would want to avoid a naive implementation which makes one UPDATE query per row.

  Assuming we do bake this "rectangular" assumption into the API behavior, it may be worth considering the ways in which the API design convey that behavior. For example, we might be inclined to design the request body like this:

  [
    { "1": 249, "2": null,     "3": "Lloyd"     },
    { "1": 250, "2": "Shelby", "3": "Shepherd"  },
    {           "2": "Robert", "3": "Mccormick" },
    {           "2": "Dawn",   "3": null        }
  ]

  But with that design, an API consumer could submit a rather strange request to update two records like this:

  [
    { "1": 249,               "3": "Lloyd" },
    { "1": 250, "2": "Shelby"              }
  ]

  Because of the potential for requests like the the above, we'd need to clearly communicate the behavior of the API under these circumstances. For record 249, column 2, would the API leave the value untouched? Or would it set the value to NULL? Or maybe the request would fail?

  Just as food for brainstorming, a request schema like this might go further towards communicating this "rectangular" requirement to API consumers. I don't know.

  {
    "columns": [1, 2, 3],
    "data": [
      [ 249, null,     "Lloyd"      ],
      [ 250, "Shelby", "Shepherd"   ],
      [ null, "Robert", "Mccormick" ],
      [ null, "Dawn",   null        ]
    ]
  }

• Unlike our proper records endpoint, the front end will not have any good way to display cell-level errors for this paste feature, or for the other features I'm imagining we'd build with this API. For that reason, the requirements around the error response are more loose. When the request fails due to some data problem (e.g. constraint violation, etc) the front end will display a toast message. The API should provide enough info in the error message to help the user fix their problem, but it certainly doesn't need to list all the error, or even be super specific about which cell caused the error.