API Batch Update Rows by ID

[Leon_Grdic]

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

[Xyzor_Max]

you can remove the formula from the key-column and move it into an action formula.
so the correct value is stored there but it is no longer a formula column.

you must then ensure that this action is executed at the right time,
so the key-column values are there at the appropriate time.

if they are only needed for the bulk upsert,
then the action formula can set the values for all rows all-at-once,
just before the API call.

max

[Leon_Grdic]

That’s it! Thank you a lot! I was focusing on the limitation and didn’t realise that row IDs could be pre-populated without the column having to be a formula column.

I could even simplify this further by adding the formula into the “Value for new rows” setting like so:

This appears to work both when the row is added through the app and the API, so it solves my problem.

It would still be nice to have an actual batch update endpoint (there are already batch insert and batch delete endpoints), but while this works I am happy

[Christiaan_Huizer]

yep @Leon_Grdic , it could be as simple as RowId(thisRow) instead of your function.

[Leon_Grdic]

Thanks for chiming in!

When I use RowId(thisRow) I only get a number:

I need to reference rows by their API IDs - e.g. i-EyNMJxYZ4A). Is there a simpler way to get that in a formula?

[Christiaan_Huizer]

when this unique number (which is also named Row Id) is not what you need, there is not really a simpler version. I use this one: thisRow._Merge().ToText().ParseJSON("$.identifier"), Is this simpler, not so sure

[Leon_Grdic]

True hahaha

Btw love your blog, thanks again for commenting and helping

[Eric_Koleda]

Some very creative solutions! Although ideally they wouldn’t be required. The need to bulk update rows by ID is something we’ve heard before (most recently from @Lea_Verou on this thread) and I’ll raise it with the engineers again.