An UPSERT statement updates an existing record or creates a new record if no matching record is found.

Upsert and Bulk Upsert Overview

This feature introduces upsert and bulk upsert support for Smartsheet.

Because the Smartsheet API does not natively support upsert operations, this functionality is implemented as an application-level (artificial) upsert. Inserts and updates are sent as separate API requests, with the system determining which operation to perform at runtime.

How It Works

At runtime, each row is evaluated based on the value of the primary column:

• If the value already exists in the target sheet, the row is sent as an update.
• If the value does not exist, the row is sent as an insert.

Requirements

• The sheet must include a user-defined primary column.
• The primary column values must be unique and non-null for each row.
• Note: These constraints are not enforced by Smartsheet and must be guaranteed externally.

Upsert Strategies

The system determines whether a row already exists using one of the following strategies:

• Full sheet scan retrieves and evaluates all rows.
• Search API searches by the primary column value.

The strategy is selected at runtime based on:

• The number of rows being inserted or updated.
• The total number of rows in the sheet (for example, page size considerations).

This approach ensures correct behavior while optimizing performance for different data sizes.