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 key 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.

By default, the key column is the sheet's primary column. You can specify a different column with the ExternalIdColumn pseudocolumn, as described below. The key column values must be unique and non-null for each row. Smartsheet itself does not guarantee these constraints natively for any kind of column.

Custom Key Column (ExternalIdColumn)

Set the ExternalIdColumn pseudocolumn to the name of the column you want to use as the key column instead of the default primary column. The value of ExternalIdColumn is the name of the target column, not a data value.

Single-row UPSERT:

UPSERT INTO MySheet ([Name], [Email], [UniqueCode], [ExternalIdColumn])
VALUES ('John', '[email protected]', 'ABC-123', 'UniqueCode');

Bulk UPSERT:

INSERT INTO MySheet#TEMP ([Name], [Email], [UniqueCode], [ExternalIdColumn]) VALUES ('John', '[email protected]', 'ABC-123', 'UniqueCode');
INSERT INTO MySheet#TEMP ([Name], [Email], [UniqueCode], [ExternalIdColumn]) VALUES ('Jane', '[email protected]', 'XYZ-789', 'UniqueCode');
UPSERT INTO MySheet ([Name], [Email], [UniqueCode], [ExternalIdColumn]) SELECT [Name], [Email], [UniqueCode], [ExternalIdColumn] FROM MySheet#TEMP;

Requirements when using ExternalIdColumn:

• The value must refer to a valid key column in the sheet.
• The target column must be included in the UPSERT statement's column list.
• Every row in the batch must specify the same ExternalIdColumn value.

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 key 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.