BatchUpsertTableRows - Amazon Honeycode

BatchUpsertTableRows

The BatchUpsertTableRows API can be used to upsert one or more rows in a table. The upsert operation takes a filter formula as input and uses it to find matching rows in the table. If matching rows are found, cells in those rows are updated as specified in the request. If a matching row is not found, a new row is created and cells in the new row are set as specified in the request.

To find more details about using this API check the BatchUpsertTableRows API Reference page.

The following example shows this API being called with two batch items. One batch item finds an existing row and updates it and the second batch item creates a new row. Replace <workbook-id> with your workbook id and <table-id> with the table id from the response of ListTables API call.

AWS CLI Example

aws honeycode batch-upsert-table-rows \ --workbook-id "<workboook-id>" \ --table-id "<table-id>" \ --rows-to-upsert '[ { "batchItemId": "item-001", "filter": { "formula": "=Filter(Stocks,\"Stocks[Symbol]=%\",\"BBB\")" }, "cellsToUpdate": { "<symbol-column-id>": { "fact": "BBB" }, "<price-column-id>": { "fact": "25.32" }, "<previous-price-column-id>": { "fact": "28.76" }, "<last-updated-column-id>": { "fact": "11/8 6:13 PM" } } }, { "batchItemId": "item-002", "filter": { "formula": "=Filter(Stocks,\"Stocks[Symbol]=%\",\"CCC\")" }, "cellsToUpdate": { "<symbol-column-id>": { "fact": "CCC" }, "<price-column-id>": { "fact": "110.8" }, "<previous-price-column-id>": { "fact": "108.10" }, "<last-updated-column-id>": { "fact": "11/8 6:13 PM" } } } ]'

Python SDK Example

def upsert_row_data(batch_item_id, filter_formula, symbol, price, previous_price, last_updated): return { "batchItemId": batch_item_id, "filter": { "formula": filter_formula }, "cellsToUpdate": { "<symbol-column-id>": { "fact": symbol }, "<price-column-id>": { "fact": price }, "<previous-price-column-id>": { "fact": previous_price}, "<last-updated-column-id>": { "fact": last_updated } } } response = honeycode_client.batch_upsert_table_rows( workbookId = '<workbook-id>', tableId = '<table-id>', rowsToUpsert = [ upsert_row_data("item-001", "=Filter(Stocks,\"Stocks[Symbol]=%\",\"BBB\")", "BBB", "25.32", "28.76", "11/8 6:13 PM"), upsert_row_data("item-002", "=Filter(Stocks,\"Stocks[Symbol]=%\",\"CCC\")", "CCC", "110.8", "108.10", "11/8 6:13 PM") ])

Response

{ "rows": { "item-001": { "rowIds": [ "<bbb-row-id>" ], "upsertAction": "UPDATED" }, "item-002": { "rowIds": [ "<ccc-row-id>" ], "upsertAction": "APPENDED" } }, "workbookCursor": 1288566784 }

After the rows are upserted, this is how the table looks: