Can AppSheet API handle batch delete operations? What is the payload format?

[gabrieldagostim]

What is the best way to perform a batch delete operation on an AppSheet database via the API? I’m looking for an efficient way to remove multiple rows at once and would like to know the correct request format and any limitations.

I am consuming the API using Python:

chunk_size = 500

for chunk in split_dataframe(json_data, chunk_size):
    data = {
        "Action": "Delete",
        "Properties": {
            "Locale": "pt-BR",
            "Timezone": "America/Sao_Paulo"
        },
        "Rows": [{"key_column": json_data[0]["key_column"]}]
    }

    response = requests.post(url, headers=headers, data=json.dumps(data))

    if response.status_code == 200:
        print(f"Chunk OK! {chunk.index[0]} - {chunk.index[-1]}")
    else:
        print(f"Chunk Error: {response.status_code}, {response.text}")

(In this project, I am using an AppSheet database.)

The scope of the deletion I need to perform quickly involves approximately 30,000 rows from a table with around 40 to 50 columns.

The project requires frequent updates to the app’s data since it changes dynamically. This involves managing approximately 10 databases with similar structures and scales.

Currently, I have an efficient method for batch additions, but I am facing challenges with large-scale deletions, such as potential API rate limits, timeouts, or excessive processing time.

What would be the best approach to handle these mass deletions efficiently? Are there any best practices or alternative strategies (e.g., chunking, optimizing API usage, or combining operations) that you would recommend?

[scott192]

First of all, I would spin off your delete operation into its own specific app and not let your api call the deletes from an app that is actively using the table. Why?  Because even though you are sending a bulk delete operation, Appsheet will treat each separate row deletion individually and do all of the trigger and event stuff it would do for each row...including VC recalculation and database syncs.  Consequently slowing down the deletion and running into timeouts and so on.

If you create a quick app that has your bulk delete table as the only table in the app and don't add any VCs or Bots, then you can send your API call to that App instead and get the kind of performance you would expect from it.

[Rifad]

Deletes often trigger updates in the parent table, affecting costs, quantity, and other tables. While this is beneficial, complex use cases may require these automations to be consistently triggered.

[gabrieldagostim]

Thank you Scott for taking the time to answer this question.

I understand that this might involve strategically delegating responsibilities that don't necessarily need to be handled by the API.

As you mentioned, separating the delete operation into a specific app—how would that work? Would it involve creating a bot or a specific automation?

[scott192 · Accepted solution]

No, not at all.  You have said that you are using the API to do bulk additions?  In that API request you have given a huge list of records to add and the response from the API would have been a huge list of the row ids (keys) that were added?

So, if I already knew the list of row ids I wanted to bulk delete, I would send a simple API call to an Appsheet App that exists solely for the purpose of accepting the API call and acting on it.  This app would not have any views or actions or bots, it would simply have the table that you wish to perform bulk deletions on with that tables primary key clearly defined...that is all.

You would POST your API request to that specific apps unique id and table in the same way you have done for bulk additions (remember to tick the box in your app so that it accepts API requests), but you would use the DELETE keyword and provide the huge list or row ids you want to delete.

[AleksiAlkio]

If you use another app as Scott proposed, you could also trigger the AppSheet API in another app. So it's kind of a data change but you don't need to trigger anything from that other app manually.