I was hoping to use the Upsert API endpoint as a way to send a batch update rows by their ids (e.g. i-EyNMJxYZ4A
).
First, I added a formula column (with this formula: Split(thisRow+'', '/').Last()
) which populates the column with row IDs like so:
The idea was to use this column as the keyColumn in the upsert request.
This is my request body which is supposed to update a single row’s “Value” cell:
{
"keyColumns": [
"c-HhEy3iwNWP"
],
"rows": [
{
"cells": [
{
"column": "c-HhEy3iwNWP",
"value": "i-EyNMJxYZ4A"
},
{
"column": "c-KJz1Yg0n4V",
"value": "new value"
}
]
}
]
}
However the request is unfortunately failing with the following error message:
"keyColumn" c-HhEy3iwNWP is bound to a formula and cannot be directly written to.
This is a bummer. I understand why this check was implemented - If the passed value doesn’t exist in the table, the request is supposed to add a new row and write to the cell, which it can’t do in a formula cell.
However, I was planning to use the endpoint exclusively for updating existing rows, so writing to a formula cell would never be necessary or expected.
I have between 1000 and 2000 rows that need to be updated, and have no other distinctive columns that could be used as keyColumns. This means that the only supported way to achieve this is sending a few thousand update endpoint requests.
I’m hoping I’m missing something, but this seems to be a pretty disappointing limitation of the API.
Thank you in advance for any help