I noticed that every value returned when using the /rows API endpoint includes ids, so I thought “Great, I can just write the same data back and it will figure out which values to update” from the ids. Nope, trying to write back the same data (even if you put it in the { rows: [{ cells: [] }] } format) does not work at all. Btw the docs seem to use a reference to a RowEdit format which is not documented anywhere. You just have to figure it out from the one example, it seems?
It is still unclear to me how to write back structured data, every example I see includes writing plain values. I guess I just have to rely on parsing? This seems a bit fragile.
But also, from reading this it appears that you have to provide a list of columns to act as the primary key? But …there is an id already?
In my use case, I’m writing a library that handles Coda data generically, so I cannot possibly know which columns I could use as key. I know there are also the single row endpoints, but it would be nice to not need to send a dozen API calls to update a number in a dozen rows.
What am I missing? I feel I must be missing something pretty huge.
Hi @Lea_Verou - Sorry to hear about the confusion, our API documentation could certainly benefit from more guides and tutorials that demonstrate common patterns.
If you are trying to update an existing row you should by use the updateRow endpoint, now the upsertRow endpoint. The updateRow endpoint takes in the row ID that you mentioned, and doesn’t require specifying any key columns.
You can think of the upsertRow endpoint as an insertRow endpoint, meant for adding new rows. It just happens to include this bonus, optional upserting feature. To use it you specify a set of key columns. and if an existing row matches the passed row in for those columns, the existing row will be updated instead of adding a new one.
“Here are the rows in my system. Add them to the table if they are new, and update them if you have an older copy.”
When upserting you don’t need to first get the existing rows, figure out how they map to your data, then update those specific rows. You just send over the data, as well as which columns in that data are the keys in the foreign system, and Coda handles the logic.
If you want to implement a more sophisticated syncing method you will need to update rows individually, which today can only be done with updateRow. We currently don’t have a batch endpoint for updating rows.
I don’t want to use a “more sophisticated syncing method”, I just want to use the ids that already exist, rather than having to depend on some combination of cells being unique (a concept that doesn’t seem to exist anywhere on the Coda interface, nor am I aware of any way to enforce it).
Fair enough, but unfortunately not something we support today. I’ll pass your feedback onto the team. If you could provide some more context on your use case it will help the engineers understand the gaps and prioritize the request.
I’m writing a library to read a Coda table and return it as a JSON object that can be manipulated by the user and written back. Since the library needs to be able to handle any data, I cannot depend on a particular combination of columns being unique, nor can I add columns with the row id. And since the update could potentially involve numerous rows, having to make an API call per row would be very inefficient.
Hi @Lea_Verou ! Just a regular Coda user here, but I do a lot of upserting of rows. In particular, I don’t know if you’ve ever used Drafts for Mac, but the Coda workflow I created for Drafts does what I think you’re describing — I can download data from the Coda API into Drafts (in my case, as JSON), edit the JSON in Drafts (a text editor), and then upsert it back to Coda using the API again. That seems like the kind of data in/data out process you’re describing. (I’ve also been able to do the same using Shortcuts for iOS.)
Couple thoughts I had that I hope might be helpful:
It is definitely the case that the format of the JSON that comes from the Coda API and the format that you need to have the JSON in to send it back to the Coda API are different. A lot of the code I write in my various Coda in/out programs is transforming that JSON from one way to the other. Mostly I reduce the incoming (downloaded) JSON to just what’s within the “values” object, the direct contents of (in this case) each row, and then use that in creating my JSON to send back. The API is really fickle about getting the pattern of nested objects and arrays just right when sending back that row/cell/column/value JSON.
I don’t specifically recall a prohibition against using the ID that comes through in Coda’s API as an upsert value, but I’m not doing it with any of my codes, so I’m wondering if at some point I found out it wasn’t possible. In one of my uses, I have a product name that might change but a SKU that’s immutable, so I might upsert to make a change to a name but I can use the immutable SKU as the key column.
For another of my Coda databases (and I’m hoping this might help you) there was really nothing relevant to use as a unique key (vendor might be the same as another, price might be the same, date might be the same), so what I did was create a formula column called ID that writes the Unix epoch time to a row when I create it — DateToEpoch(thisRow.Created()). All my other attributes might be unique, but the time down to the millisecond that I create a row can only occur one time. But, Coda doesn’t let you use calculation columns for keys, so then I have another column, ID Duplicate, and a button system where occasionally (or before I’m going to do some downloading/upserting) I can have it simply copy the row-creation epoch time value from the ID column to the ID Duplicate column. That ID downloads when I download the JSON and I can use it as the key when I upload, because it’s unique and I never need to change it.
Upsert calls can definitely contain updates to multiple rows (though matching each row’s values to their equivalent in just one key column) and anything that doesn’t match gets added as a new row. So batch upserting definitely exists and shouldn’t require multiple API calls.
I hope that helps! Again, I’m just going by my own knowledge cobbled together from using Coda over the years, but if I can answer any questions, don’t hesitate to let me know. All best.
I hope the PMs in charge of the API are monitoring this, because that’s a pretty big DX smell. Reading and writing should be idempotent (or at least symmetrical), to the extent possible. Right now these two API endpoints appear to have been designed by completely different people, following completely different design principles!
Yup, as I mentioned I cannot do that, because a library intended for reading data and writing back data changes cannot be arbitrarily inserting columns for its own use. I do not (necessarily) control the tables I’m modifying.
Yes, that was my original understanding, and it appears to be correct. The main issue is referencing the correct rows, without actually modifying the data model. Which doesn’t appear to be possible right now (in the general case).
there is a way to set your ID value using a formula that DOESNT make the column into a computed column, thus eleminating the need to copy the column later with a button or automation.
you set the “value for new rows” option for your ID column to the formula that combines epoch-timestamp and other values.
it gets executed immediately when a new row is created; giving it the unique UID you need.
but it is not a formula column thereafter, so you can use it directly in your API calls as the key for upserting.