BatchUpsertTableRows - Amazon Honeycode
AWS CLI ExamplePython SDK ExampleResponse

To our valued customers: After careful consideration, we have made the decision to end the Amazon Honeycode beta service, effective February 29, 2024. New customer sign-ups and account plan upgrades are no longer available. Existing customers will be able to use Honeycode and your Honeycode apps as normal (and add team members to your existing account) until February 29, 2024, when the service will be discontinued. After this date, you will no longer be able to use Honeycode or any of the apps you created in Honeycode. To learn more about this change, and how to download your data, visit the Community Discussions.

BatchUpsertTableRows

Note

This API does not currently support multi-select picklist values in the new Honeycode experience.

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: